首页 > 解决方案 > 如何从此表中计算总计和小计

问题描述

我有 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

标签: mysqlsql

解决方案


推荐阅读