mariadb 是否有可能查询计算2表中每个类别的总利润？

你好。
以下是支付Payments的表1
| 派门蒂德|单位|分支|佃农|姓名|数量|音符|付款日期|
| - ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|
| 1个|博德加|桑托|1个|阿尔萨斯阿尔萨斯|一万|刷新数据|2022年12月27日16时22分53秒|
| 第二章|博德加|桑托|1个|阿尔萨斯阿尔萨斯|小行星1333|沃达德|2022年11月22日19时17分45秒|
| 三个|博德加|哈辛托地铁站|1个|阿尔萨斯阿尔萨斯|一千|德瓦道道|2023年1月1日19时36分13秒|
| 四个|博德加|哈辛托地铁站|四个|奥德奥德|二○ ○ ○年|奥维德|2022年12月25日15时45分49秒|
以下是Expenses费用的表2
| 支出项|分支|费用种类|数量|音符|支出日期|
| - ------|- ------|- ------|- ------|- ------|- ------|
| 第二章|桑托|电力|二九九|阿达达德|2022年12月27日00时00分|
| 三个|马利诺|电力|二十个|达乌德|2022年12月27日00时00分|
| 四个|桑托|电力|小行星1111|阿达沃德|2022年12月27日00时00分|
| 五个|桑托|电力|三十|埃夫|2022年12月27日00时00分|
| 七|桑托|电力|一百|我们|2023年1月17日19时56分26秒|
| 八个|桑托|电力|二百|dw|2022年12月25日15时45分49秒|
我想得到这个表中每个分支的总利润，我用的查询是这样的：

SELECT payments.branch , SUM(payments.amount) AS Profits, SUM(expenses.amount) AS Expenses, SUM(payments.amount)  - SUM(expenses.amount) AS Total
FROM payments
RIGHT OUTER JOIN expenses
on payments.branch = expenses.branch
GROUP BY payments.branch

我尝试了上面的查询，但结果是这样的：

预期成果
| 分支|付款|费用|利润|
| - ------|- ------|- ------|- ------|
| 桑托|小行星1133|小行星11740|-407|
| 哈辛托地铁站|三千|无|三千|
| 马利诺|无|二十个|-20个|
如果你想在你的机器上试试，这里是查询

CREATE TABLE `expenses` (
  `expensesid` int(255) NOT NULL,
  `branch` varchar(255) NOT NULL,
  `typeofexpenses` varchar(255) NOT NULL,
  `amount` int(255) NOT NULL,
  `note` varchar(255) NOT NULL,
  `dateofexpenses` datetime NOT NULL
);

INSERT INTO `expenses` (`expensesid`, `branch`, `typeofexpenses`, `amount`, `note`, `dateofexpenses`) VALUES
(2, 'Santo', 'Electricity', 299, 'aadadad', '2022-12-27 00:00:00'),
(3, 'Maligno', 'Electricity', 20, 'daawd', '2022-12-27 00:00:00'),
(4, 'Santo', 'Electricity', 11111, 'adawd', '2022-12-27 00:00:00'),
(5, 'Santo', 'Electricity', 30, 'ef', '2022-12-27 00:00:00'),
(7, 'Santo', 'Electricity', 100, 'we', '2023-01-17 19:56:26'),
(8, 'Santo', 'Electricity', 200, 'dw', '2022-12-25 15:45:49');

CREATE TABLE `payments` (
  `paymentid` int(11) NOT NULL,
  `Unit` varchar(255) NOT NULL,
  `branch` varchar(255) NOT NULL,
  `tenantid` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `amount` int(11) NOT NULL,
  `note` varchar(255) NOT NULL,
  `dateofpayment` datetime NOT NULL
);

INSERT INTO `payments` (`paymentid`, `Unit`, `branch`, `tenantid`, `name`, `amount`, `note`, `dateofpayment`) VALUES
(1, 'Bodega', 'Santo', 1, 'Alsace Alsace', 10000, 'REFRESHED DATA', '2022-12-27 16:22:53'),
(2, 'Bodega', 'Santo', 1, 'Alsace Alsace', 1333, 'wawdad', '2022-11-22 19:17:45'),
(3, 'Bodega', 'Jacinto MRT', 1, 'Alsace Alsace', 1000, 'dwadawdaw', '2023-01-01 19:36:13'),
(4, 'Bodega', 'Jacinto MRT', 4, 'awd awdawd', 2000, 'awd', '2022-12-25 15:45:49');

一种方法是从完整的分支列表（子查询b）开始，然后左连接到其他两个表的聚合数据（子查询p和e）-

SELECT
    b.branch AS Branch,
    IFNULL(p.amount, 0) AS Payments,
    IFNULL(e.amount, 0) AS Expenses,
    IFNULL(p.amount, 0) - IFNULL(e.amount, 0) AS Profit
FROM (SELECT DISTINCT branch FROM expenses UNION SELECT DISTINCT branch FROM payments) AS b
LEFT JOIN (SELECT branch, SUM(amount) amount FROM payments GROUP BY branch) AS p ON b.branch = p.branch
LEFT JOIN (SELECT branch, SUM(amount) amount FROM expenses GROUP BY branch) AS e ON b.branch = e.branch;

由于您使用的是MariaDB〉= 10.2.1，我们可以使用Common Table Expressions来提高可读性，并且由于您已经声明您有一个单独的表用于branches，我将假设branches (branchid, name)和branchid而不是branch在expenses和payments表中。

WITH payments AS (
    SELECT branchid, SUM(amount) total FROM payments GROUP BY branchid
), expenses AS (
    SELECT branchid, SUM(amount) total FROM expenses GROUP BY branchid
)
SELECT
    b.name AS Branch,
    IFNULL(p.total, 0) AS Payments,
    IFNULL(e.total, 0) AS Expenses,
    IFNULL(p.total, 0) - IFNULL(e.total, 0) AS Profit
FROM branches AS b
LEFT JOIN payments AS p ON b.branchid = p.branchid
LEFT JOIN expenses AS e ON b.branchid = e.branchid;

mariadb 是否有可能查询计算2表中每个类别的总利润？

1条答案

相关问题

热门标签

最新问答