; with aa as ( select EmplHisID,EmplID,EffDT,[Level],ROW_NUMBER() over ( order by getdate()) m from HRDW.dbo.EDW_Empl_His where EmplID in ('000288','014385') ),cte as ( select EmplHisID,EmplID,EffDT,[Level] ,m,sotd=1 from aa where m=1 union all select a.EmplHisID,a.EmplID,a.EffDT,a.[Level],a.m,sotd=(case when a.[Level]=b.[Level] then b.sotd else b.sotd+1 end ) from aa a,cte b where a.m=b.m+1 ) select * from cte option(maxrecursion 32767) 楼主试试这个
with t as (select EmplHisID,EmplID,EffDT,Level, row_number() over(partition by EmplID order by EffDT,Level) 'rn' from [temps] where EmplID in ('000288','014385')) select a.EmplHisID,a.EmplID,a.EffDT,a.Level, (select count(1)+1 from (select * from t b where b.EmplID=a.EmplID and b.rn<=a.rn) c left join (select * from t d where d.EmplID=a.EmplID and d.rn<=a.rn) e on c.rn=e.rn-1 where c.Level<>e.Level) 'number' from t a/* EmplHisID EmplID EffDT Level number -------------------- ---------- ----------------------- ---------- ----------- 0002882007091600 000288 2007-09-16 00:00:00.000 M2 1 0002882008070100 000288 2008-07-01 00:00:00.000 M3 2 0002882010010101 000288 2010-01-01 00:00:00.000 M4 3 0002882011022800 000288 2011-02-28 00:00:00.000 M3 4 0002882011030100 000288 2011-03-01 00:00:00.000 M3 4 0002882012010102 000288 2012-01-01 00:00:00.000 M4 5 0002882012010101 000288 2012-01-01 00:00:00.000 M4 5 0143852012010100 014385 2012-01-01 00:00:00.000 P5 1 0143852012010101 014385 2012-01-01 00:00:00.000 P6 2 0143852013111900 014385 2013-11-19 00:00:00.000 P6 2(10 row(s) affected) */
是这样吗: select [EmplHisID], [EmplID], [EffDT], [Level], DENSE_RANK() over(partition by emplid order by max_date ,level) rows from ( select *, MAX(effdt) over(partition by emplid,level,row) max_date from ( select *, rownum- ROW_NUMBER() over(partition by EmplID,level order by rownum) row from ( select EmplHisID,EmplID,EffDT,Level, ROW_NUMBER() over (partition by emplid order by effdt desc) rownum from dbo.temps where EmplID in ('000288','014385') )t )t )t order by EmplID desc,rows desc /* EmplHisID EmplID EffDT Level rows 0143852013111900 014385 2013-11-19 00:00:00.000 P6 2 0143852012010101 014385 2012-01-01 00:00:00.000 P6 2 0143852012010100 014385 2012-01-01 00:00:00.000 P5 1 0002882012010102 000288 2012-01-01 00:00:00.000 M4 5 0002882012010101 000288 2012-01-01 00:00:00.000 M4 5 0002882011030100 000288 2011-03-01 00:00:00.000 M3 4 0002882011022800 000288 2011-02-28 00:00:00.000 M3 4 0002882010010101 000288 2010-01-01 00:00:00.000 M4 3 0002882008070100 000288 2008-07-01 00:00:00.000 M3 2 0002882007091600 000288 2007-09-16 00:00:00.000 M2 1 */
SELECT DISTINCT t.EffDT , t.EmplHisID , t.EmplID , t.Level , t_index.row_index FROM dbo.temps t LEFT JOIN ( SELECT EmplID , Level , ROW_NUMBER() OVER ( ORDER BY Level ASC ) AS row_index FROM ( SELECT DISTINCT EmplID , Level FROM temps ) t ) t_index ON t.EmplID = t_index.EmplID AND t.Level = t_index.Level GROUP BY t.EmplID , t_index.row_index , t.EffDT , t.EmplHisID , t.Level
WITH T AS ( SELECT ROW_NUMBER() OVER ( ORDER BY EMPLID DESC, EmplHisID ) AS ID , * FROM [TEMPS] ), M AS ( SELECT * , NUM = ISNULL(( SELECT CASE WHEN T1.LEVEL = T2.LEVEL THEN ID ELSE ID + 1 END FROM T AS T2 WHERE T2.ID = T1.ID - 1 ), 1) FROM T AS T1 ) SELECT EMPLHISID , EMPLID , EFFDT , [LEVEL] , DENSE_RANK() OVER ( PARTITION BY LEFT(LEVEL, 1) ORDER BY NUM ) AS NUM FROM M
begin declare @t table(id int ,name varchar(50) , clazz int, greade int,Subject varchar(10)) declare @clazzid int DECLARE E1cursor cursor FOR select clazz from ttcc group by clazz OPEN E1cursor fetch next from E1cursor into @clazzid WHILE @@FETCH_STATUS = 0 begin insert into @t select * from ttcc where clazz=@clazzid order by greade asc FETCH NEXT from E1cursor into @clazzid end select * from @t CLOSE E1cursor /* 关闭游标 */ DEALLOCATE E1cursor end
select EmplHisID,EmplID,EffDT,[Level],ROW_NUMBER() over ( order by getdate()) m
from HRDW.dbo.EDW_Empl_His where EmplID in ('000288','014385')
),cte as (
select EmplHisID,EmplID,EffDT,[Level] ,m,sotd=1 from aa where m=1
union all
select a.EmplHisID,a.EmplID,a.EffDT,a.[Level],a.m,sotd=(case when a.[Level]=b.[Level] then b.sotd else b.sotd+1 end )
from aa a,cte b where a.m=b.m+1
)
select * from cte
option(maxrecursion 32767) 楼主试试这个
[EmplHisID] [varchar](50) NOT NULL,
[EmplID] [varchar](10) NOT NULL,
[EffDT] [datetime] NULL,
[Level] [varchar](10) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[temps] ([EmplHisID], [EmplID], [EffDT], [Level]) VALUES (N'0143852013111900', N'014385', CAST(0x0000A27B00000000 AS DateTime), N'P6')
INSERT [dbo].[temps] ([EmplHisID], [EmplID], [EffDT], [Level]) VALUES (N'0143852012010101', N'014385', CAST(0x00009FCB00000000 AS DateTime), N'P6')
INSERT [dbo].[temps] ([EmplHisID], [EmplID], [EffDT], [Level]) VALUES (N'0143852012010100', N'014385', CAST(0x00009FCB00000000 AS DateTime), N'P5')
INSERT [dbo].[temps] ([EmplHisID], [EmplID], [EffDT], [Level]) VALUES (N'0002882012010102', N'000288', CAST(0x00009FCB00000000 AS DateTime), N'M4')
INSERT [dbo].[temps] ([EmplHisID], [EmplID], [EffDT], [Level]) VALUES (N'0002882012010101', N'000288', CAST(0x00009FCB00000000 AS DateTime), N'M4')
INSERT [dbo].[temps] ([EmplHisID], [EmplID], [EffDT], [Level]) VALUES (N'0002882011030100', N'000288', CAST(0x00009E9900000000 AS DateTime), N'M3')
INSERT [dbo].[temps] ([EmplHisID], [EmplID], [EffDT], [Level]) VALUES (N'0002882011022800', N'000288', CAST(0x00009E9800000000 AS DateTime), N'M3')
INSERT [dbo].[temps] ([EmplHisID], [EmplID], [EffDT], [Level]) VALUES (N'0002882010010101', N'000288', CAST(0x00009CF100000000 AS DateTime), N'M4')
INSERT [dbo].[temps] ([EmplHisID], [EmplID], [EffDT], [Level]) VALUES (N'0002882008070100', N'000288', CAST(0x00009ACC00000000 AS DateTime), N'M3')
INSERT [dbo].[temps] ([EmplHisID], [EmplID], [EffDT], [Level]) VALUES (N'0002882007091600', N'000288', CAST(0x000099AB00000000 AS DateTime), N'M2')这是建表语句 查询结果要是这样的:
EmplHisID EmplID EffDT Level number
0143852013111900 014385 2013-11-19 00:00:00.000 P6 2
0143852012010101 014385 2012-01-01 00:00:00.000 P6 2
0143852012010100 014385 2012-01-01 00:00:00.000 P5 1
0002882012010102 000288 2012-01-01 00:00:00.000 M4 5
0002882012010101 000288 2012-01-01 00:00:00.000 M4 5
0002882011030100 000288 2011-03-01 00:00:00.000 M3 4
0002882011022800 000288 2011-02-28 00:00:00.000 M3 4
0002882010010101 000288 2010-01-01 00:00:00.000 M4 3
0002882008070100 000288 2008-07-01 00:00:00.000 M3 2
0002882007091600 000288 2007-09-16 00:00:00.000 M2 1
with t as
(select EmplHisID,EmplID,EffDT,Level,
row_number() over(partition by EmplID order by EffDT,Level) 'rn'
from [temps]
where EmplID in ('000288','014385'))
select a.EmplHisID,a.EmplID,a.EffDT,a.Level,
(select count(1)+1 from
(select * from t b where b.EmplID=a.EmplID and b.rn<=a.rn) c
left join
(select * from t d where d.EmplID=a.EmplID and d.rn<=a.rn) e on c.rn=e.rn-1
where c.Level<>e.Level) 'number'
from t a/*
EmplHisID EmplID EffDT Level number
-------------------- ---------- ----------------------- ---------- -----------
0002882007091600 000288 2007-09-16 00:00:00.000 M2 1
0002882008070100 000288 2008-07-01 00:00:00.000 M3 2
0002882010010101 000288 2010-01-01 00:00:00.000 M4 3
0002882011022800 000288 2011-02-28 00:00:00.000 M3 4
0002882011030100 000288 2011-03-01 00:00:00.000 M3 4
0002882012010102 000288 2012-01-01 00:00:00.000 M4 5
0002882012010101 000288 2012-01-01 00:00:00.000 M4 5
0143852012010100 014385 2012-01-01 00:00:00.000 P5 1
0143852012010101 014385 2012-01-01 00:00:00.000 P6 2
0143852013111900 014385 2013-11-19 00:00:00.000 P6 2(10 row(s) affected)
*/
select [EmplHisID], [EmplID], [EffDT], [Level],
DENSE_RANK() over(partition by emplid order by max_date ,level) rows
from
(
select *,
MAX(effdt) over(partition by emplid,level,row) max_date
from
(
select *,
rownum- ROW_NUMBER() over(partition by EmplID,level order by rownum) row
from
(
select EmplHisID,EmplID,EffDT,Level,
ROW_NUMBER() over (partition by emplid order by effdt desc) rownum
from dbo.temps
where EmplID in ('000288','014385')
)t
)t
)t
order by EmplID desc,rows desc
/*
EmplHisID EmplID EffDT Level rows
0143852013111900 014385 2013-11-19 00:00:00.000 P6 2
0143852012010101 014385 2012-01-01 00:00:00.000 P6 2
0143852012010100 014385 2012-01-01 00:00:00.000 P5 1
0002882012010102 000288 2012-01-01 00:00:00.000 M4 5
0002882012010101 000288 2012-01-01 00:00:00.000 M4 5
0002882011030100 000288 2011-03-01 00:00:00.000 M3 4
0002882011022800 000288 2011-02-28 00:00:00.000 M3 4
0002882010010101 000288 2010-01-01 00:00:00.000 M4 3
0002882008070100 000288 2008-07-01 00:00:00.000 M3 2
0002882007091600 000288 2007-09-16 00:00:00.000 M2 1
*/
t.EffDT ,
t.EmplHisID ,
t.EmplID ,
t.Level ,
t_index.row_index
FROM dbo.temps t
LEFT JOIN ( SELECT EmplID ,
Level ,
ROW_NUMBER() OVER ( ORDER BY Level ASC ) AS row_index
FROM ( SELECT DISTINCT
EmplID ,
Level
FROM temps
) t
) t_index ON t.EmplID = t_index.EmplID
AND t.Level = t_index.Level
GROUP BY t.EmplID ,
t_index.row_index ,
t.EffDT ,
t.EmplHisID ,
t.Level
WITH T AS ( SELECT ROW_NUMBER() OVER ( ORDER BY EMPLID DESC, EmplHisID ) AS ID ,
*
FROM [TEMPS]
),
M AS ( SELECT * ,
NUM = ISNULL(( SELECT CASE WHEN T1.LEVEL = T2.LEVEL
THEN ID
ELSE ID + 1
END
FROM T AS T2
WHERE T2.ID = T1.ID - 1
), 1)
FROM T AS T1
)
SELECT EMPLHISID ,
EMPLID ,
EFFDT ,
[LEVEL] ,
DENSE_RANK() OVER ( PARTITION BY LEFT(LEVEL, 1) ORDER BY NUM ) AS NUM
FROM M
结果如下:
EMPLHISID EMPLID EFFDT LEVEL NUM
0002882007091600 000288 2007-09-16 00:00:00.000 M2 1
0002882008070100 000288 2008-07-01 00:00:00.000 M3 2
0002882010010101 000288 2010-01-01 00:00:00.000 M4 3
0002882011022800 000288 2011-02-28 00:00:00.000 M3 4
0002882011030100 000288 2011-03-01 00:00:00.000 M3 4
0002882012010101 000288 2012-01-01 00:00:00.000 M4 5
0002882012010102 000288 2012-01-01 00:00:00.000 M4 5
0143852012010100 014385 2012-01-01 00:00:00.000 P5 1
0143852012010101 014385 2012-01-01 00:00:00.000 P6 2
0143852013111900 014385 2013-11-19 00:00:00.000 P6 2
declare @t table(id int ,name varchar(50) , clazz int, greade int,Subject varchar(10))
declare @clazzid int
DECLARE E1cursor cursor
FOR select clazz from ttcc group by clazz
OPEN E1cursor
fetch next from E1cursor into @clazzid
WHILE @@FETCH_STATUS = 0
begin
insert into @t select * from ttcc where clazz=@clazzid order by greade asc
FETCH NEXT from E1cursor into @clazzid
end
select * from @t
CLOSE E1cursor /* 关闭游标 */
DEALLOCATE E1cursor
end