mysql - 如何使用 Union all 查询来获取不在表中的记录？

Question

我在 MYSQL 数据库中有一个具有以下格式的_ad_display 表

╔═════════════╦═════════╦═════════╦═════════════════════╗
║ addisplayId ║ restId  ║ clicked ║     displaytime     ║
╠═════════════╬═════════╬═════════╬═════════════════════╣
║          1  ║  20707  ║      0  ║ 2013-03-07 10:50:55 ║
║          2  ║  20707  ║      1  ║ 2013-03-07 10:55:55 ║
║          3  ║  20711  ║      1  ║ 2013-03-08 12:50:55 ║
║          4  ║  20712  ║      1  ║ 2013-03-08 01:50:55 ║
║          5  ║  20707  ║      1  ║ 2013-03-08 10:50:55 ║
║          6  ║  20708  ║      1  ║ 2013-03-09 12:50:55 ║
║          7  ║  20707  ║      1  ║ 2013-03-09 09:50:55 ║
║          8  ║  20708  ║      0  ║ 2013-03-10 10:50:55 ║
║          9  ║  20708  ║      0  ║ 2013-03-11 11:50:55 ║
║         10  ║  20707  ║      0  ║ 2013-03-11 12:50:55 ║
╚═════════════╩═════════╩═════════╩═════════════════════╝

现在我想要的是用最近7天的数据获取restId的记录

如果当前日期是 2013-3-12 那么我想显示 2013-3-5 到 2013-3-11 的数据

     DISPLAY TIME                   CLICKED     count of row(impression)
March, 05 2013 00:00:00+0000            0       0             
March, 06 2013 00:00:00+0000            0       0
March, 07 2013 00:00:00+0000            1       2
March, 08 2013 00:00:00+0000            1       1
March, 09 2013 00:00:00+0000            1       1
March, 10 2013 00:00:00+0000            1       1
March, 11 2013 00:00:00+0000            0       1

我想忽略时间..只想在明智地获取记录日期的同时关注日期我想获取 restId=10707 的记录，其中包含表中该日期的总行数和该日期的点击次数总和，如果表中没有任何日期在上周日期的记录，那么它应该打印该日期并在点击时打印 0，并在行数列中打印 0。

我在 SQL fiddle 中制作了一个虚拟表并应用了这个查询但它不起作用..所以任何人都可以指导我......我知道它非常难以理解..但是如果你对我的问题有任何疑问..然后将清除您以从您身边获得解决方案..

以下是我的查询，没有提供正确的数据

select
  displaytime,
  sum(impression) as clicked,
  (count(impression) -1) as impressions
from (
  select
    displaytime,
    a_ad_display.clicked as impression
  from
    a_ad_display
  where
    a_ad_display.displaytime
  BETWEEN
    date_add(CONVERT_TZ(CURDATE( ),'+00:00','-11:00'), interval -6 day)
    AND
    CONVERT_TZ(CURDATE( ),'+00:00','-11:00')
    and
    restId=10708
    group by
      a_ad_display.displaytime
    union distinct
    select
      CONVERT_TZ(CURDATE( ),'+00:00','-11:00'), 0
    union distinct
    select
      date_add(CONVERT_TZ(CURDATE( ),'+00:00','-11:00'), interval -1 day), 0
    union distinct
    select
      date_add(CONVERT_TZ(CURDATE( ),'+00:00','-11:00'), interval -2 day), 0
    union distinct
    select
      date_add(CONVERT_TZ(CURDATE( ),'+00:00','-11:00'), interval -3 day), 0
    union distinct
    select
      date_add(CONVERT_TZ(CURDATE( ),'+00:00','-11:00'), interval -4 day), 0
    union distinct
    select
      date_add(CONVERT_TZ(CURDATE( ),'+00:00','-11:00'), interval -5 day), 0
    union distinct
    select
      date_add(CONVERT_TZ(CURDATE( ),'+00:00','-11:00'), interval -6 day), 0
) as x
group by
  date(displaytime)
order by
  displaytime

请忽略代码 CONVERT_TZ(CURDATE( ),'+00:00','-11:00') 因为它只是将我的日期从 utc 转换为用户时区...

这是我的 sql fiddle 链接 http://sqlfiddle.com/#!2/73f07/1

Answer 1

此查询应显示正确的数据。不是一个非常“干净”的解决方案。如果您真的想包含没有数据的日期，您应该用日历表替换这些并集。

SELECT IFNULL(z.displaytime, x.displaytime), IFNULL(z.clicked, 0), IFNULL(z.count, 0)  FROM

  (select
    date(CONVERT_TZ(CURDATE( ),'+00:00','-11:00')) AS 'displaytime', 0 AS 'Clicked', 0 AS 'count of row(impression)'
  union distinct
  select
    date(date_add(CONVERT_TZ(CURDATE( ),'+00:00','-11:00'), interval -1 day)), 0, 0
  union distinct
  select
    date(date_add(CONVERT_TZ(CURDATE( ),'+00:00','-11:00'), interval -2 day)), 0, 0
  union distinct
  select
    date(date_add(CONVERT_TZ(CURDATE( ),'+00:00','-11:00'), interval -3 day)), 0, 0
  union distinct
  select
    date(date_add(CONVERT_TZ(CURDATE( ),'+00:00','-11:00'), interval -4 day)), 0, 0
  union distinct
  select
    date(date_add(CONVERT_TZ(CURDATE( ),'+00:00','-11:00'), interval -5 day)), 0, 0
  union distinct
  select
    date(date_add(CONVERT_TZ(CURDATE( ),'+00:00','-11:00'), interval -6 day)), 0, 0) AS x

LEFT JOIN

  (SELECT
    DATE(displaytime) AS 'displaytime',
    MAX(clicked) AS 'Clicked',
    COUNT(*) AS 'Count'
  FROM 
    a_ad_display
  WHERE (
    displaytime BETWEEN date_add(CURDATE(), interval -6 day) AND CURDATE()
  ) AND restId = 10707 
  GROUP BY DATE(displaytime)) AS z
ON x.displaytime = z.displaytime