我有一个问题,关于如何从两个不同的表中求和总值,然后在获得其总数后,我想减去它。 例如,我有表格"rsales"和"sales",下面有这些 ff vlue。
数据来自"Rsales"
id | total | pcode |
1 | 100 | 2143 |
2 | 100 | 2143 |
3 | 50 | 2222 |
4 | 50 | 2222 |
来自"销售"的数据
id | total | pcode |
7 | 100 | 2143 |
8 | 50 | 2222 |
我的问题是这个。 我想通过 PCODE 对销售额的所有"总"值求和和 Rsales 组的"总"值总和,然后在得到它的总和后,我想减去它。 我的页面必须是这样的。
total pcode
| 100 | 2143 |
| 50 | 2222 |
我有这个 FF 代码,但它不适合我
sql "select sum(rsales.total)- sum(sales.total) as t1 where pcode = rsales.pcode"
使用:
SELECT
SUM(r.total)-(
SELECT SUM(s.total)
FROM sales AS s WHERE r.pcode=s.pcode
) as total,
r.pcode
FROM rsales AS r
GROUP BY r.pcode;
输出:
+--+--+--+--+--+-
| total | pcode |
+--+--+--+--+--+-
| 100 | 2143 |
| 50 | 2222 |
+--+--+--+--+--+-
2 rows in set
你试过这样的事情吗?
SELECT
SUM(L.total) as lTotal, SUM(R.total) as rTotal
FROM
sales L
INNER JOIN rsales R
ON
R.pcode = L.pcode
GROUP BY L.pcode
如果从两个表中都获得预期值,则可以轻松地在 FROM 子句中添加添加和子结构。
不需要联接即可执行此操作。如果某些 pcode 仅在一个表中,则此解决方案有效:
select SUM(total), pcode from (
select sum(total) as total, pcode from rsales group by pcode
union all
select SUM(-total) as total, pcode from sales group by pcode) salesTables
group by pcode