MYSQL 联合 DISTINCT

MYSQL UNION DISTINCT

提问by Adam

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

我有两个选择，我目前作为 UNION 成功运行。

(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);

The results come back like this:

结果如下：

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

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.

问题是查询将带来重复的 user_id（在本例中为 100）。第一个查询中的详细信息优先于我，如果 user_id 在第二个查询中重复，我不需要它。

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.

有没有办法让 UNION 在列上是 DISTINCT？在这种情况下，user_id？有没有办法做上面的调用而不是重复的 user_id 的 - 删除第二个。我应该以不同的方式重新编写查询而不使用 UNION。真的希望将其作为一个查询 - 如果需要，我可以使用 SELECT 和 PHP 来清除重复项。

thx Adam

谢谢亚当

回答by zerkms

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

不可以。您不能指定需要区分的确切字段。它仅适用于整行。

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

至于您的问题 - 只需将您的查询设为子查询并在外部查询 GROUP BYuser_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

回答by ypercube??

(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 ALL

(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
  AND a.user_id NOT IN
      ( SELECT user_id
        FROM userprofiletemp 
        WHERE typeofupdate='city'
      ) 
);

回答by bloodyKnuckles

MySQL UNIONwill by default produce distinct rows—however, the entire row needs to be distinct. If you wish to limit the distinction to a single or a few columns, due to other columns not being distinct, then one method to do this is to wrap the UNIONin a sub-query and GROUP BYthe sub-query by the columns you wish to be unique.

MySQLUNION默认会生成不同的行——但是，整行需要是不同的。如果您希望将区别限制为单个或几个列，因为其他列不明显，那么一种方法是将UNION子查询和GROUP BY子查询包装在您希望成为的列中独特的。

Here I wrap the entire UNIONin a sub-query, give it an alias, then GROUP BYthe desired unique column:

在这里，我将整个包裹UNION在一个子查询中，给它一个别名，然后GROUP BY是所需的唯一列：

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

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

) aa GROUP BY user_id;

If you have more than one column you'd like to include in the distinction, list them after the GROUP BY: such as GROUP BY user_id, city.

如果您想在区分中包含不止一列，请将它们列在GROUP BY:之后，例如GROUP BY user_id, city。

---

---

SIDE NOTE: since, in this case, UNIONdoes not provide the desired distinction, there is no benefit to simply using UNION, and apparently "UNION ALLis much faster than UNION", therefore you can use UNION ALLto speed up this query.

旁注：因为在这种情况下，UNION没有提供所需的区别，所以简单地使用没有任何好处UNION，并且显然“UNION ALL比UNION”快得多，因此您可以使用UNION ALL来加速这个查询。