SELECT
USERID,
SUM(ISNULL(加班,0)) AS 加班,
CASE WHEN ISWORKDAY=0 THEN SUM(HOLIDAYTYPE)/2 END AS 法定假日
FROM
ATTENDANCESTATISTICS
GROUP BY USERID
上面的代码执行的时候肯定是出错的。会提示ISWORKDAY没在Group By里面。但是因为这个ISWORKDAY有两种值:1和0。如果我将ISWORKDAY放到group by里面了,那么查出来的结果中通一个userid就会有两条。181 5
181 NULL
182 5
182 NULL所以我想有没有什么方法能解决这个冲突。谢谢各位了先。
SELECT
USERID,
SUM(ISNULL(加班,0)) AS 加班,
CASE WHEN max(ISWORKDAY)=0 THEN SUM(HOLIDAYTYPE)/2 END AS 法定假日
FROM
ATTENDANCESTATISTICS
GROUP BY USERID
试试...
USERID,
SUM(ISNULL(加班,0)) AS 加班,
sum(CASE WHEN ISWORKDAY=0 THEN HOLIDAYTYPE/2 END) AS 法定假日
FROM
ATTENDANCESTATISTICS
GROUP BY USERID
SELECT
USERID,
SUM(ISNULL(加班,0)) AS 加班,
SUM(CASE WHEN ISWORKDAY=0 THEN (HOLIDAYTYPE)/2 ELSE 0 END) AS 法定假日
FROM
ATTENDANCESTATISTICS
GROUP BY USERID
USERID,
SUM(ISNULL(加班,0)) AS 加班,
SUM(HOLIDAYTYPE)/2 AS 法定假日
FROM ATTENDANCESTATISTICS
WHERE ISWORKDAY=0
GROUP BY USERID-- 加个 where 条件就可以啦,不需要CASE 语句!