两个查询的SQL UNION，重复的列名错误 [英] SQL UNION of two queries, duplicate column name error

问题描述

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

I need a UNION of two queries, each of them works separatly, but not together, I get error: duplicate column name zipcode_id, please help.

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

推荐答案

对于每个子查询，您可能需要使用不同的别名.这应该起作用:

You may need to use different alias names for each sub-query. This should work:

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

这篇关于两个查询的SQL UNION，重复的列名错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！