我一直在研究这个问题,尝试各种解决方案,但就是无法正常工作。 我认为这并不那么复杂。 我有两个具有公共字段的表,其中一个是"jobamount"。 两个表是"现场音乐"和"VO"。 我只是想使用 UNION 获取每个表的所有值的总和。 所以,我现在的基本陈述是:
SELECT SUM(jobamount) FROM LiveMusic WHERE jobdate LIKE '2019%'
UNION ALL
SELECT SUM(jobamount) FROM VO WHERE jobdate LIKE '2019%'
就我得到两个结果而言,这工作正常,每个表中所有值的总和。 我该如何SUM
这些,GROUP
它们,无论如何? 我只需要将它们合并为一个。 多谢!
你可以对总和求和:
SELECT SUM(jobsum) AS totalsum FROM (
SELECT SUM(jobamount) AS jobsum FROM LiveMusic WHERE jobdate LIKE '2019%' UNION ALL SELECT SUM(jobamount) AS jobsum FROM VO WHERE jobdate LIKE '2019%'
) sums;
或者将全部并集后的值求和:
SELECT SUM(jobamount) AS totalsum FROM (
SELECT jobamount FROM LiveMusic WHERE jobdate LIKE '2019%' UNION ALL SELECT jobamount AS jobsum FROM VO WHERE jobdate LIKE '2019%'
) jobamounts;
您可以对这两个查询运行 EXPLAIN ,以查看哪一个查询运行得更快。
您可以使用子查询:
SELECT SUM(X.jobamount)
FROM (
SELECT jobamount FROM LiveMusic WHERE jobdate LIKE '2019%'
UNION ALL
SELECT jobamount FROM VO WHERE jobdate LIKE '2019%'
) X
像这样。
SELECT
SUM(amount_sum)
FROM
(
SELECT
SUM(jobamount) AS amount_sum
FROM
LiveMusic
WHERE
jobdate LIKE '2019%'
UNION ALL
SELECT
SUM(jobamount) AS amount_sum
FROM
VO
WHERE
jobdate LIKE '2019%'
) t