多个表格上的分组求和

huangapple
go评论115阅读模式
英文:

multiple sums on grouped tables

问题

  1. 表头
  2. +---------+-------+
  3. | ref     | total |
  4. +---------+-------+
  5. | 2544    |  2000 |
  6. | 2544    |    10 |
  7. | 2545    |  2566 |
  8. | 2545    |    34 |
  9. +---------+-------+
  11. 表项
  12. +---------+-------+
  13. | ref     | total |
  14. +---------+-------+
  15. | 2544    |  1500 |
  16. | 2544    |   500 |
  17. | 2545    |  2000 |
  18. | 2545    |   500 |
  19. | 2545    |   100 |
  20. +---------+-------+
  22. 我想要对两个表中的总计列按ref分组求和，所以我的输出应该类似于：
  24. 输出:
  25. +---------+--------------+-------------+
  26. | ref     | total_header | total_items | 
  27. +---------+--------------+-------------+
  28. | 2544    |        2010  |       2000  |
  29. | 2545    |        2600  |       2600  |
  30. +---------+--------------+-------------+
  32. 然后我可以比较两个总计列并检查差异。
  34. 查询:
  36. 选择
  37.     表头.ref,
  38.     求和(表头.total) 作为 total_header
  39. 从
  40.     表头
  41. 分组按
  42.     表头.ref
  43. 按
  44.     表头.ref 降序排序
  46. 这给我提供了表头表的正确信息。但是，一旦我将表项表添加到查询中，total_header 和 total_items 就会给出错误的值。
  48. 以下是我使用的查询....
  50. 选择
  51.     表头.ref,
  52.     求和(表头.total) 作为 total_header,
  53.     求和(表项.total) 作为 total_items
  54. 从
  55.     表头 左连接
  56.     表项 On 表头.ref = 表项.ref
  57. 分组按
  58.     表头.ref
  59. 按
  60.     表头.ref 降序排序
  62. 有人能帮助我创建一个可以生成上述输出的查询吗？此外，如果可能的话，还可以使用另一列来比较这两个总计列，根据它们是否匹配来给出 true 或 false。
  64. 谢谢，
  66. Martin。
英文: 
  1. Table Header
  2. +---------+-------+
  3. | ref     | total | 
  4. +---------+-------+
  5. | 2544    |  2000 |
  6. | 2544    |    10 |
  7. | 2545    |  2566 |
  8. | 2545    |    34 |
  9. +---------+-------+
  11. Table Items
  12. +---------+-------+
  13. | ref     | total | 
  14. +---------+-------+
  15. | 2544    |  1500 |
  16. | 2544    |   500 |
  17. | 2545    |  2000 |
  18. | 2545    |   500 |
  19. | 2545    |   100 |
  20. +---------+-------+

I would like to sum the total column in both tables grouped by ref, so my output would look something like:

  1. Output:
  2. +---------+--------------+-------------+
  3. | ref     | total_header | total_items | 
  4. +---------+--------------+-------------+
  5. | 2544    |        2010  |       2000  |
  6. | 2545    |        2600  |       2600  |
  7. +---------+--------------+-------------+

I can then compare the two total columns and check for differences.

The query:

  1. Select
  2.     Header.ref,
  3.     Sum(Header.total) as total_header
  4. From
  5.     Header
  6. Group By
  7.     Header.ref
  8. Order By
  9.     Header.ref Desc

This gives me the correct info for the Header table. As soon as I add the Items table to the query then the total_header and total_items give the wrong values

Here is the query that I am using....

  1. Select
  2.     Header.ref,
  3.     Sum(Header.total) as total_header,
  4.     Sum(Items.total) as total_items
  5. From
  6.     Header Left Join
  7.     Items On Header.ref = Items.ref
  8. Group By
  9.     Header.ref
  10. Order By
  11.     Header.ref Desc

Can anyone help me create a query that would produce the output above? Also, if possible with another column that compares the 2 total columns giving true or false depending on if they match.

Thank you,

Martin.

答案1

得分: 1

以下是翻译好的部分：

  1. 你可以首先获取总和，然后将它们连接 - 
  3. SELECT H.ref, H_TOT, I_TOT
  4. FROM (SELECT ref, SUM(total) H_TOT
  5.       FROM Header
  6.       GROUP BY ref) H
  7. JOIN (SELECT ref, SUM(total) I_TOT
  8.       FROM Items
  9.       GROUP BY ref) I ON H.ref = I.ref
  10. ORDER BY H.ref
