我偶然发现了这个问题,似乎无法理解。我有三个表:
MSG_V3_THREAD
MSG_V3_THREAD_USERS
MSG_V3_THREAD_PARTNERS
现在,我需要做的就是最终获得此表:
(注意:上面的图片是photoshop的)
我似乎找不到解决方案。我设法做了一个group_concat
可以给我正确的结果,但是它不是我需要的格式。我现在也有这个查询:
SELECT msg_v3_thread.thread_id, user_id, NULL as partner_id
FROM msg_v3_thread
LEFT JOIN msg_v3_thread_users ON msg_v3_thread_users.thread_id = msg_v3_thread.thread_id
WHERE msg_v3_thread.thread_id = 3
UNION
SELECT msg_v3_thread.thread_id, partner_id, NULL as user_id
FROM msg_v3_thread
LEFT JOIN msg_v3_thread_partners ON msg_v3_thread_partners.thread_id = msg_v3_thread.thread_id
WHERE msg_v3_thread.thread_id = 3
结果如下:
它将所有ID放在一栏中。
谁能帮我这个忙吗?解决方法可能很简单,但我看不到。
谢谢!
您有正确的想法,您只需要null
在查询的显式s周围切换即可。使用查询union
运算符时,除第一个查询外,所有查询中的别名都将被忽略:
SELECT msg_v3_thread.thread_id, user_id, NULL as partner_id
FROM msg_v3_thread
LEFT JOIN msg_v3_thread_users ON msg_v3_thread_users.thread_id = msg_v3_thread.thread_id
WHERE msg_v3_thread.thread_id = 3
UNION
SELECT msg_v3_thread.thread_id, NULL as user_id, partner_id -- Here!
FROM msg_v3_thread
LEFT JOIN msg_v3_thread_partners ON msg_v3_thread_partners.thread_id = msg_v3_thread.thread_id
WHERE msg_v3_thread.thread_id = 3
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句