求每个类别选一个产品的SQL语句
一个表中有N个产品,如
//product
id classid productname
1 1 a
2 1 b
3 2 c
4 2 d
5 3 e
6 3 f
我现在想用一个查询,每个类别(ClassId)选出一个产品,请指点!
结果应为
id classid productname
1 1 a
3 2 c
5 3 e
[解决办法]
select *
from product,(select classid,productname=min(productname) from product group by classid) t
where classid=t.classid and productname=t.productname
[解决办法]
--上面第二種還是有bug，正確的為以下四種寫法
Create Table product
(id Int,
classid Int,
productname Varchar(10))
Insert product Select 1, 1, 'a '
Union All Select 2, 1, 'b '
Union All Select 3, 2, 'c '
Union All Select 4, 2, 'd '
Union All Select 5, 3, 'e '
Union All Select 6, 3, 'f '
GO
--方法一
Select * From product Where id In (Select Min(id) From product Group By classid)
--方法二
Select * From product A Where Not Exists(Select id From product Where classid = A.classid And id < A.id)
--方法三
Select * From product A Where id = (Select Min(id) From product Where classid = A.classid)
--方法四
Select A.* From product A
Inner Join
(Select classid, Min(id) As id From product Group By classid) B
On A.classid = B.classid And A.id = B.id
GO
Drop Table product
--Result
/*
idclassidproductname
11a
32c
53e
*/
