表 id num pro
dg 1000 a
dg 2000 b
df 301 c
gt 450 d
id为订单号,num为订货数,pro为产品名。
写一条语句查询出所有一次性订购了不止一个产品的订单号?
dg 1000 a
dg 2000 b
df 301 c
gt 450 d
id为订单号,num为订货数,pro为产品名。
写一条语句查询出所有一次性订购了不止一个产品的订单号?
GROUP BY TB HAVING COUNT(1)>1
GROUP BY DG HAVING COUNT(1)>1
--SORRY
SELECT ID FROM TB
GROUP BY ID HAVING COUNT(1)>1
from tb
group by id
having count(*)>1
where exists(select 1 from tb where id=t.id AND NUM<> T.NUM and pro<>t.pro)
---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-07-31 15:27:14
---------------------------------
--> 生成测试数据表:tbIf not object_id('[tb]') is null
Drop table [tb]
Go
Create table [tb]([id] nvarchar(2),[num] int,[pro] nvarchar(1))
Insert tb
Select 'dg',1000,'a' union all
Select 'dg',2000,'b' union all
Select 'df',301,'c' union all
Select 'gt',450,'d'
Go
--Select * from tb-->SQL查询如下:
select distinct id from tb t where exists(select 1 from tb where id=t.id and pro<>t.pro)
/*
id
----
dg(1 行受影响)
*/
SELECT ID FROM TB
GROUP BY ID HAVING COUNT(1)>1)
where exists(select 1 from tb where id=t.id AND NUM<> T.NUM and pro<>t.pro)
引用 9 楼 yangzhixin 的回复:
恩!知道了!还有我要把这些单的信息都列出来应该怎么写? select * from tb where id in (select id from tb group by id having count(id)>1)
select id,num,pro from tb t where
(select count(1) from tb where id = t.id) > 1
/**
id num pro
---- ----------- ----
dg 1000 a
dg 2000 b(所影响的行数为 2 行)
**/