关于order 和 item 的sql查询

[elkxie]

销售记录表中每一个定单（order）对应着多条记录，而每个定单的中每一个产品对应一条记录，如下：
order item
aa 11
aa 22
bb 11
bb 33
bb 44
问题：
如何找出item含有(或者不包含)'11'的定单数？

[elkxie]

对不起，应该是定单的编号

[Softlee81307]

select * from order where item='11' --- 是含有
select * from order where item<>'11' --不包含

[xiajianfeng]

--try
--含有

SELECT [order] FROM tb a
WHERE [order] IN (SELECT [order] FROM tb WHERE [order] = a.[order] AND item = '11')
GROUP BY [order]

[xiajianfeng]

--不含有


SELECT [order] FROM tb_0125 a
WHERE [order] IN (SELECT [order] FROM tb_0125 WHERE [order] = a.[order] AND item <> '11')
GROUP BY [order]

[xiajianfeng]

--不含有

SELECT [order] FROM tb a
WHERE [order] NOT IN (SELECT [order] FROM tb WHERE [order] = a.[order] AND item = '11')
GROUP BY [order]

[elkxie]

对不起，我开始可能简化了这个问题，现在看来简化可能有些问题：（
原题是这样的：找出定单总价（对应每个item 有price 和 qty）大于某值
即 sum(price*qty)>=80 (group by order) 同时含有(或者不含有)某个item

感谢各位关注

[xiajianfeng]

--try
--含有11

SELECT [order], SUM(price * qty) AS 总价 FROM tb a
WHERE [order] IN (SELECT [order] FROM tb_0125 WHERE [order] = a.[order] AND item = '11')
GROUP BY [order]
HAVING SUM(price * qty) > 80

--不含有
SELECT [order], SUM(price * qty) AS 总价 FROM tb a
WHERE [order] NOT IN (SELECT [order] FROM tb_0125 WHERE [order] = a.[order] AND item = '11')
GROUP BY [order]
HAVING SUM(price * qty) > 80