我需要按年份统计记录,我做了一些查询,但没有得到正确的结果。以下是我的疑问。但这对我不起作用。有人能看看这个并给我正确的查询吗?任何帮助都将不胜感激。
SELECT
(SELECT count(DISTINCT id) FROM call_response WHERE disposition=0 AND user_id=pu.id ) AS `trueAlarm`,
(SELECT count(DISTINCT id) FROM call_response WHERE disposition=1 AND user_id=pu.id ) AS `falseAlarm`,
(SELECT count(DISTINCT id) FROM call_response WHERE disposition=2 AND user_id=pu.id ) AS `disregarded`,
YEAR(cr.created_date) AS `callYear`
FROM `call_response` AS `cr`
INNER JOIN `permit_users` AS `pu`
ON cr.user_id=pu.id
WHERE ( pu.is_deleted=0 AND pu.is_trashed=0 AND cr.is_deleted=0)
GROUP BY `callYear`
您想要的查询使用条件聚合或子查询,但不能同时使用这两种查询。换句话说,要么使用子查询,但不具有到call_response
的外部联接。或者,有外部联接,但没有子查询。
我会这样写查询:
SELECT count(distinct case when disposition = 0 AND user_id = pu.id then id end) as trueAlarm,
count(distinct case when disposition = 1 AND user_id = pu.id then id end) as falseAlarm,
count(distinct case when disposition = 2 AND user_id = pu.id then id end) as disregarded,
YEAR(cr.created_date) AS `callYear`
FROM `call_response` `cr` INNER JOIN
`permit_users` `pu`
ON cr.user_id = pu.id
WHERE pu.is_deleted = 0 AND pu.is_trashed = 0 AND cr.is_deleted = 0
GROUP BY `callYear`;