复杂sql语句：按部门统计⼈数复杂的sql语句，按部门统计⼈数：
--按部门统计⼈数
SELECT o.OUGUID AS OUGUID, o.OUNAME AS OUNAME,
IFNULL(COUNT(DISTINCT o.USERGUID), 0) AS USERNUM,
SUM(CASE WHEN IFNULL(q.CALLTIME, 0) !=0THEN1ELSE0END) AS CallNoCount,
SUM(CASE WHEN q.ROW_ID ='1'THEN1ELSE0END) AS QuestionCount,
SUM(CASE WHEN q.`STATUS` ='3'THEN1ELSE0END) AS PassCount,sql统计每个系的学生人数
SUM(CASE WHEN q.`STATUS` ='1'OR q.`STATUS` ='2'THEN1ELSE0END) AS BanjianCount
FROM
(SELECT ROW_ID,`STATUS`,handleuserguid,CALLTIME FROM audit_queue UNION ALL
SELECT ROW_ID,`STATUS`,handleuserguid,CALLTIME FROM audit_queue_history) q
LEFT JOIN (SELECT o.OUNAME,o.OUGUID,u.USERGUID, u.DISPLAYNAME FROM frame_user u
LEFT JOIN frame_ou o ON u.OUGUID = o.OUGUID) o
ON q.handleuserguid = o.USERGUID
WHERE q.CALLTIME BETWEEN'2018-09-30 00:00:00'AND'2018-10-30 23:59:59'
GROUP BY o.OUGUID;
其中类似 SUM(CASE WHEN q.ROW_ID = '1' THEN 1 ELSE 0 END) AS QuestionCount 为按⼀定的条件计数；

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。