我有两个不同的表共享相同的列名。这些表列出了由“id”标识的相同产品。这些产品在两个表中具有不同的收入,并且在每个表中多次列出。
我想将两个表中相同产品的收入相加,并按总和排序。结果是首先对收入最高的产品进行排序。
我尝试过联合和联合,但似乎找不到正确的解决方案。
我试过联合查询...
SELECT id, SUM(rev) as total
FROM (
SELECT id, rev FROM table1 UNION ALL
SELECT id, rev FROM table2 UNION ALL
)
ORDER BY total DESC我尝试过联接查询...
SELECT table1.id,
table1.rev,
table2.id,
table2.rev,
(table1.rev + table2.rev) as revenue
FROM table1
INNER JOIN table2 ON table1.id = table2.id
ORDER BY revenue DESC发布于 2017-07-20 23:23:17
看起来你只需要根据ID分组然后..。除非我漏掉了什么。
select d.* from (
SELECT
table1.id,
Sum(table1.rev) + Sum(table2.rev) as revenue
FROM table1
INNER JOIN table2 ON table1.id = table2.id
GROUP BY
table1.id) d
order by d.revenue发布于 2017-07-20 23:28:31
SELECT id, SUM(rev) as total
FROM (
SELECT id, rev FROM table1 UNION ALL
SELECT id, rev FROM table2
)
GROUP BY id
ORDER BY total DESChttps://stackoverflow.com/questions/45218899
复制相似问题