非常怪异的SQL现象! 求高手给分析1下原因,我都糊涂了!
DECLARE @a TABLE ([CardNum] [varchar](20) NULL,
[wDate] [datetime] NULL,
[wTime] [varchar](8) NULL)
INSERT INTO @a
select 'emp1','2013-03-01','07:55:25'
union
select 'emp1','2013-03-01','07:58:25'
union
select 'emp1','2013-03-01','12:18:25'
union
select 'emp1','2013-03-01','13:08:25'
union
select 'emp1','2013-03-01','19:05:25'
union
select 'emp2','2013-03-01','08:01:25'
union
select 'emp2','2013-03-01','08:05:25'
union
select 'emp2','2013-03-01','12:05:25'
union
select 'emp2','2013-03-01','13:05:25'
union
select 'emp2','2013-03-01','18:05:25'
SELECT CardNum,wDate,
MAX(CASE WHEN wTime BETWEEN '00:00:00' AND '08:00:59' THEN wTime ELSE '' end) ss,
MAX(CASE WHEN wTime BETWEEN '11:59:59' AND '12:46:00' THEN wTime ELSE '' END) sx,
MAX(CASE WHEN wTime BETWEEN '12:46:00' AND '13:31:00' THEN wTime ELSE '' END) xs,
MAX(CASE WHEN wTime BETWEEN '17:30:00' AND '23:59:59' THEN wTime ELSE '' END) xx
FROM @a
GROUP BY CardNum,wDate
--------------------------------------------------以上语句执行正确
SELECT CardNum,wDate,
MAX(CASE WHEN wTime BETWEEN '00:00:00' AND '08:00:59' THEN wTime ELSE '' end) ss,
MIN(CASE WHEN wTime BETWEEN '11:59:59' AND '12:46:00' THEN wTime ELSE '' END) sx,
MAX(CASE WHEN wTime BETWEEN '12:46:00' AND '13:31:00' THEN wTime ELSE '' END) xs,
MAX(CASE WHEN wTime BETWEEN '17:30:00' AND '23:59:59' THEN wTime ELSE '' END) xx
FROM @a
GROUP BY CardNum,wDate
--------------------------------------------------这条语句只是改了一个"MIN",为什么MIN的值就求不到了呢?
[解决办法]
MIN(CASE WHEN wTime BETWEEN '11:59:59' AND '12:46:00' THEN wTime ELSE '' END)
'asdf'
'233'
''
用min求出来当然是''了
[解决办法]
你把 ELSE '' 换成 ELSE NULL 试下
