英文:
SQL - summarize results from multiple tables
问题
我有以下简单的SQL查询，需要在3个表上运行：
SELECT
A.date,
SUM(A.number)
FROM A
GROUP BY
A.date
但我还有另外两个表（B和C），我想在这两个表上运行相同的查询，然后将结果合并为一个输出表。
我期望的输出应该类似于：
| date | A.number| B.number| C.number|
|:---- | -------:| -------:| -------:|
| 2022 | 12322.1 | 9999999 | 888888 |
英文:
I have the following simple SQL query that I need to run on 3 tables:
SELECT
A.date,
SUM(A.number)
FROM A
GROUP BY
A.date
But I have two other tables (B and C) on which I'd like to run the same query. And combine the results into one table as output.
I am expecting the output to look something like:
| date | A.number | B.number | C.number |
|---|---|---|---|
| 2022 | 12322.1 | 9999999 | 888888 |
答案1
得分: 1
可以尝试以下的联合查询方法：
SELECT
date,
SUM(CASE WHEN src = 'A' THEN number ELSE 0 END) AS A_sum,
SUM(CASE WHEN src = 'B' THEN number ELSE 0 END) AS B_sum,
SUM(CASE WHEN src = 'C' THEN number ELSE 0 END) AS C_sum
FROM
(
SELECT date, number, 'A' AS src FROM A
UNION ALL
SELECT date, number, 'B' FROM B
UNION ALL
SELECT date, number, 'C' FROM C
) t
GROUP BY date
ORDER BY date;
英文:
We can try the following union approach:
<!-- language: sql -->
SELECT
date,
SUM(CASE WHEN src = 'A' THEN number ELSE 0 END) AS A_sum,
SUM(CASE WHEN src = 'B' THEN number ELSE 0 END) AS B_sum,
SUM(CASE WHEN src = 'C' THEN number ELSE 0 END) AS C_sum
FROM
(
SELECT date, number, 'A' AS src FROM A
UNION ALL
SELECT date, number, 'B' FROM B
UNION ALL
SELECT date, number, 'C' FROM C
) t
GROUP BY date
ORDER BY date;
答案2
得分: 0
这是我的方法：
创建表TableA
(
日期 日期,
编号 整数
)
GO
创建表TableB
(
日期 日期,
编号 整数
)
GO
创建表TableC
(
日期 日期,
编号 整数
)
GO
插入到TableA
值('2023-01-01', 100000),
('2023-01-02', 30000)
GO
插入到TableB
值('2023-01-01', 200000),
('2023-01-02', 10000)
GO
插入到TableC
值('2023-01-01', 400000),
('2023-01-02', 20000)
GO
选择*从
(
选择*，'A' 作为 Det 从 TableA
联合全部
选择*，'B' 从 TableB
联合全部
选择*，'C' 从 TableC
)ABC
PIVOT
(对于 Det 在 (A，B，C) 的总和(ABC.编号))
XYZ
删除表TableA
删除表TableB
删除表TableC
英文:
Here is my approach:
Create Table TableA
(
Dates Date,
Number Int
)
GO
Create Table TableB
(
Dates Date,
Number Int
)
GO
Create Table TableC
(
Dates Date,
Number Int
)
GO
Insert Into TableA
Values ('2023-01-01', 100000),
('2023-01-02',30000)
GO
Insert Into TableB
Values ('2023-01-01', 200000),
('2023-01-02',10000)
GO
Insert Into TableC
Values ('2023-01-01', 400000),
('2023-01-02',20000)
GO
SELECT * from
(
Select *,'A' Det from TableA
UNION ALL
Select *,'B' from TableB
UNION ALL
Select *,'C' from TableC
)ABC
PIVOT
(SUM(ABC.Number) FOR Det IN (A,B,C))
XYZ
DROP TABLE TableA
DROP Table TableB
DROP table TableC
答案3
得分: 0
最好的选择是首先创建日历，然后与不同的表进行左连接：
日历对于从连接中收集数据非常重要。
如果你的列名为“year”有点奇怪。在我看来，这是复杂度较低的级别。
但好吧，假设你只有年份。
创建年份表。
创建年份表：
CREATE TABLE Years
(
years int
)
接下来：
INSERT INTO Years(years )
VALUES
(2022),(2021),(2020),(2019),(2018),(2017),(2016),(2015)
例如：
SELECT
y.*,
sum(a.number) as SumA,
sum(b.number) as SumB,
sum(c.number) as SumC
FROM Years as y
left join
table_a a
on
y.years=a.date
left join
table_b b
on
y.years=b.date
left join
table_c c
on
y.years=c.date
GROUP BY
y.years
希望这有所帮助！
请告诉我是否符合你的要求。
英文:
IMO best option is to create calendar first and then left join created calendar with different tables:
Calendar is important to gather data from join.
And i'ts quite odd if you have column date as year.
In my oppinion it's to small level of complexity.
But ok. Let's say that you have only year.
Create table with years.
Create Table Years
(
years int
)
next:
INSERT INTO Years(years )
VALUES
(2022),(2021),(2020),(2019),(2018),(2017),(2016),(2015)
eg.
SELECT
y.*,
sum(a.number) as SumA,
sum(b.number) as SumB,
sum(c.number) as SumC
FROM Years as y
left join
table_a a
on
y.years=a.date
left join
table_b b
on
y.years=b.date
left join
table_c c
on
y.years=c.date
GROUP BY
y.years
Hopefully this helps!
Please let me know if it works as you wanted.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论