英文:

You may first get the sum and then join them -

  1. SELECT H.ref, H_TOT, I_TOT
  2. FROM (SELECT ref, SUM(total) H_TOT
  3.       FROM Header
  4.       GROUP BY ref) H
  5. JOIN (SELECT ref, SUM(total) I_TOT
  6.       FROM Items
  7.       GROUP BY ref) I ON H.ref = I.ref
  8. ORDER BY H.ref

答案2

得分: 0

使用UNION ALL连接两个表，然后进行聚合：

  1. select t.ref,
  2.   sum(t.total_header) total_header,
  3.   sum(t.total_items) total_items,
  4.   sum(t.total_header) = sum(t.total_items) matching
  5. from (  
  6.   select ref, total total_header, 0 total_items from Header
  7.   union all
  8.   select ref, 0 total_header, total total_items from Items
  9. ) t  
  10. group by t.ref

查看演示。<br/>
结果：

  1. | ref  | total_header | total_items | matching |
  2. | ---- | ------------ | ----------- | -------- |
  3. | 2544 | 2010         | 2000        | 0        |
  4. | 2545 | 2600         | 2600        | 1        |
英文:

With UNION ALL of the 2 tables and then aggregate:

  1. select t.ref,
  2.   sum(t.total_header) total_header,
  3.   sum(t.total_items) total_items,
  4.   sum(t.total_header) = sum(t.total_items) matching
  5. from (  
  6.   select ref, total total_header, 0 total_items from Header
  7.   union all
  8.   select ref, 0 total_header, total total_items from Items
  9. ) t  
  10. group by t.ref

See the demo.<br/>
Results:

  1. | ref  | total_header | total_items | matching |
  2. | ---- | ------------ | ----------- | -------- |
  3. | 2544 | 2010         | 2000        | 0        |
  4. | 2545 | 2600         | 2600        | 1        |

答案3

得分: 0

可能的解决方案

  1. SELECT refs.ref, sum_header.header_total, sum_items.items_total
  2. FROM ( SELECT ref FROM header
  3.        UNION
  4.        SELECT ref FROM items ) refs
  5. LEFT JOIN ( SELECT ref, SUM(total) header_total
  6.             FROM header
  7.             GROUP BY ref ) sum_header USING (ref)
  8. LEFT JOIN ( SELECT ref, SUM(total) items_total
  9.             FROM items
  10.             GROUP BY ref ) sum_items USING (ref)

这个解决方案的最佳情况是：

  1. SELECT COUNT(*)/COUNT(DISTINCT ref) 对于两个表都很高，约为（10或更多）。
  2. 在两个表中都对 ref 进行了索引（此外，尝试在第一个子查询中为两个表执行 SELECT DISTINCT ref FROM）。
英文:

Possible solution

  1. SELECT refs.ref, sum_header.header_total, sum_items.items_total
  2. FROM ( SELECT ref FROM header
  3.        UNION
  4.        SELECT ref FROM items ) refs
  5. LEFT JOIN ( SELECT ref, SUM(total) header_total
  6.             FROM header
  7.             GROUP BY ref ) sum_header USING (ref)
  8. LEFT JOIN ( SELECT ref, SUM(total) items_total
  9.             FROM items
  10.             GROUP BY ref ) sum_items USING (ref)

The best situation for this solution is:

  1. SELECT COUNT(*)/COUNT(DISTINCT ref) is high, ~ (10 or more), for both tables.
  2. ref is indexed in both tables (additionally try SELECT DISTINCT ref FROM in first subquery for both tables).

答案4

得分: -1

你可以简单地使用以下代码：

  1. 选择引用，总计(header.total) 作为 total_header，总计(items.total) 作为 total_items
  2. 从 header, items
  3. 其中 header.ref = items.ref
  4. 按引用降序排序
英文:

You can simply use

  1. Select ref, sum(header.total) as total_header, sum(items.total) as total_items
  2. from header,items where header.ref = items.ref
  3. group by ref
  4. order by ref desc

huangapple
  • 本文由 发表于 2020年1月7日 01:38:32
  • 转载请务必保留本文链接：https://go.coder-hub.com/59616569.html
  • group-by
  • mysql
  • sql
  • sum
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定