mysql - Mysql Union返回重复项

我有这个问题

(
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，所以我不应该得到重复的行，但这会返回重复的行。

最佳答案

你应该可以通过这样做接近你想要的：

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

这不能保证把第一组放在第一位。但实际上，确实如此。