建立mysql查询以计算同一表/行下的特定数据

本文介绍了建立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'

对结果进行修饰