# 如果用一条sql取每分类里的第一条数据？

0
[已解决问题] 解决于 2013-10-28 21:58

2
```CREATE table Cate
(CateID int,
CateName Nvarchar(20));

CREATE table Product
(ProductID int,
CateID int,
ProductName Nvarchar(20));

INSERT Cate
values
(1,'DemoA'),
(2,'DemoB'),
(3,'DemoC'),
(4,'DemoD')

INSERT PRODUCT
values
(1,1,'PA'),
(2,3,'PB'),
(3,2,'PC'),
(4,2,'PD'),
(5,4,'PE'),
(6,3,'PF'),
(7,1,'PG'),
(8,4,'PH')

SELECT * FROM product;
/*
ProductID    CateID    ProductName
1    1    PA
2    3    PB
3    2    PC
4    2    PD
5    4    PE
6    3    PF
7    1    PG
8    4    PH
*/```
```--方法一：
Select t.CateID,t.ProductID,t.ProductName
From
(Select CateID,ProductID,ProductName,
Row_Number() over(Partition By CATEID Order by productid Asc) RowID
From PRODUCT
) t WHERE t.RowID=1

--方法二：
SELECT CateID,ProductID,ProductName  FROM PRODUCT T
WHERE productID in (
SELECT top 1 productID  FROM PRODUCT
WHERE cateid=T.cateid order by cateID asc )

/*

CateID    ProductID    ProductName
1    1    PA
2    3    PC
3    2    PB
4    5    PE
*/```

0

jerry-Tom | 园豆：4077 (老鸟四级) | 2013-10-28 16:16
0

select * from product where id in(select min(id) from product group by categoryID)

select * from product where id in(select max(id) from product group by categoryID)

happydaily | 园豆：301 (菜鸟二级) | 2013-10-28 16:53
0

hpze2000 | 园豆：109 (初学一级) | 2013-10-28 16:54
0

0

您需要登录以后才能回答，未注册用户请先注册