求每个类别选一个产品的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
/*
id classid productname
1 1 a
3 2 c
5 3 e
*/