php - 如何计算条件相似的2个表的mysql结果？

我想将总共2个表的计数插入到goals的pivot_data列中。

我可以使它与1个表一起使用，但是我无法在此表上合并第二个表。我想念什么？

INSERT INTO pivot_data (date, goals)
SELECT CURDATE( ) AS today, COUNT( * ) AS goals

然后

FROM alerts_data
WHERE DATE( alerts_data_timestamp ) = CURDATE( ) AND alerts_data_status ='goal'

但我也想结合

FROM alerts_push_data
WHERE DATE( push_data_timestamp ) = CURDATE( ) AND push_data_status ='goal'

接着

ON DUPLICATE KEY UPDATE pivot_data.goals = VALUES(goals)

最佳答案

尝试使用UNION ALL：

INSERT INTO pivot_data (date, goals)
SELECT today, goals
FROM (
  SELECT CURDATE( ) AS today, SUM( goals ) AS goals
  FROM (
    SELECT COUNT(*) AS goals
    FROM alerts_data
    WHERE DATE( alerts_data_timestamp ) = CURDATE( )
          AND alerts_data_status ='goal'

    UNION ALL

    SELECT COUNT(*) AS goals
    FROM alerts_push_data
    WHERE DATE( push_data_timestamp ) = CURDATE( )
          AND push_data_status ='goal') AS s ) AS t
ON DUPLICATE KEY UPDATE pivot_data.goals = t.goals