SQL SELECT 语句 - 根据第二个表中的匹配数选择记录

Question

这是我的第一个问题，很抱歉违反了任何潜规则。我目前正在尝试使用 InventoryItem 表中的主键从 InventoryCategory 表中检索类别。

由于一个项目可以有一个或多个类别，InventoryCategory 表包含 ItemCode、CategoryCode 和 IsPrimary(定义哪个类别是primary category，是TRUE/FALSE类型)

我确实需要对只有一个类别的所有项目使用主要类别，对具有两个类别的项目使用非主要类别。我正在尝试使用 CASE 语句将正确的值输出为命名的 ParentCategory 字段，但遇到了困难。

目前，我只有 WHERE IC.IsPrimary = 1，因为我还不知道如何查询 ItemCode 以返回 # of CategoryCodes 并根据它选择非主要类别。我尝试执行 COUNT，但它总是返回 1 并继续删除。

代码:

SELECT 
    BillToCode, POSWorkstationID, 
    InvoiceDate, ItemName, 
    CategoryCode, 
    SUM(QtySales) QtySales, 
    SUM(Sales) Sales, 
    SUM(QtyReturns) QtyReturns, 
    SUM([Returns]) [Returns], 
    WarehouseCode   
FROM 
    (SELECT 
         CI.BillToCode, CI.WarehouseCode, 
         CI.POSWorkstationID, CI.InvoiceDate, 
         IC.CategoryCode, ii.ItemName, 
         CASE 
            WHEN CI.Type IN ('Invoice', 'Opening Invoice') 
              THEN CID.QuantityShipped 
              ELSE 0 
         END AS QtySales, 
         CASE 
            WHEN CI.Type IN ('Invoice', 'Opening Invoice') 
              THEN CID.ExtPriceRate 
              ELSE 0 
         END AS Sales, 
         CASE 
            WHEN CI.Type IN ('Credit Memo', 'Opening Credit', 'Gift Card', 'Gift Certificate') 
              THEN CID.QuantityShipped 
              ELSE 0 
         END AS QtyReturns,
         CASE 
            WHEN CI.Type IN ('Credit Memo', 'Opening Credit', 'Gift Card', 'Gift Certificate') 
              THEN CID.ExtPriceRate 
              ELSE 0 
         END AS [Returns]
     FROM 
         CustomerInvoice CI            
     INNER JOIN 
         Customer C ON CI.BillToCode = C.CustomerCode            
     INNER JOIN 
         CustomerInvoiceDetail CID ON CI.InvoiceCode = CID.InvoiceCode
     INNER JOIN 
         InventoryItem ii ON CID.ItemCode = ii.ItemCode    
     INNER JOIN 
         InventoryCategory IC ON CID.ItemCode = IC.ItemCode
     WHERE 
         (CI.IsBatch = 0 OR CI.IsBatch IS NULL) 
         AND CI.IsPosted = 1 
         AND CI.[Type] = 'Invoice' 
         AND CI.IsVoided = 0 
         AND IC.IsPrimary = 1) Items        
GROUP BY 
    POSWorkstationID, BillToCode, ItemName, InvoiceDate, CategoryCode, WarehouseCode

Answer 1

如果非主行的 IsPrimary 字段为 0，您可以执行 SELECT MIN 语句。见下文:

SELECT BillToCode        
, POSWorkstationID    
, InvoiceDate
, ItemName           
, CategoryCode
, SUM(QtySales) QtySales          
, SUM(Sales) Sales          
, SUM(QtyReturns) QtyReturns          
, SUM([Returns]) [Returns]       
, WarehouseCode   

FROM (          

SELECT CI.BillToCode
, CI.WarehouseCode
, CI.POSWorkstationID
, CI.InvoiceDate
, IC.CategoryCode
, ii.ItemName  
, CASE WHEN CI.Type IN ('Invoice', 'Opening Invoice') THEN CID.QuantityShipped ELSE 0 END AS QtySales            
, CASE WHEN CI.Type IN ('Invoice', 'Opening Invoice') THEN CID.ExtPriceRate ELSE 0 END AS Sales          
, CASE WHEN CI.Type IN ('Credit Memo', 'Opening Credit', 'Gift Card', 'Gift Certificate') THEN CID.QuantityShipped ELSE 0 END AS QtyReturns          
, CASE WHEN CI.Type IN ('Credit Memo', 'Opening Credit', 'Gift Card', 'Gift Certificate') THEN CID.ExtPriceRate ELSE 0 END AS [Returns]      

FROM CustomerInvoice CI            
INNER JOIN Customer C ON CI.BillToCode = C.CustomerCode            
INNER JOIN CustomerInvoiceDetail CID ON CI.InvoiceCode = CID.InvoiceCode
INNER JOIN InventoryItem ii ON CID.ItemCode = ii.ItemCode    
INNER JOIN InventoryCategory IC ON CID.ItemCode = IC.ItemCode
WHERE (CI.IsBatch = 0 OR CI.IsBatch IS NULL) 
AND CI.IsPosted = 1 
AND CI.[Type] = 'Invoice' AND CI.IsVoided = 0 
AND IC.IsPrimary = (SELECT MIN(IsPrimary) FROM InventoryCategory ICi WHERE ICi.ItemCode = IC.ItemCode)
) Items        

GROUP BY POSWorkstationID, BillToCode, ItemName, InvoiceDate, CategoryCode, WarehouseCode