MYSQL联盟区 [英] MYSQL UNION DISTINCT

问题描述

我有两个选择，目前我已成功运行为UNION.

I have two selects that I'm currently running as a UNION successfully.

(SELECT a.user_id,a.updatecontents as city,b.country
FROM userprofiletemp AS a
LEFT JOIN userattributes AS b ON a.user_id=b.user_id
WHERE typeofupdate='city')

UNION DISTINCT

(SELECT a.user_id,c.city,c.country
FROM userverify AS a
LEFT JOIN userlogin AS b ON a.user_id=b.user_id
LEFT JOIN userattributes AS c ON a.user_id=c.user_id
WHERE b.active=1 AND a.verifycity=0);

结果会像这样返回:

100 Melbourne Australia
200 NewYork America
300 Tokyo Japan
100 Sydney Australia

问题是查询将带来重复的user_id(在这种情况下为100). 对于我而言，第一个查询的详细信息优先，如果第二个查询中重复了user_id，则我不需要它.

The catch is the query will bring duplicate user_id (in this case the 100). The details in the first query take precedent for me and if the user_id is repeated in the second query I don't need it.

是否有办法使UNION在列上保持不同?在这种情况下，user_id? 有没有一种方法可以进行上述调用，而不会得到重复的user_id-删除第二个. 我应该以其他方式重新编写查询并且不使用UNION吗? 确实希望将其作为一个查询-如有必要，我可以使用SELECT和PHP清除重复项.

Is there a way to get a UNION to be DISTINCT on a column? in this case the user_id? Is there a way to do the above call and not get duplicate user_id's - drop the second. Should I re-write the query differently and not use a UNION. Really want it as one query - I can use to SELECT's and PHP to weed out duplicate if necessary.

thx 亚当

推荐答案

否.您无法指定需要与之区分的确切字段.它仅适用于整行.

No. You cannot specify which exact field you need to distinct with. It only works with the whole row.

从您的问题开始-只需将您的查询作为子查询，并在外部查询GROUP BY user_id

As of your problem - just make your query a subquery and in outer one GROUP BY user_id

SELECT * FROM 
(SELECT a.user_id,a.updatecontents as city,b.country
FROM userprofiletemp AS a
LEFT JOIN userattributes AS b ON a.user_id=b.user_id
WHERE typeofupdate='city')

UNION DISTINCT

(SELECT a.user_id,c.city,c.country
FROM userverify AS a
LEFT JOIN userlogin AS b ON a.user_id=b.user_id
LEFT JOIN userattributes AS c ON a.user_id=c.user_id
WHERE b.active=1 AND a.verifycity=0) x
GROUP BY user_id

这篇关于MYSQL联盟区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！