当前位置：文江博客话题详情

sql查询以组和内连接两个表格总和

发布于 2025-01-23 03:52:38 字数 803 浏览 0 评论 0原文

我有两个这样的表：

两张表中的每一行都是由列周周和城市唯一识别的。我想创建一个具有5列（周，Value_a，value_b，value1，value2）和3行（每周1行）的表格，值列在每个城市汇总）。最后一个表格应该完全像这样：

sum_a是所有城市中每周的值a的总和，sum_b是所有城市的value_b之和。

这是我的SQL查询：选择 * 从table1 内联接table2 在table1.week = table2.week和 table1.city = table2.City

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

真心难拥有 2025-01-30 03:52:38

如果您需要依靠加入列为列表，则只需要在避免重复数据之前总结一下表格
认为如果您在表1中有一个星期，而在表2中不在表2中，则数据将不会在您的示例中是shawn

SELECT
    A1.week,
    A1.city,
    A1.value1,
    A1.value2,
    A2.value1,
    A2.value2
FROM (
    SELECT
        Week,
        city,
        sum(value1),
        sum(value2)
    FROM table1
    GROUP BY Week, city
) A1
INNER JOIN (
    SELECT
        Week,
        city,
        sum(valueA),
        sum(valueB)
    FROM table2
    GROUP BY Week, city
) A2
    ON a1.week = a2.week AND a1.city = a2.city

If you need to sum column relied by join you just need to sum your tables before to avoid repeat data
Considere that if you have a week in your table 1 and not in the table 2 the data will not be shawn in your example

SELECT
    A1.week,
    A1.city,
    A1.value1,
    A1.value2,
    A2.value1,
    A2.value2
FROM (
    SELECT
        Week,
        city,
        sum(value1),
        sum(value2)
    FROM table1
    GROUP BY Week, city
) A1
INNER JOIN (
    SELECT
        Week,
        city,
        sum(valueA),
        sum(valueB)
    FROM table2
    GROUP BY Week, city
) A2
    ON a1.week = a2.week AND a1.city = a2.city

回复收藏 0 原文

眼眸 2025-01-30 03:52:38

以下查询可以按预期为您提供输出：

SELECT table1.week, sum(value_a) as sum_a, sum(value_b) as sum_b, sum(value1) as sum_1, sum(value2) as sum_2
FROM table1
  INNER JOIN table2 ON table1.week = table2.week AND table1.city = table2.city
group by table1.week

可以通过检查链接

/b4vvn.png“ alt =”在此处输入图像描述>

the below query can give you output as expected:

SELECT table1.week, sum(value_a) as sum_a, sum(value_b) as sum_b, sum(value1) as sum_1, sum(value2) as sum_2
FROM table1
  INNER JOIN table2 ON table1.week = table2.week AND table1.city = table2.city
group by table1.week

Query can be validated by checking the link db<>fiddle<>example