如何在(子查询)中选择Where column_name

Question

我想选择查询结果中列值所在的表的所有行。从概念上讲，下面的代码看起来应该可以工作，但我不确定这是否是语法错误

SELECT * FROM generated.existing_conditions ec 
WHERE st_to IN (
SELECT st_to FROM 
(SELECT st_to AS st_to, COUNT(*) AS total_count 
FROM generated.existing_conditions ec GROUP BY st_to) AS source_1
WHERE total_count > 1
) source_2
;

这难道就是不允许的吗？我能把它重写为WITH query AS吗？

我的目标是选择任何具有重复属性值的行的唯一id，因为这可能是数据中的错误。

Answer 1

虽然不需要两个级别的子查询，但您的查询很好。您可以将其重写为：

SELECT *
FROM generated.existing_conditions ec 
WHERE st_to IN (SELECT ec2.st_to FROM 
FROM generated.existing_conditions ec2
GROUP BY ec2.st_to
HAVING COUNT(*) > 1
) ;

然而，我建议使用窗口功能：

select ec.*
from (select ec.*, count(*) over (partition by st_to) as cnt
from generated.existing_conditions ec 
) ec
where cnt > 1;

如果您喜欢CTE，这两个子查询都可以使用CTE编写。

Answer 2

与我发布的内容相比，下面通过删除source_2别名修复了错误。

SELECT * FROM generated.existing_conditions ec 
WHERE st_to IN (
SELECT st_to FROM 
(SELECT st_to AS st_to, COUNT(*) AS total_count FROM generated.existing_conditions ec GROUP BY st_to) AS source_1
WHERE total_count > 1
);