我只需要从三个表中获取唯一行。我该怎么办?
我有一个SQL查询:
SELECT *
FROM l_req
LEFT JOIN p_req ON (p_req.uid = l_req.uid)
LEFT JOIN r_req ON (r_req.uid = l_req.uid)
WHERE l_req.uid = 100931362
表架构:
Table l_req
id column2 column3 uid
1 2 3 100931362
2 3 4 100931362
3 4 5 100931362
Table p_req
id column2 column3 uid
1 6 5 100931362
等等
查询结果:
id column2 column3 uid id1 column21 column31 uid1
1 2 3 100931362 1 6 5 100931362
2 3 4 100931362 1 6 5 100931362
3 4 5 100931362 1 6 5 100931362
=>我有一个来自JOIN的重复行。
我需要这样的结果:
id column2 column3 uid id1 column21 column31 uid1
1 2 3 100931362 1 6 5 100931362
2 3 4 100931362 NULL NULL NULL NULL
3 4 5 100931362 NULL NULL NULL NULL
因为您的表结构看起来几乎相同,所以除了联接之外,还有其他选择。下面是使用一个可能的解决方案联盟:
设置一个手动字段名,以标识行来自哪个表,以便您可以在合并后进行区分,因为否则结果将看起来都一样。
SELECT 'l_req' AS table_name, id, column2, column3, uid FROM l_req WHERE uid = 100931362
UNION ALL
SELECT 'p_req' AS table_name, id, column2, column3, uid FROM p_req WHERE uid = 100931362
UNION ALL
SELECT 'r_req' AS table_name, id, column2, column3, uid FROM r_req WHERE uid = 100931362
我在这里还假设您的r_req
表具有与其他表相同的结构。您可以从此查询中获得如下所示的结果:
table_name id column2 column3 uid
l_req 1 2 3 100931362
l_req 2 3 4 100931362
l_req 3 4 5 100931362
p_req 1 6 5 100931362 -- this is your extra result original from join
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句