分组行号

[chunyingjun7212]

a表


b c
11 a
11 b
11 c
12 dd
12 ee
12 aa

想得到根据字段b 取得的行号

[二月十六]

根据字段b 取得的行号，是需要分组吗还是按顺序？

--测试数据
if not object_id(N'Tempdb..#a') is null
	drop table #a
Go
Create table #a([b] int,[c] nvarchar(22))
Insert #a
select 11,N'a' union all
select 11,N'b' union all
select 11,N'c' union all
select 12,N'dd' union all
select 12,N'ee' union all
select 12,N'aa'
Go
--测试数据结束
--分组
Select ROW_NUMBER()OVER(PARTITION BY b ORDER BY GETDATE()) AS num,* from #a
--按顺序
SELECT ROW_NUMBER()OVER(ORDER BY b) AS num,* FROM #a

[顺势而为1]

Select Row_No=row_number() over (Partition by c) From a表

[吉普赛的歌]

USE tempdb
GO
--测试数据
if not object_id(N'T') is null
    drop table T
Go
CREATE TABLE T(
	b INT,
	c VARCHAR(10)
)
GO
INSERT INTO T(b,c)
select 11,'a'
union select 11,'b'
union select 11,'c'
union select 12,'dd'
union select 12,'ee'
union select 12,'aa'

SELECT ROW_NUMBER() OVER (PARTITION BY b ORDER BY c) AS rid
,* 
FROM t
/*
rid	b	c
1	11	a
2	11	b
3	11	c
1	12	aa
2	12	dd
3	12	ee
*/
 

[道素]

 select year(sdate) as yr,month(sdate) as m,sno,sname,sum(case when (datediff(hour,checkin,checkout)+24)%24>=8 then 1 else 0 end)
 from #tmp_1
 group by sno,sname,year(sdate),month(sdate)

 	yr	m	sno	sname	(No column name)
1	2017	8	1906	王海	5
2	2017	9	1906	王海	3
3	2017	8	1913	游一秀	6
4	2017	9	1913	游一秀	3
5	2017	8	1962	梅青青	7
6	2017	9	1962	梅青青	5

[道素]

别的帖子的答复误贴到这里了。下面是很本帖相关的

;with t(b,a) AS
(
    select 11,N'a' union all
    select 11,N'b' union all
    select 11,N'c' union all
    select 12,N'dd' union all
    select 12,N'ee' union all
    select 12,N'aa'
)
select dense_rank()over(order by b) as gid,row_number()over(partition by b order by a) as rid, * from t

  	gid	rid	b	a
	1	1	11	a
	1	2	11	b
	1	3	11	c
	2	1	12	aa
	2	2	12	dd
	2	3	12	ee

[xiaoxiangqing]

用ROW_NUMBER()就行了