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

发布于 2024-12-08 11:38:50 字数 559 浏览 1 评论 0原文

我需要两个查询的 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)

原文

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)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

小女人ら 2024-12-15 11:38:50

如果您实际上使用的是 SELECT *，则 zipcode_id 列同时位于 Jobs 表和 Zipcode 表中。正如错误消息所示，您不能让两列使用与您拥有的名称相同的名称。因为您正在使用子查询，所以如果您引用重复的列名，SQL 引擎将无法理解您的含义。例如，以下 SQL 应该返回什么？

SELECT num FROM (SELECT 1 AS num, 2 AS num) AS SQ

无论如何，使用 SELECT * 是一种非常糟糕的做法。

If you're actually using SELECT * then the zipcode_id column is in both the Jobs table and the Zipcode table. As the error message says, you can't have the two columns using the same name as you have it. Because you are using subqueries there would be no way for the SQL engine to understand what you meant if you referred to the duplicated column name. For example, what should the following SQL return?

SELECT num FROM (SELECT 1 AS num, 2 AS num) AS SQ

Using SELECT * is a pretty bad practice in any event.

回复收藏 0 原文

眼中杀气 2024-12-15 11:38:50

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

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

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)

回复收藏 0 原文

~没有更多了~