SQL语句完善

[禁用F3]

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)
我这个语句生存出来想改进一下
目前我这个语句生存出来的结果是
/*

Numbers                                            DateMonths              ClassNumber
-------------------------------------------------- ----------------------- --------------------------------------------------
001                                                2011-05-01 00:00:00.000 1
001                                                2011-05-02 00:00:00.000 9
001                                                2011-05-03 00:00:00.000 8
002                                                2011-05-01 00:00:00.000 9
002                                                2011-05-02 00:00:00.000 3
002                                                2011-05-03 00:00:00.000 4
*/
我想改成
Numbers                                            DateMonths              ClassNumber
-------------------------------------------------- ----------------------- --------------------------------------------------
001                                                2011-05-01 00:00:00.000 1
002                                                2011-05-01 00:00:00.000 9
001                                                2011-05-02 00:00:00.000 9
002                                                2011-05-02 00:00:00.000 3
001                                                2011-05-03 00:00:00.000 8
002                                                2011-05-03 00:00:00.000 4

[cd731107]

--最后加一句 
order by DateMonths,Numbers

[cd731107]

order by tempSurvival.DateMonths,tempSurvival.Numbers

[AcHerat]

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

[禁用F3]

没看明白

[AcHerat]

加了个排序字段，例如你上面的数据加排序后就是


001 --- 1
001 --- 2
001 --- 3
002 --- 1
002 --- 2
002 --- 3

查询结果集时按排序字段先排，然后按你需要的字段排。

[禁用F3]

我要的是我的SQL查出来就是
001 1
002 1
001 2
002 2
而不是我查询结果集后再做.

[Lyongt]

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

[AcHerat]

你可以把#t换成你那段查询，方法就是做排序字段，然后先按排序字段排，再按你需要的字段排序。