求一个SQL查询
0
悬赏园豆：20
[已解决问题]
解决于 2011-04-27 10:27
字段1 字段2 字段3
1 2 a
2 2 a
3 5 b
4 2 a
5 2 b
6 5 a
7 5 c
查询结果是
字段1 字段2 字段3
1 2 a
2 2 a
3 5 b
5 2 b
7 5 c
分组取前2条数据，并且当只有一条数据就取一条。
最佳答案
0
select top 2 * from tablename groupby x orderby ID asc;
收获园豆：20
其他回答(1)
0
IFNOTOBJECT_ID('[t_DemoA]') ISNULL
DROPTABLE[t_DemoA]
GO
CREATETABLE dbo.[t_DemoA] (
ID intNOTNULLIDENTITY (1, 1),
Y INTNOTNULL,
X Nvarchar(10) NULL
)
GO
--插入数据
INSERTINTO[t_DemoA](Y, X) values(2,'a')
INSERTINTO[t_DemoA](Y, X) values(2,'a')
INSERTINTO[t_DemoA](Y, X) values(5,'b')
INSERTINTO[t_DemoA](Y, X) values(2,'a')
INSERTINTO[t_DemoA](Y, X) values(2,'b')
INSERTINTO[t_DemoA](Y, X) values(5,'a')
INSERTINTO[t_DemoA](Y, X) values(5,'c')
GO
SELECT*FROM[t_DemoA] T
WHERE ID in (
SELECTtop2 ID FROM[t_DemoA]
WHERE x=T.X orderby ID asc )
/*
ID Y X
1 2 a
2 2 a
3 5 b
5 2 b
7 5 c
*/
DROPTABLE[t_DemoA]
GO
CREATETABLE dbo.[t_DemoA] (
ID intNOTNULLIDENTITY (1, 1),
Y INTNOTNULL,
X Nvarchar(10) NULL
)
GO
--插入数据
INSERTINTO[t_DemoA](Y, X) values(2,'a')
INSERTINTO[t_DemoA](Y, X) values(2,'a')
INSERTINTO[t_DemoA](Y, X) values(5,'b')
INSERTINTO[t_DemoA](Y, X) values(2,'a')
INSERTINTO[t_DemoA](Y, X) values(2,'b')
INSERTINTO[t_DemoA](Y, X) values(5,'a')
INSERTINTO[t_DemoA](Y, X) values(5,'c')
GO
SELECT*FROM[t_DemoA] T
WHERE ID in (
SELECTtop2 ID FROM[t_DemoA]
WHERE x=T.X orderby ID asc )
/*
ID Y X
1 2 a
2 2 a
3 5 b
5 2 b
7 5 c
*/
