如何在SQL查询中添加带有嵌套的where子句? [英] How to add where clause with unnest in sql query?

问题描述

我有这样的查询-

select unnest(string_to_array(news_article.news_category_id, ',')):: int rowz
     from news_article) where rowz=1;

由于将rowz = 1放入查询中，因此该查询无法正常工作?如果我只希望在嵌套后rowz = 1的结果该怎么做.

this query is not working because of putting rowz=1 in the query ? How to do it if I want only that result where rowz= 1 after unnest.

当我这样做时-

选择嵌套(string_to_array(na.news_category_id，'，')):: int rowz来自news_article;

select unnest(string_to_array(na.news_category_id, ','))::int rowz from news_article;

我的桌子是-

Create table news_article(
id                          bigserial NOT NULL PRIMARY KEY,
news_headline               character varying(70) NOT NULL,
news_content_src            character varying(240) NOT NULL,
news_language_id            integer NOT NULL,
news_category_id            character varying(50) NOT NULL,
news_publisher_id           integer NOT NULL references news_publishers(pub_id),
news_date                   timestamp WITH TIME ZONE Default now()
);

然后它给我这个结果-

rowz
1
2
1
3
2

推荐答案

这回答了您的问题:

SELECT * FROM
  (SELECT unnest(string_to_array(news_article.news_category_id, ',')):: int rowz
   FROM news_article) AS categories
WHERE rowz = 1;

技巧是将数组取消嵌套到一组记录中，然后将其用作子查询.

The trick is that you unnest the array into a set of records which you then use as a sub-query.

但是，结果看起来很愚蠢.您是否想要所有 news_category_id = 1 的新闻文章的所有详细信息?在这种情况下:

The result, however, looks silly. Do you perhaps want all details of news articles which have a news_category_id = 1, possibly among other categories? In that case:

SELECT a.*
FROM news_article a
JOIN (SELECT id, unnest(string_to_array(news_article.news_category_id, ',')):: int rowz
      FROM news_article) AS c ON c.id = a.id
WHERE c.rowz = 1;

这篇关于如何在SQL查询中添加带有嵌套的where子句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！