Mysql Union 返回重复项

Question

我有这个查询

(
SELECT u.Id, 1 AS which
FROM users u
JOIN user_opt uo ON u.id = uo.UserId
WHERE uo.country
IN (
'Spain',  'Azerbaijan'
)
AND uo.Hobbies REGEXP  'Astronomy'
LIMIT 100
)
UNION (

SELECT u.Id, 2 AS which
FROM users u
JOIN user_opt uo ON u.id = uo.UserId
WHERE uo.country
IN (
'Spain',  'Azerbaijan'
)
LIMIT 100
)
ORDER BY which 

因为我在这个查询中使用了UNION，所以我不应该得到重复的行。但是这会返回重复的行。

Answer 1

通过执行以下操作，您应该能够非常接近您想要的:

(SELECT u.Id, 1 AS which
 FROM users u JOIN
      user_opt uo
      ON u.id = uo.UserId
 WHERE uo.country IN ('Spain',  'Azerbaijan') AND
       uo.Hobbies REGEXP 'Astronomy'
 LIMIT 100
)
UNION ALL
(SELECT u.Id, 1 AS which
 FROM users u JOIN
      user_opt uo
      ON u.id = uo.UserId
 WHERE uo.country IN ('Spain',  'Azerbaijan') AND
       uo.Hobbies NOT REGEXP 'Astronomy'
 LIMIT 100
)

这不能保证将第一组放在第一位。但在实践中，确实如此。