英文:
Is it possible to sum a row from a table to a row to another table?
问题
可以使用单个SQL查询来实现这个目标吗？
英文:
I have 2 tables made like so:
| NickName | Points |
|---|---|
| Player 1 | 15 |
| Player 2 | 8 |
| NickName | Points |
|---|---|
| Player 1 | 33 |
| Player 2 | 22 |
and I need to get this table:
| NickName | Points |
|---|---|
| Player 1 | 48 |
| Player 2 | 30 |
Is it possible to do such thing using a single query in SQL?
答案1
得分: 1
将两个表连接起来，使用 UNION ALL 对 Points 列进行聚合操作。
SELECT NickName, SUM(Points) as Points
FROM (
SELECT NickName, Points FROM table1
UNION ALL
SELECT NickName, Points FROM table2
) as combined_tables
GROUP BY NickName;
你可以在以下链接中查看示例：db<>fiddle
英文:
Join the two tables and perform an aggregation on the Points column using the UNION ALL
SELECT NickName, SUM(Points) as Points
FROM (
SELECT NickName, Points FROM table1
UNION ALL
SELECT NickName, Points FROM table2
) as combined_tables
GROUP BY NickName;
db<>fiddle output
答案2
得分: 1
理想的设计是将数据放在同一张表中，同时有第三列来定义当前分离数据的内容（例如事件/比赛等）。这将允许您单独选择数据或进行聚合。
例如：
选择NickName、Points from mytable WHERE eventID = 1
或者
选择NickName、SUM(Points) from mytable GROUP BY NickName
英文:
The ideal design would be to have the data in the same table with a 3rd column defining whatever it is that currently separates your data (i.e. event/game etc). This would allow you to select the data either separately or aggregate.
E.g.
SELECT NickName, Points from mytable WHERE eventID = 1
or
SELECT NickName, SUM(Points) from mytable GROUP BY NickName
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论