mysql - 计算 View 中两个数据库之间具有偏移量的时间戳

Question

我正在尝试计算两个数据库之间的时间戳，但其中一个具有重叠的时间戳，这不是我的设计缺陷。

SELECT date(time + INTERVAL 8 HOUR) as day, COUNT(DISTINCT comment)
FROM  news.data
GROUP BY day
    UNION ALL
SELECT date(time + INTERVAL 8 HOUR) as day, COUNT(DISTINCT comment)
FROM`news-backup`.`data`
GROUP BY day
ORDER BY year(day) desc, day(day) DESC
LIMIT 20

似乎发生了什么，两个数据库的范围内都有一些时间戳，因此它们为特定日期生成单独的计数。所以它会从新闻和新闻备份中为今天计数

EX:
date      count
2013-1-15  10
2013-1-15  13
2013-1-14  8
2013-1-13  15

我想要的是

EX:
date      count
2013-1-15  23
2013-1-14  8
2013-1-13  15

这里有一个问题，我需要在 View 中使用它，所以它有一些限制(不允许子查询)。想法？不，我不能更改数据库之间发生的数据转储顺序

Answer 1

不能在 View 中放置子查询，但可以在 View 中放置 View 。

所以:

create view1 as 
    SELECT date(time + INTERVAL 8 HOUR) as day, 'current' as which, COUNT(DISTINCT comment) as cnt
    FROM  news.data
    GROUP BY day
        UNION ALL
    SELECT date(time + INTERVAL 8 HOUR) as day, 'backup' as which, COUNT(DISTINCT comment) as cnt
    FROM`news-backup`.`data`
    GROUP BY day, which

我不确定你将它们组合起来的逻辑是什么:

create view2 as
    select day, max(cnt)  -- sum(cnt)?  prefer current or backup?
    from view1
    group by day
    ORDER BY day desc

禁止子查询的文档是here .请务必搜索“SELECT 语句不能包含”。

如果您有一个包含所有日期的表格，则以下“荒谬的”SQL 可能会起作用:

select c.date,
       coalesce( (select count(distinct comment) from news.data where date(time + INTERVAL 8 HOUR) = c.date),
                 (select count(distinct comment) from news_backup.data where date(time + INTERVAL 8 HOUR) = c.date)
               ) as NumComments
from calendar c

此版本假设您首先需要"new"，然后是备份。如果您想要总和，则可以将它们相加。