mysql - 计算两个表中行 id 不匹配的 (*) 行。

Question

为此感到头疼:

我需要计算(*)从现在到 1 天前两个表中的行数，其中表一中的historyId!=表二中的historyId并且userId=？。然后我需要返回两个表的所有行的总和。

如果表一中的historyId与表二中的historyId相等，则只需将其计为一行，而不是两行。

查询一

    SELECT HOUR(date) as hr, historyId, COUNT(*) as num_rows 
    FROM webHistory WHERE userId=? AND date BETWEEN (SYSDATE() - INTERVAL 1 DAY) 
    AND SYSDATE() GROUP BY HOUR(date);

查询二

    SELECT HOUR(date) as hr, historyId, COUNT(*) as num_rows 
    FROM locationHistory WHERE userId=? AND date BETWEEN (SYSDATE() - INTERVAL 1 DAY) 
    AND SYSDATE() GROUP BY HOUR(date);

我一直在考虑加入，但我完全陷入困境，不知道下一步要搜索什么，更不用说冒险和测试的路线了。

Answer 1

我认为您想为此使用union:

select count(distinct historyid)
from (select hour(date) as hr, historyid
      from webhistory
      where userid = ? and date between sysdate() - interval 1 day and sysdate()
      union all
      select hour(date) as hr, historyid
      from locationhistory
      where userid = ? and date between sysdate() - interval 1 day and sysdate()
     ) t
group by hr;