求一sql语句,请高手帮忙啊!
一表:
id h_id (id是主键)
例如:有数据
1,100
2,100
3,100
4,101
5,101
6,101
7,100
现在我想出这样的数据:
1,100
2,100
3,100
4,101
5,101
6,101
也就是要查出以h_id为分组,id从小到大的所有的前三条刻录.请问怎么写sql?
一表:
id h_id (id是主键)
例如:有数据
1,100
2,100
3,100
4,101
5,101
6,101
7,100
现在我想出这样的数据:
1,100
2,100
3,100
4,101
5,101
6,101
也就是要查出以h_id为分组,id从小到大的所有的前三条刻录.请问怎么写sql?
select h_id from 一表
group by h_id
having count(*)>=3
) as b
where a.h_id=b.h_id
and a.id in (
select top 3 id from 一表 where h_id=a.h_id order by id )
Select * From TEST A Where Id In(Select TOP 3 ID From TEST Where h_id=A.h_id)
where id in (
select top 3 id from 一表 where h_id=a.h_id order by id )
Select * From TEST A Where Not Exists (Select 1 From TEST Where h_id=A.h_id And ID<A.ID Having Count(*)>2)
--方法三:
Select * From TEST A Where (Select Count(*) From TEST Where h_id=A.h_id And ID<A.ID)<3
(ID Int Identity(1,1),
h_id Int)
Insert TEST Select 100
Union All Select 100
Union All Select 100
Union All Select 101
Union All Select 101
Union All Select 101
Union All Select 100
GO
--方法一:
Select * From TEST A Where Id In(Select TOP 3 ID From TEST Where h_id=A.h_id)
--方法二:
Select * From TEST A Where Not Exists (Select 1 From TEST Where h_id=A.h_id And ID<A.ID Having Count(*)>2)
--方法三:
Select * From TEST A Where (Select Count(*) From TEST Where h_id=A.h_id And ID<A.ID)<3
GO
Drop Table TEST
GO
/*
ID h_id
1 100
2 100
3 100
4 101
5 101
6 101
*/
我有一个更难的问题 大家请进啊,好急啊
http://community.csdn.net/Expert/topic/4766/4766420.xml?temp=.8008386