两个查询的SQL UNION，重复的列名错误

一尘不染

两个查询的SQL UNION，重复的列名错误

sql

我需要UNION两个查询中的一个，它们每个都是分开工作的，但不能一起工作，但我收到错误消息：重复的列名zipcode_id，请帮忙。

(SELECT * FROM
    ( (SELECT * FROM jobs AS j LEFT JOIN zipcode AS z ON z.zipcode_id=j.zipcode_id WHERE 1 AND source='student'
              ORDER BY postdate DESC LIMIT 20) ORDER BY search_order DESC ) 
s1)
    UNION ALL
(SELECT * FROM
        (  (SELECT * FROM jobs AS j LEFT JOIN zipcode AS z ON z.zipcode_id=j.zipcode_id WHERE 1 AND source='manager'
               ORDER BY postdate DESC LIMIT 30, 1000000) ORDER BY postdate DESC )
s2)

阅读 185

2021-03-08

共1个答案

一尘不染

您可能需要为每个子查询使用不同的别名。这应该工作：

    (SELECT * FROM
    ( (SELECT j1.* FROM jobs AS j1 LEFT JOIN zipcode AS z1 ON z1.zipcode_id=j1.zipcode_id WHERE 1 AND source='student'
              ORDER BY postdate DESC LIMIT 20) ORDER BY search_order DESC ) s1) UNION ALL
(SELECT * FROM
        (  (SELECT j2.* FROM jobs AS j2 LEFT JOIN zipcode AS z2 ON z2.zipcode_id=j2.zipcode_id WHERE 1 AND source='manager'
               ORDER BY postdate DESC LIMIT 30, 1000000) ORDER BY postdate DESC )
s2)

2021-03-08