mysql - MySQL合并来自多个表的列
问题描述
我正在尝试通过以下方式选择一些数据:
field:
+----------+------------+-----------+
| id | room_id | server_id |
+----------+------------+-----------+
| 1 | 34 | 0 |
| 2 | 34 | 0 |
| 3 | 35 | 1 |
+----------+------------+-----------+
user_position:
+----------+------------+-----------+
| user_id | server_id | position |
+----------+------------+-----------+
| 11 | 0 | 2 |
| 17 | 1 | 25 |
| 19 | 0 | 28 |
+----------+------------+-----------+
room:
+----------+------------+-----------+
| id | server_id | background|
+----------+------------+-----------+
| 34 | 0 | #d91a1a |
| 35 | 1 | #f81b2a |
| 36 | 0 | #191b4a |
+----------+------------+-----------+
RESULT:
(I hope I didn't mess it up)
+----------+------------+-----------+------------+------------+
| id | server_id | background| room_id | user_id |
+----------+------------+-----------+------------+------------+
| 1 | 0 | #d91a1a | 34 | null |
| 2 | 0 | #d91a1a | 34 | 11 |
| 3 | 1 | #f81b2a | 35 | null |
| 25 | 1 | null | null | 17 |
| 28 | 0 | null | null | 19 |
+----------+------------+-----------+------------+------------+
不幸的是,我无法编写正确的查询来实现此结果。我能得到的最好的结果是 field.id、user_position.position 和 field.room_id、user_position.room_id 列是分开的。我不知道如何将它们合并在一起。
有人可以帮助我吗?
更新
好的,所以经过一番尝试,我得到了这个:
SELECT field.id, field.server_id, field.room_id, null AS user_id, room.background
FROM field
LEFT JOIN room ON room.id = field.room_id
WHERE field.server_id = 0
UNION
SELECT user_position.position, user_position.server_id, null, user_position.user_id, room.background
FROM user_position
LEFT JOIN room ON room.id = (SELECT field.room_id FROM field WHERE field.id = user_position.position)
WHERE user_position.server_id = 0
现在它正在工作我只想问是否有更好的方法来达到相同的结果。或者您认为这个查询是否足够好?
解决方案
使用给定的数据集,您可以尝试以下 -
SELECT COALESCE(F.id, UP2.position) id
,COALESCE(F.server_id, UP2.server_id) server_id
,UP2.background
,F.room_id
,UP2.user_id
FROM field F
LEFT OUTER JOIN (SELECT UP.server_id, R.background, UP.position, UP.user_id
FROM (SELECT server_id, position, user_id, ROW_NUMBER()OVER(PARTITION BY server_id ORDER BY user_id) rn
FROM user_position) UP
JOIN (SELECT server_id, background, ROW_NUMBER()OVER(PARTITION BY server_id ORDER BY id) rn
FROM room) R ON UP.rn = R.rn
AND UP.server_id = R.server_id) UP2 ON F.id = UP2.position
UNION
SELECT COALESCE(F.id, UP3.position) id
,COALESCE(F.server_id, UP3.server_id) server_id
,UP3.background
,F.room_id
,UP3.user_id
FROM field F
RIGHT OUTER JOIN (SELECT UP.server_id, R.background, UP.position, UP.user_id
FROM (SELECT server_id, position, user_id, ROW_NUMBER()OVER(PARTITION BY server_id ORDER BY user_id) rn
FROM user_position) UP
JOIN (SELECT server_id, background, ROW_NUMBER()OVER(PARTITION BY server_id ORDER BY id) rn
FROM room) R ON UP.rn = R.rn
AND UP.server_id = R.server_id) UP3 ON F.id = UP3.position
ORDER BY id
这是演示。
推荐阅读
- javascript - 如果在javascript中打开ios低功耗模式会触发什么javascript事件?
- c++ - 将指向成员的类型转换为简单的指针类型
- mysql - 用最大和最小标记更新每一行
- javascript - 如何停止监听 Firebase Firestore 中的数据库更改?
- google-people-api - 是否可以通过 Apps 脚本删除 Google 其他联系人?
- disassembly - IDA PRO 更改 .rdata 值
- docker - 入口 Nginx 规则并不总是得到遵守
- apache-spark - 如何将ojai配置从驱动程序传递给spark中的执行程序?
- reactjs - useMemo 钩子不适用于地图元素
- mysql - MYSQL 在一段时间不活动后失去连接