一、以PersonID,classid,dt_ClassData为条件进行分组,每个小组加序号,row_number在sql2005中不可用
方法一、sql2000及以上版本
--以PersonID,classid,dt_ClassData为条件进行分组,每组加序号
select ID
,(select count(1) from m_LampHistoryDataGet as b
where a.dt_ClassData=b.dt_ClassData
and a.classid=b.classid
and a.PersonID=b.PersonID
and b.id<=a.id
) as getnum
,[PersonID]
,[ClassID]
,[dt_GetTime]
,[dt_ClassData]
from m_LampHistoryDataGet as a
order by PersonID,dt_GetTime
方法二、用于sql2005及以上版本
--以PersonID,classid,dt_ClassData为条件进行分组,每组加序号
select ID
,ROW_NUMBER() over(partition by PersonID,classid,dt_ClassData order by id) as getnum
,[PersonID]
,[ClassID]
,[dt_GetTime]
,[dt_ClassData]
from m_LampHistoryDataGet as a
order by PersonID,dt_GetTime
二、 显示结果如下
ID getnum PersonID ClassID dt_GetTime dt_ClassData
1 1 6 1 2015-01-15 14:50:03.000 2015-01-15 00:00:00.000
2 2 6 1 2015-01-15 14:50:06.000 2015-01-15 00:00:00.000
3 3 6 1 2015-01-15 14:50:10.000 2015-01-15 00:00:00.000
7 4 6 1 2015-01-15 14:50:51.000 2015-01-15 00:00:00.000
4 1 6 1 2015-01-16 14:50:45.000 2015-01-16 00:00:00.000
5 1 143 1 2015-01-15 14:50:45.000 2015-01-15 00:00:00.000
8 2 143 1 2015-01-15 14:50:51.000 2015-01-15 00:00:00.000
6 1 144 1 2015-01-15 14:50:45.000 2015-01-15 00:00:00.000