高手们帮我看一下我个语句条件的问题.

Question

下面的SQL语句
如果Scheduling表里面只有2011-05-01 00:00:00.000
5月的份才有day1-31这些日期字段里面有值.可我这样的查询.
我改成2011-06-01 00:00:00.000
改成6月份的话就出问题了.数据查出day1-31这些日期字段竟然是5月份的值了.
要怎么修改我这个SQL语句.select  temp.Numbers,temp.DateMonths ,case  datepart(dd, temp.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-06-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-06-01 00:00:00.000',120)+'01')<convert(varchar(8),dateadd(m,1,'2011-06-01 00:00:00.000'),120)+'01') temp
left join
Scheduling
on 
Scheduling.Numbers=temp.Numbers 
/*
Numbers                                            DateMonths
-------------------------------------------------- -----------------------
001                                                2011-05-01 00:00:00.000
002                                                2011-05-01 00:00:00.000
........
003                                                2011-05-31 00:00:00.000
004                                                2011-05-31 00:00:00.000
*/
--上面是执行temp表后的结果
/*
--这是Scheduling表的数段数据一直到day31,因为字段长就只列出day3了ID          Numbers                                            DateMonths              Day1                                               Day2                                               Day3
----------- -------------------------------------------------- ----------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
1           001                                                2011-05-01 00:00:00.000 1                                                  9                                                  8
2           002                                                2011-05-01 00:00:00.000 9                                                  4                                                  3
3           001                                                2011-06-01 00:00:00.000 NULL                                               NULL                                               NULL 
4           005                                                2011-06-01 00:00:00.000 NULL                                               NULL                                               NULL 
*/

Answer 1

不是吧,我表达能力是有点差..
我就是要我这个查询查Scheduling表6月份的.就是6月份的day1-day31字段里面的值

Answer 2

select  temp.Numbers,temp.DateMonths ,case (case when isdate(temp.DateMonths)=1 then datepart(dd, temp.DateMonths) else '' end)
    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
    else ''
end AS ClassNumber
from 
(select Numbers,dateadd(dd,number,convert(varchar(8),'2011-06-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-06-01 00:00:00.000',120)+'01')<convert(varchar(8),dateadd(m,1,'2011-06-01 00:00:00.000'),120)+'01') temp
left join
Scheduling
on 
Scheduling.Numbers=temp.Numbers ？？？？

Answer 3

你也没看懂?我是想改一下我这个SQL语句
--上面语句中
select Numbers,dateadd(dd,number,convert(varchar(8),'2011-06-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-06-01 00:00:00.000',120)+'01')<convert(varchar(8),dateadd(m,1,'2011-06-01 00:00:00.000'),120)+'01'
这段是生成6月份的数据
我要以生存的日期数据去查Scheduling表里面的相应字段的值出来.

Answer 4

查出日期后，与原表left jion一下，用日期进行关联

Answer 5

为什么要:
convert(varchar(8),'2011-06-01 00:00:00.000',120)+'01'
呢?
这不就是:
'2011-06-01'
么~!

Answer 6

/*select  temp.Numbers,temp.DateMonths ,case  datepart(dd, temp.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 Number,dateadd(dd,number,'2011-06-01') as DateMonths
from master..spt_values --a,Employees b
where type='p' 
and dateadd(dd,number,convert(varchar(8),'2011-06-01 00:00:00.000',120)+'01')<convert(varchar(8),dateadd(m,1,'2011-06-01 00:00:00.000'),120)+'01'
/*
) temp
left join
Scheduling
on 
Scheduling.Numbers=temp.Numbers 
*/
/*
Number      DateMonths
----------- -----------------------
0           2011-06-01 00:00:00.000
1           2011-06-02 00:00:00.000
2           2011-06-03 00:00:00.000
3           2011-06-04 00:00:00.000
4           2011-06-05 00:00:00.000
5           2011-06-06 00:00:00.000
6           2011-06-07 00:00:00.000
7           2011-06-08 00:00:00.000
8           2011-06-09 00:00:00.000
9           2011-06-10 00:00:00.000
10          2011-06-11 00:00:00.000
11          2011-06-12 00:00:00.000
12          2011-06-13 00:00:00.000
13          2011-06-14 00:00:00.000
14          2011-06-15 00:00:00.000
15          2011-06-16 00:00:00.000
16          2011-06-17 00:00:00.000
17          2011-06-18 00:00:00.000
18          2011-06-19 00:00:00.000
19          2011-06-20 00:00:00.000
20          2011-06-21 00:00:00.000
21          2011-06-22 00:00:00.000
22          2011-06-23 00:00:00.000
23          2011-06-24 00:00:00.000
24          2011-06-25 00:00:00.000
25          2011-06-26 00:00:00.000
26          2011-06-27 00:00:00.000
27          2011-06-28 00:00:00.000
28          2011-06-29 00:00:00.000
29          2011-06-30 00:00:00.000(30 行受影响)
*/

Answer 7

/*select  temp.Numbers,temp.DateMonths ,case  datepart(dd, temp.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 Number,dateadd(dd,number,'2011-06-01') as DateMonths
from master..spt_values --a,Employees b
where type='p' 
and convert(varchar(7),dateadd(dd,number,'2011-06-01'),120)='2011-06'
/*
) temp
left join
Scheduling
on 
Scheduling.Numbers=temp.Numbers 
*/
/*
Number      DateMonths
----------- -----------------------
0           2011-06-01 00:00:00.000
1           2011-06-02 00:00:00.000
2           2011-06-03 00:00:00.000
3           2011-06-04 00:00:00.000
4           2011-06-05 00:00:00.000
5           2011-06-06 00:00:00.000
6           2011-06-07 00:00:00.000
7           2011-06-08 00:00:00.000
8           2011-06-09 00:00:00.000
9           2011-06-10 00:00:00.000
10          2011-06-11 00:00:00.000
11          2011-06-12 00:00:00.000
12          2011-06-13 00:00:00.000
13          2011-06-14 00:00:00.000
14          2011-06-15 00:00:00.000
15          2011-06-16 00:00:00.000
16          2011-06-17 00:00:00.000
17          2011-06-18 00:00:00.000
18          2011-06-19 00:00:00.000
19          2011-06-20 00:00:00.000
20          2011-06-21 00:00:00.000
21          2011-06-22 00:00:00.000
22          2011-06-23 00:00:00.000
23          2011-06-24 00:00:00.000
24          2011-06-25 00:00:00.000
25          2011-06-26 00:00:00.000
26          2011-06-27 00:00:00.000
27          2011-06-28 00:00:00.000
28          2011-06-29 00:00:00.000
29          2011-06-30 00:00:00.000(30 行受影响)
*/

Answer 8

晴天,你执行的那个是生存了6月份所有日期的数据
我要用这个生存的数据去到Scheduling表里面找到当然的日期字段的数据
下面就是Scheduling表里面的数据日期字段day1就是1号,day2是2号....day31号ID          Numbers                                            DateMonths              Day1                                               Day2                                               Day3
----------- -------------------------------------------------- ----------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
1           001                                                2011-05-01 00:00:00.000 1                                                  9                                                  8
2           002                                                2011-05-01 00:00:00.000 9                                                  4                                                  3
3           001                                                2011-06-01 00:00:00.000 NULL                                               NULL                                               NULL 
4           005                                                2011-06-01 00:00:00.000 NULL                                               NULL                                               NULL 
*/

Answer 9

select  
temp.Numbers,
temp.DateMonths ,
CASE DATEPART(dd, temp.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, --#1.这个字段是a表还是b表的?
dateadd(dd,a.number,convert(varchar(8),'2011-06-01',120)+'01') as DateMonths
from master..spt_values a, Employees b
where a.[type]='p' --#2.a和b没有条件关联,难道是笛卡尔集?
and dateadd(dd,a.number,convert(varchar(8),'2011-06-01',120)+'01')<convert(varchar(8),dateadd(m,1,'2011-06-01'),120)+'01'
AND a.number = b.Numbers  --#3.是否应该加上一句,建立a表和b表的关系?
) temp
LEFT JOIN Scheduling
ON Scheduling.Numbers = temp.Numbers--问题分析: 你的temp表的值有问题.你的写法相当于下面,自己改一下吧,应该不难的:
select *
FROM
(
SELECT
number,
dateadd(dd,number,convert(varchar(8),'2011-06-01',120)+'01') as DateMonths
FROM master..spt_values
WHERE [type]='p'
AND dateadd(dd,number,convert(varchar(8),'2011-06-01',120)+'01')<convert(varchar(8),dateadd(m,1,'2011-06-01'),120)+'01'
) m
INNER JOIN dbo.Employees
ON 1 =1
--其实你对每个月日期的生成是对的,只是和Employees关联的时间有问题.

Answer 10

select  temp.Numbers,temp.DateMonths ,case datepart(dd, temp.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-06-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-06-01 00:00:00.000',120)+'01')<convert(varchar(8),dateadd(m,1,'2011-06-01 00:00:00.000'),120)+'01') temp
left join
Scheduling
on 
convert(varchar(8),Scheduling.DateMonths,120)=convert(varchar(8),temp.DateMonths ,120)  ----改了这里,但其实不太清楚你想怎么样~~

Answer 11

--我来点测试数据
select Numbers,dateadd(dd,number,convert(varchar(8),'2011-06-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-06-01 00:00:00.000',120)+'01')<convert(varchar(8),dateadd(m,1,'2011-06-01 00:00:00.000'),120)+'01'
--这个生存员工编号与当月的数据--Scheduling以下是就是Scheduling表里面的数据其中字段day1就是1号,day2是2号....day31号
ID          Numbers                                            DateMonths              Day1                                               Day2                                               Day3
----------- -------------------------------------------------- ----------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
1           001                                                2011-05-01 00:00:00.000 1                                                  9                                                  8
2           002                                                2011-05-01 00:00:00.000 9                                                  4                                                  3
3           001                                                2011-06-01 00:00:00.000 NULL                                               NULL                                               NULL 
4           005                                                2011-06-01 00:00:00.000 NULL                                               NULL                                               NULL 
*/我要解决的问题就是
在生存的数据里面以员工编号number与生成日期找到Scheduling员工相应的月份的日期字段的值

Answer 12

既然你的列里就有day30/day31,那么在6月份的时候肯定没有向这个字段里写东西去,再说,在表中不是有datemonths吗,在查询时直接用月份条件把其他行过滤掉,怎么会出现6月份有31的问题呢.即使列表有,值也是NULL,不会影响到结果吧.
所以,你这个查询貌似就应该
select id,numbers,datemonths,day1,day2......day31 from tb where month(datemonths)=@m
还要搞那些花样干嘛啊!

Answer 13

我这里存在有两张表.一张是生存的日期表.select Numbers,dateadd(dd,number,convert(varchar(8),'2011-06-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-06-01 00:00:00.000',120)+'01')<convert(varchar(8),dateadd(m,1,'2011-06-01 00:00:00.000'),120)+'01'把这个生存的表,以生存的员工编号,生存的日期,向Scheduling表里面查询相应月份的day1日期字段的值出来.

Answer 14

我的意思就是:
你根本用不着去生成日期表,只要拿相应的员工编号,向Scheduling表里面查询指定月份的所有日期的值,然后直接到应用程序里去处理,只显示该月存在的日子就行了.

Answer 15

我不需要.我只以那段生存的员工编号与日期的数据为条件.查询Scheduling表里面查询相应员工编号与月份的day1日期就可以了

Answer 16

如果一定得要在数据库里处理日期问题,可以考虑用动态语句,因为你是在不同的条件下查询不同数量的列:
declare @d varchar(10)
set @d='2011-06'
declare @sql nvarchar(max)
select day(dateadd(d,-1,dateadd(m,1,@d+'-01')))
set @sql='select numbers,datemonths,day1,day2,day3,.....此处省略100字..,day28'+
(case when day(dateadd(d,-1,dateadd(m,1,@d+'-01')))>=29 then ',day29' else '' end)+
(case when day(dateadd(d,-1,dateadd(m,1,@d+'-01')))>=30 then ',day30' else '' end)+
(case when day(dateadd(d,-1,dateadd(m,1,@d+'-01')))=31 then ',day31' else '' end)+
' from ClassNumber where number=''002'' and convert(varchar(7),DateMonths,120)='''+@d+''''
exec(@sql)

Answer 17

如果只要 day1 的值,那在这儿就不用生成日期了,这样不行吗:
select numbers,datemonths,day1 from ClassNumber where number='002' and convert(varchar(7),DateMonths,120)='2011-06'

Answer 18

我那个生存是生存指定月份的数据.如果一个月有30天就会生存30天.如果有31天就会生存31天.
我要用这个生存的添加去向Scheduling表查询相应天数的值出来.

Answer 19

我搞定了.解决结果是这样.
大家看一下.这样解决有没有不好的地方.我就加了一个where而已.
select  temp.Numbers,temp.DateMonths ,case  datepart(dd, temp.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-06-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-06-01 00:00:00.000',120)+'01')<convert(varchar(8),dateadd(m,1,'2011-06-01 00:00:00.000'),120)+'01') temp
inner join
Scheduling
on 
Scheduling.Numbers=temp.Numbers 
where datepart(mm,Scheduling.DateMonths)=datepart(mm,'2011-06-01 00:00:00.000')