mysql - 如何从此表中计算总计和小计
问题描述
我有 2 个如下表:
表 list_acc:
id_acc nama_acc parent_id_acc level_acc pattern_volume id_sat
------- ---------------- -------------- ---------- -------------- -----------
1 activities 1 (NULL) 1 (NULL) 1
2 sub activities 1 1 2 1 1
3 sub activities 2 1 2 1 1
4 sub activities 3 1 2 1 1
5 item sa 3 (1) 4 3 1 1
6 item sa 3 (2) 4 3 1 1
7 item sa 3 (3) 4 3 1 1
8 sub activities 4 1 2 1 1
9 activities 2 (NULL) 1 (NULL) 1
10 sub activities 1 9 2 1 2
11 sub activities 2 9 2 1 2
12 sub activities 3 9 2 1 2
13 sub activities 4 9 2 1 2
14 sub activities 5 9 2 1 2
表名义:
id_nominal id_acc volume nominal id_user
---------- ------- ------ ----------- ---------
1 2 2.00 15000000.00 1
2 3 2.00 30000000.00 1
3 2 2.00 10000000.00 2
4 10 1.00 5000000.00 2
5 5 1.00 20000000.00 1
6 14 10.00 2000000.00 1
然后我通过加载所有 list_acc 表并基于名义表中的 id_user 连接这两个表,并使用查询
SELECT
level_acc, list_acc.id_acc, parent_id_acc, name_acc, volume, nominal,
COALESCE(volume * nominal,0) AS total
FROM list_acc
LEFT JOIN
(SELECT * FROM nominal
WHERE id_user = '1') AS tbl_nominal_user
ON list_acc.`id_acc` = tbl_nominal_user.id_acc
ORDER BY list_acc.id_acc
所以结果如下:
level_acc id_acc parent_id_acc name_acc volume nominal total
--------- ------ ------------- ---------------- ------ ----------- ---------------
1 1 (NULL) activities 1 (NULL) (NULL) 0.0000
2 2 1 sub activities 1 2.00 15000000.00 30000000.0000
2 3 1 sub activities 2 2.00 30000000.00 60000000.0000
2 4 1 sub activities 3 (NULL) (NULL) 0.0000
3 5 4 item sa 3 (1) 1.00 20000000.00 20000000.0000
3 6 4 item sa 3 (2) (NULL) (NULL) 0.0000
3 7 4 item sa 3 (3) (NULL) (NULL) 0.0000
2 8 1 sub activities 4 (NULL) (NULL) 0.0000
1 9 (NULL) activities 2 (NULL) (NULL) 0.0000
2 10 9 sub activities 1 (NULL) (NULL) 0.0000
2 11 9 sub activities 2 (NULL) (NULL) 0.0000
2 12 9 sub activities 3 (NULL) (NULL) 0.0000
2 13 9 sub activities 4 (NULL) (NULL) 0.0000
2 14 9 sub activities 5 10.00 2000000.00 20000000.0000
然后我想从子活动 3 中计算总数,其中总数是从总项目 sa 3 或父 ID 4 中获得的。然后,总活动 1 和 2,其中总数是从总子活动中获得的
如何在mysql上编写查询?
预期的结果是
level_acc id_acc parent_id_acc name_acc volume nominal total
--------- ------ ------------- ---------------- ------ ----------- ---------------
1 1 (NULL) activities 1 (NULL) (NULL) 110000000.0000
2 2 1 sub activities 1 2.00 15000000.00 30000000.0000
2 3 1 sub activities 2 2.00 30000000.00 60000000.0000
2 4 1 sub activities 3 (NULL) (NULL) 20000000.0000
3 5 4 item sa 3 (1) 1.00 20000000.00 20000000.0000
3 6 4 item sa 3 (2) (NULL) (NULL) 0.0000
3 7 4 item sa 3 (3) (NULL) (NULL) 0.0000
2 8 1 sub activities 4 (NULL) (NULL) 0.0000
1 9 (NULL) activities 2 (NULL) (NULL) 20000000.0000
2 10 9 sub activities 1 (NULL) (NULL) 0.0000
2 11 9 sub activities 2 (NULL) (NULL) 0.0000
2 12 9 sub activities 3 (NULL) (NULL) 0.0000
2 13 9 sub activities 4 (NULL) (NULL) 0.0000
2 14 9 sub activities 5 10.00 2000000.00 20000000.0000
解决方案
推荐阅读
- delphi - 使用 JVCL HID 设备编译现有程序后出错
- angular - 角度测试:未捕获的错误:未捕获(承诺):TypeError:无法读取未定义的属性(读取“订阅”)
- javascript - Neutralinojs:如何在将 Neutralino 窗口拖离最大化状态时最大化它
- google-sheets - Google 表格:如何根据与整个范围匹配的列返回文本值?
- delphi - Delphi中带有Firemonkey和Imagelist分辨率的ScaledLayout额外未设计的行
- r - 正则表达式仅匹配第一次出现的字符与 R
- opencv - 图像处理:知道物体的角度高达 90°,如何找到调整图像大小的因子 x 和 y
- javascript - 为什么我们需要返回一个流而不是直接分派一个值?
- r - 如何列出在函数中调用的所有用户定义函数或在 R 中使用 pryr 包的用户定义函数?
- javascript - 如何在 React Native 和 React 中处理富文本提要