group by 分组取值

来源：百度知道编辑：UC知道时间：2024/07/02 04:18:18

tb_FactoryName
ID factory JCheng DateTime
1 a 1 2009-5-5
2 b 1 2009-5-5
3 c 1 2009-5-5
4 d 1 2009-5-5
5 a1 2 2009-5-4
6 a2 2 2009-5-4
7 a3 2 2009-5-4
8 a4 2 2009-5-4
9 b1 3 2009-4-4
10 b2 3 2009-4-4
11 b3 3 2009-4-3
12 b4 3 2009-4-4
13 c1 4 2009-3-3
14 c2 4 2009-3-3
15 c3 4 2009-3-3
各位大侠我想达到这样的效果按照JCheng分组根据ID 只取每组的前两个数据怎么实现啊？先谢谢了小弟分不多以后有了再补上呵呵
效果如下
ID factory JCheng

SQL2005\08可用用cross apply/outer apply
select ID=row_number()over(order by b.ID),b.*
from (select distinct JCheng from tb_FactoryName)a
cross apply
(select Top 2 * from tb_FactoryName where JCheng =a.JCheng order by ID asc)b

SQL2000：
Select * from tb_FactoryName a
where (select count(1) from tb_FactoryName where [JCheng]=a.[JCheng] and ID<=a.ID) <=2

第一列生成连接时，可用临时表identity(int,1,1) as ID

SQL2000
select Top 2 ID factory JCheng DateTime
from tb_FactoryName
where JCheng =a.JCheng
group by JCheng ID
说明：group by JCheng ID
（1）group by JCheng是根据JCheng分组
（2）group by JCheng ID 后面加个ID是因为你想根据ID 只取每组的前两个数据，那么就必须在根据JCheng分组的情况下在对ID进行分组，取前2位用 top 2 表示！

group by按月分组统计(SQL) asp中 select分组查询group by问题 SQL语句：用count求group by分组的个数 sql查询 group by SQL group by 问题 mssql group by 问题 SQL GROUP BY 问题请教group by ,谢谢!!!!!! SQL中的GROUP BY sql中关于group by