表:rs_kqskjl
id0000 sksj00 ygid00 sxblx0
125 2009-02-05 08:25:10 10001 1
126 2009-02-05 12:25:10 10001 2
127 2009-02-05 14:25:10 10001 3
127 2009-02-05 18:25:10 10001 4指定语句
select
case when sxblx0 ='1' then sksj00 else 0 end as '上班时间1',
case when sxblx0 ='2' then sksj00 else 0 end as '下班时间1',
case when sxblx0 ='3' then sksj00 else 0 end as '上班时间2',
case when sxblx0 ='4' then sksj00 else 0 end as '下班时间2'
from rs_kqskjl where sksj00 like '2009-02-05%' AND ygid00='10000001' group by ygid00为什么执行后结果会这样,,什么原因
上班时间1 下班时间1 上班时间2 下班时间2
2009-02-05 08:25:10 0 0 0
id0000 sksj00 ygid00 sxblx0
125 2009-02-05 08:25:10 10001 1
126 2009-02-05 12:25:10 10001 2
127 2009-02-05 14:25:10 10001 3
127 2009-02-05 18:25:10 10001 4指定语句
select
case when sxblx0 ='1' then sksj00 else 0 end as '上班时间1',
case when sxblx0 ='2' then sksj00 else 0 end as '下班时间1',
case when sxblx0 ='3' then sksj00 else 0 end as '上班时间2',
case when sxblx0 ='4' then sksj00 else 0 end as '下班时间2'
from rs_kqskjl where sksj00 like '2009-02-05%' AND ygid00='10000001' group by ygid00为什么执行后结果会这样,,什么原因
上班时间1 下班时间1 上班时间2 下班时间2
2009-02-05 08:25:10 0 0 0
GO
CREATE TABLE rs_kqskjl
(id0000 INT, sksj00 DATETIME, ygid00 VARCHAR(20), sxblx0 INT)
INSERT rs_kqskjl SELECT 125 , '2009-02-05 08:25:10', 10001 , 1
INSERT rs_kqskjl SELECT 126 , '2009-02-05 12:25:10', 10001 , 2
INSERT rs_kqskjl SELECT 127 , '2009-02-05 14:25:10', 10001 , 3
INSERT rs_kqskjl SELECT 127, '2009-02-05 18:25:10', 10001 , 4
select case when sxblx0 ='1' then sksj00 else NULL end as '上班时间1',
case when sxblx0 ='2' then sksj00 else NULL end as '下班时间1',
case when sxblx0 ='3' then sksj00 else NULL end as '上班时间2',
case when sxblx0 ='4' then sksj00 else NULL end as '下班时间2'
from rs_kqskjl where CONVERT(VARCHAR(10),sksj00,120) like '%2009-02-05%' AND ygid00='10001'
/*上班时间1 下班时间1 上班时间2 下班时间2
------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------
2009-02-05 08:25:10.000 NULL NULL NULL
NULL 2009-02-05 12:25:10.000 NULL NULL
NULL NULL 2009-02-05 14:25:10.000 NULL
NULL NULL NULL 2009-02-05 18:25:10.000
*/
max(case when sxblx0 ='1' then sksj00 else null end) as '上班时间1',
max(case when sxblx0 ='2' then sksj00 else null end) as '下班时间1',
max(case when sxblx0 ='3' then sksj00 else null end) as '上班时间2',
max(case when sxblx0 ='4' then sksj00 else null end) as '下班时间2'
from rs_kqskjl where sksj00 like '2009-02-05%' AND ygid00='10001'
group by ygid00
(id0000 INT, sksj00 DATETIME, ygid00 VARCHAR(20), sxblx0 varchar(2))
INSERT @t SELECT 125 , '2009-02-05 08:25:10', '10001' , '1'
INSERT @t SELECT 126 , '2009-02-05 12:25:10', '10001' , '2'
INSERT @t SELECT 127 , '2009-02-05 14:25:10', '10001' , '3 '
INSERT @t SELECT 127, '2009-02-05 18:25:10', '10001' , '4'
select * from @tselect
max(case when sxblx0 ='1' then sksj00 else null end) as '上班时间1',
max(case when sxblx0 ='2' then sksj00 else null end) as '下班时间1',
max(case when sxblx0 ='3' then sksj00 else null end) as '上班时间2',
max(case when sxblx0 ='4' then sksj00 else null end) as '下班时间2'
from @t
where sksj00 between '2009-02-05' and '2009-2-5 23:59:59' AND ygid00='10001'
group by ygid00 /*(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)id0000 sksj00 ygid00 sxblx0
----------- ------------------------------------------------------ -------------------- ------
125 2009-02-05 08:25:10.000 10001 1
126 2009-02-05 12:25:10.000 10001 2
127 2009-02-05 14:25:10.000 10001 3
127 2009-02-05 18:25:10.000 10001 4(所影响的行数为 4 行)上班时间1 下班时间1 上班时间2 下班时间2
------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------
2009-02-05 08:25:10.000 2009-02-05 12:25:10.000 2009-02-05 14:25:10.000 2009-02-05 18:25:10.000(所影响的行数为 1 行)警告: 聚合或其它 SET 操作消除了空值。
*/