SQL，将值逻辑返回到另一个查询字段

问题描述：

这是我的查询，因为它是现在。如果ItemID等于另一个表列（BuildComponents.ComponentID）中的任何值，我想向结果检查中添加另一列。例如。如果它在那里写'Y'，否则写'N'。我不希望这会影响返回的行。我使用MS SQL 2005的逻辑是这样的：SQL，将值逻辑返回到另一个查询字段

如果（项目ID是（从BuildComponents选择的ComponentID）回报 'Y' 其他回报 'N'

SELECT 
'Item' AS type, 
i.ItemID, 
i.ItemNumber AS prodid, 
i.ItemName AS 'desc', 
i.SellUnitMeasure AS unit, 
i.SellUnitQuantity AS quantity, 
i.VATExclusiveStandardCost AS pricein, 
i.BaseSellingPrice AS priceout, 
v1.VATPercentageRate AS vatRateIn, 
1 AS vatTypeIn, 
v1.VATCode AS VATCodeIn, 
v1.VATCodeDescription AS VATCodeDescriptionIn, 
v2.VATPercentageRate AS vatRateOut, 
2 AS vatTypeOut, 
v2.VATCode AS VATCodeOut, 
v2.VATCodeDescription AS VATCodeDescriptionOut, 
i.IsInactive AS inactive, 
s.CardRecordID AS VendID, 
i.SupplierItemNumber AS VendCode, 
i.VATExclusiveStandardCost AS VendInPrice, 
i.ItemDescription AS ProductNote, 
i.CustomField1, 
i.CustomField2, 
i.CustomField3, 
cl1.CustomListText AS CustomField4, 
cl1.CustomListName AS CustomField4Name, 
cl2.CustomListText AS CustomField5, 
cl2.CustomListName AS CustomField5Name, 
cl3.CustomListText AS CustomField6, 
cl3.CustomListName AS CustomField6Name, 
'' AS QuantityOnHand, 
'' AS LocationName, 
i.PriceIsInclusive, 
i.ItemIsStocked, 
ISNULL(l1.LocationName, ISNULL(l2.LocationName, 'Default Warehouse')) AS DefaultLocation, 
i.PositiveAverageCost as cost 
FROM Items i 
LEFT JOIN ItemLocations il ON il.ItemID = i.ItemID AND il.ItemID IS NULL 
LEFT JOIN VATCodes v2 ON v2.VATCodeID = i.SellVATCodeID 
LEFT JOIN VATCodes v1 ON v1.VATCodeID = i.BuyVATCodeID 
LEFT JOIN Suppliers s ON s.SupplierID = i.PrimarySupplierID 
LEFT JOIN CustomLists cl1 ON cl1.CustomListID = i.CustomList1ID 
LEFT JOIN CustomLists cl2 ON cl2.CustomListID = i.CustomList2ID 
LEFT JOIN CustomLists cl3 ON cl3.CustomListID = i.CustomList3ID 
LEFT JOIN Locations l1 ON l1.LocationID = i.DefaultSellLocationID 
LEFT JOIN Locations l2 ON l2.LocationID = 1

答

要做到这一点的方法是左连接到目标表，如果列为空，那么它是'N'，否则'Y'与case语句。

但是，如果ComponentID在BuildComponents中不唯一，则可能会得到比预期更多的结果，因此您需要一个明确的子查询来阻止该情况的发生。

为了您的加入列表中添加

LEFT JOIN (SELECT DISTINCT ComponentID FROM BuildComponents) BC ON BC.ComponentID = i.ItemID

要你选择字段，你需要添加另一列，

CASE WHEN BC.ComponentID IS NULL THEN 'N' ELSE 'Y' END as MyColName

答

case when exists (select ComponentID from BuildComponents where ComponentID = i.ItemID) then 'Y' else 'N' end

答

SELECT 'Item' AS type, 
     …, 
     COALESCE(
     (
     SELECT TOP 1 'Y' 
     FROM BuildComponents bc 
     WHERE ComponentID = ItemID 
     ), 'N' 
     ) 
FROM Items i, 
     …

答

CASE WHEN EXISTS(SELECT * FROM BuildComponents bc WHERE bc.ComponentID = i.ItemID) THEN 'Y' ELSE 'N' END

应该做的伎俩。

EXISTS是EFFIC最好的方法是，如果BuildComponents中有多个记录，那么无论它是否存在，都会立即停止，只要它发现第一个存在就停止。

SQL，将值逻辑返回到另一个查询字段

相关推荐