本文介绍了建立mysql查询以计算同一表/行下的特定数据的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
Table name "events"
user_id event_type Created 123 Flash C log 25-Sep-16 127 Client C log 25-Sep-16 123 Flash C log 25-Sep-16 126 Flash C log 25-Sep-16 185 Monitor log 25-Sep-16 146 Delete log 25-Sep-16 173 Flash C log 26-Sep-16 183 Client C log 26-Sep-16 193 Flash C log 26-Sep-16 204 Monitor log 27-Sep-16 214 Delete log 27-Sep-16 225 Flash C log 27-Sep-16
期望结果
Date Flash C log Client C log 25-Sep-16 2 1 26-Sep-16 2 1 27-Sep-16 1 0我的查询没有给出正确的结果;
My query which is not giving correct result;
SELECT COUNT (DISTINCT e.user_id) AS Flash, COUNT (DISTINCT et.user_id) AS Client, TO_CHAR (e.created) AS Date_created FROM events e INNER JOIN events et ON e.user_id = et.user_id WHERE e.created BETWEEN '25-SEP-15' AND '27-SEP-15' AND e.event_type = 'Flash C log' AND et.event_type = 'Client C log' GROUP BY TO_CHAR (e.created);推荐答案
必须在单独的子查询中完成COUNT个操作.这应该起作用.
The COUNTs has to be done in separate subqueries. This should work.
纠正了一些愚蠢的错误,并在答案中加入了小提琴.
Corrected a couple of stupid mistakes and included a fiddle with the answer.
SELECT DISTINCT E.created,F.flash,C.client FROM events E LEFT JOIN ( SELECT created,COUNT(DISTINCT user_id) as flash FROM events WHERE event_type LIKE 'Flash C log' GROUP BY created ) F ON F.created = E.created LEFT JOIN ( SELECT Created,COUNT(DISTINCT user_id) as client FROM events WHERE event_type LIKE 'Client C log' GROUP BY created ) C ON C.created = E.created WHERE E.created BETWEEN '2016-09-25' AND '2016-09-27'对结果进行修饰
更多推荐
建立mysql查询以计算同一表/行下的特定数据
发布评论