select .. into #t from .. where .. --楼主那一大段;with cte as ( select px = row_number() over (partition by numbers order by getdate()),* from #t )select * from cte order by px,numbers
Select * From (select tempSurvival.Numbers,tempSurvival.DateMonths , --以tempSurvival表的日期找日期字段 case datepart(dd, tempSurvival.DateMonths) WHEN 1 THEN day1 WHEN 2 THEN day2 WHEN 3 THEN day3 WHEN 4 THEN day4 WHEN 5 THEN day5 WHEN 6 THEN day6 WHEN 7 THEN day7 WHEN 8 THEN day8 WHEN 9 THEN day9 WHEN 10 THEN day10 WHEN 11 THEN day11 WHEN 12 THEN day12 WHEN 13 THEN day13 WHEN 14 THEN day14 WHEN 15 THEN day15 WHEN 16 THEN day16 WHEN 17 THEN day17 WHEN 18 THEN day18 WHEN 19 THEN day19 WHEN 20 THEN day20 WHEN 21 THEN day21 WHEN 22 THEN day22 WHEN 23 THEN day23 WHEN 24 THEN day24 WHEN 25 THEN day25 WHEN 26 THEN day26 WHEN 27 THEN day27 WHEN 28 THEN day28 WHEN 29 THEN day29 WHEN 30 THEN day30 WHEN 31 THEN day31 end AS ClassNumber from ( --从这里开始 select Numbers,dateadd(dd,number,convert(varchar(8),'2011-05-01 00:00:00.000',120)+'01') as DateMonths from master..spt_values a,Employees b where type='p' and dateadd(dd,number,convert(varchar(8),'2011-05-01 00:00:00.000',120)+'01')<convert(varchar(8),dateadd(m,1,'2011-05-01 00:00:00.000'),120)+'01' and b.State BETWEEN 1 AND 3 --到这里结束是自动生存员工编号与指定月的数据 ) tempSurvival inner join Scheduling on Scheduling.Numbers=tempSurvival.Numbers where datepart(mm,Scheduling.DateMonths)=datepart(mm,'2011-05-01 00:00:00.000') and not exists (select 1 from EmployeesAttendance where (convert(varchar(8),'2011-05-01 00:00:00.000',120)+'01')=DateMonths and EmployeesAttendance.Numbers=tempSurvival.Numbers) ) T Order By ClassNumber
--最后加一句 order by DateMonths,Numbers
order by tempSurvival.DateMonths,tempSurvival.Numbers
select .. into #t
from ..
where ..
--楼主那一大段;with cte as
(
select px = row_number() over (partition by numbers order by getdate()),*
from #t
)select *
from cte
order by px,numbers
001 1
002 1
001 2
002 2
而不是我查询结果集后再做.
Select *
From (select tempSurvival.Numbers,tempSurvival.DateMonths ,
--以tempSurvival表的日期找日期字段
case datepart(dd, tempSurvival.DateMonths)
WHEN 1 THEN day1 WHEN 2 THEN day2 WHEN 3 THEN day3 WHEN 4 THEN day4 WHEN 5 THEN day5
WHEN 6 THEN day6 WHEN 7 THEN day7 WHEN 8 THEN day8 WHEN 9 THEN day9 WHEN 10 THEN day10
WHEN 11 THEN day11 WHEN 12 THEN day12 WHEN 13 THEN day13 WHEN 14 THEN day14 WHEN 15 THEN day15
WHEN 16 THEN day16 WHEN 17 THEN day17 WHEN 18 THEN day18 WHEN 19 THEN day19 WHEN 20 THEN day20
WHEN 21 THEN day21 WHEN 22 THEN day22 WHEN 23 THEN day23 WHEN 24 THEN day24 WHEN 25 THEN day25
WHEN 26 THEN day26 WHEN 27 THEN day27 WHEN 28 THEN day28 WHEN 29 THEN day29 WHEN 30 THEN day30
WHEN 31 THEN day31
end AS ClassNumber
from
(
--从这里开始
select Numbers,dateadd(dd,number,convert(varchar(8),'2011-05-01 00:00:00.000',120)+'01') as DateMonths
from master..spt_values a,Employees b
where type='p'
and dateadd(dd,number,convert(varchar(8),'2011-05-01 00:00:00.000',120)+'01')<convert(varchar(8),dateadd(m,1,'2011-05-01 00:00:00.000'),120)+'01'
and b.State BETWEEN 1 AND 3
--到这里结束是自动生存员工编号与指定月的数据
) tempSurvival
inner join
Scheduling
on
Scheduling.Numbers=tempSurvival.Numbers
where datepart(mm,Scheduling.DateMonths)=datepart(mm,'2011-05-01 00:00:00.000')
and not exists (select 1 from EmployeesAttendance where (convert(varchar(8),'2011-05-01 00:00:00.000',120)+'01')=DateMonths and EmployeesAttendance.Numbers=tempSurvival.Numbers)
) T
Order By ClassNumber
order by DateMonths,Numbers