我在创建良好查询时遇到了一些麻烦,需要您的专家帮助:)
我有 2 张包含数据的表格
Players_ingame:
## playerid ## ## week ## ## score ##
13 1 5
13 2 0
13 3 7
07 1 0
07 2 8
.. . ..
Players_bench:
## playerid ## ## week ## ## score ##
07 3 2
.. . ..
我想要查询的结果,例如:
## playerid ## ## week1 ## ## week2 ## ## week3 ## ## wee... ##
13 5 0 7 .
07 0 8 2 .
..
我该怎么做?甚至可能吗? 我有一个SQLite数据库,但如果它会有所作为,可以切换到MySQL
请帮忙..非常感谢
对 2 个表使用 UNION ALL 返回所有行,然后通过条件聚合获取结果:
select t.playerid,
max(case t.week when 1 then t.score end) week1,
max(case t.week when 2 then t.score end) week2,
max(case t.week when 3 then t.score end) week3
from (
select * from Players_ingame
union all
select * from Players_bench
) t
group by t.playerid
请参阅演示。
结果:
| playerid | week1 | week2 | week3 |
| -------- | ----- | ----- | ----- |
| 7 | 0 | 8 | 2 |
| 13 | 5 | 0 | 7 |