根据SQL中的特定条件查找Item

Question

我有以下表格结构:

事务表:

code    type        action  level   id  qty   OrderID
====================================================
10005   PRODUCT     RECIPE  0       0   1     O-123  
821     VALUE_MEAL  RECIPE  0       1   1     O-123
9       PRODUCT     RECIPE  1       1   1     O-123
7093    PRODUCT     RECIPE  1       1   1     O-123
10005   PRODUCT     CHOICE  1       1   1     O-123
721     VALUE_MEAL  RECIPE  0       2   1     O-123
9       PRODUCT     RECIPE  1       2   1     O-123
7093    PRODUCT     RECIPE  1       2   1     O-123
10005   PRODUCT     CHOICE  1       2   1     O-123

My Master table:

code      FullName   MenuCategory 
---------------------------------
821         ML2       Group2  
721         ML1       Group2   
9           fires     Group2   
10005       Orange    Group3 

期望结果集:

code    QTY with Meal   Sold Alone
------------------------------------
10005        2              1

在预期结果中，我们需要区分商品代码"10005";是与具有不同级别和id的VALUE_MEAL一起出售，还是作为同一订单中的PRODUCT单独出售?

示例:Code=821，包含三个主要子层(9,7093,10005)，Code= 721，包含三个主要子层(9,7093,10005)因此，这里我们知道2 QTY以10005的价格出售，因为它是VALUE_MEALS的一部分，而code = 10005的QTY作为独立产品的一部分

Answer 1

看起来您只是想要一个分组自连接，带有条件聚合

SELECT
t.code,
[QTY with Meal] = COUNT(t2.IsMeal),  -- only counts non nulls
[Sold Alone] = COUNT(*) - COUNT(t2.IsMeal)
FROM [Transaction] t
CROSS APPLY (
SELECT IsMeal =  CASE WHEN COUNT(*) > 0 THEN 1 ELSE NULL END
FROM [Transaction] t2
WHERE t2.id = t.id
AND t2.type = 'VALUE_MEAL'
) t2
WHERE t.code = 10005
GROUP BY
t.code;

您也可以将子查询中的COUNT(*)切换为EXISTS

Answer 2

对于每个(OrderID, code)唯一对，查询使用'level'列来确定项目是否已组合在一个餐

select OrderID, code, 
sum(case when [level]>0 then 1 else 0 end) [QTY with Meal],
sum(case when [level]=0 then 1 else 0 end) [Sold Alone]
from TransactionTable
group by OrderID, code
order by OrderID, code;