在构建MYSQL查询以计算单行/表中的两个内容时需要帮助 [英] Need help in building MYSQL query to count two content from single row/table

问题描述

我正在尝试获取在同一表名下列出的具有特定日期范围的两组数据的计数.

I'm trying to get count of two set of data which is listed under same table name, with specific date range.

表事件"

u_id event  Create
123  F_log  25-Sep-16
127  C_log  25-Sep-16
123  F_log  25-Sep-16
126  F_log  25-Sep-16
185  M_log  25-Sep-16
146  D_log  25-Sep-16
173  F_log  26-Sep-16
183  C_log  26-Sep-16
193  F_log  26-Sep-16
204  M_log  27-Sep-16
214  D_log  27-Sep-16
225  F_log  27-Sep-16

预期结果

Created    F_log    C_log
25-Sep-16   2       1
26-Sep-16   2       1

我的查询没有给出正确的结果；

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);

推荐答案

选择to_char(cast(创建为日期)，'YYYY-MM-DD')作为Report_date， count(distinct(case event_type in('Flash C Log')then user_id else 0 end)的形式为Flash_Log， count(distinct(当(event_type ='Client C Log')然后user_id否则为0结束的情况))作为Client_Log 从事件e 在"25-SEP-16"和"27-SEP-16"之间创建的位置 按to_char分组(播放(创建为日期)，'YYYY-MM-DD') 通过to_char(cast(按日期创建)，'YYYY-MM-DD')排序;

select to_char(cast(created as date),'YYYY-MM-DD') as Report_date, count(distinct(case when event_type in ('Flash C Log') then user_id else 0 end)) as Flash_Log, count(distinct(case when (event_type = 'Client C Log') then user_id else 0 end)) as Client_Log from events e where created between '25-SEP-16' and '27-SEP-16' group by to_char(cast(created as date),'YYYY-MM-DD') order by to_char(cast(created as date),'YYYY-MM-DD');

这篇关于在构建MYSQL查询以计算单行/表中的两个内容时需要帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！