mysql - UNION生成重复的行

我知道默认情况下UNION不会生成重复的行。但是在这种情况下，由于我使用列sort_col进行排序，因此UNION会生成重复的行（实际上不是重复的，因为sort_col在每个select中是不同的）。为了澄清，请参见此答案和comments
如何克服呢？

$key_word_1 = $what;
$key_word_2 = $what."%";
$key_word_3 = "%".$what."%";


(SELECT 1 AS sort_col,
        chair.id_user,
        chair.company,
        chair.district,
        chair.summary,
        chair.place,
        products.name
 FROM   chair
        LEFT JOIN products
               ON chair.id_user = products.id_user
 WHERE  ( chair.district LIKE ?
          AND chair.place LIKE ? )
        AND ( chair.company LIKE ?
               OR chair.summary LIKE ?
               OR products.name LIKE ?
               OR products.description LIKE ? )
 GROUP  BY chair.id_user)
UNION
(SELECT 2,
        chair.id_user,
        chair.company,
        chair.district,
        chair.summary,
        chair.place,
        products.name
 FROM   chair
        LEFT JOIN products
               ON chair.id_user = products.id_user
 WHERE  ( chair.district LIKE ?
          AND chair.place LIKE ? )
        AND ( chair.company LIKE ?
               OR chair.summary LIKE ?
               OR products.name LIKE ?
               OR products.description LIKE ? )
 GROUP  BY chair.id_user)
UNION
(SELECT 3,
        chair.id_user,
        chair.company,
        chair.district,
        chair.summary,
        chair.place,
        products.name
 FROM   chair
        LEFT JOIN products
               ON chair.id_user = products.id_user
 WHERE  ( chair.district LIKE ?
          AND chair.place LIKE ? )
        AND ( chair.company LIKE ?
               OR chair.summary LIKE ?
               OR products.name LIKE ?
               OR products.description LIKE ? )
 GROUP  BY chair.id_user)
ORDER  BY sort_col
LIMIT
?, ?

最佳答案

只需删除sort_col，因为实际上并集将按顺序加入（第二个选择结果将追加到第一个选择结果之后，第三个选择结果将在第二个选择结果之后）

关于mysql - UNION生成重复的行，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/19923923/