PostgreSQL：not in 子句导致"too many columns"错误

Question

我使用的是postgreSQL 10，我试图创建一个相当复杂的查询。

这是

select clothes_id , true as iscon 
from archive
where 
customer_id = 101 and 
clothes_id <> 1 and 
shoes_id is null  and 
clothes_id is not null and 
clothes_id not in 
( select shoes_id, clothes_id from archive where customer_id in
( select customer_id from archive where customer_id <> 101 and clothes_id = 1 )  
)

此查询给出错误

> ERROR:  subquery has too many columns LINE 5:                
> clothes_id not in ( select shoes_id, clo...
>                                         ^ SQL state: 42601 Character: 147

我该如何解决这个问题？

如果你想知道这个查询试图做什么，为了调试，请阅读下面的

档案是一张连接顾客、鞋子和衣服的表格。

该查询尝试选择客户订购特定衣服的所有时间，然后对照另一组子查询进行检查。这些子查询检查是否有其他顾客订购了相同的衣服。如果是，那么查询最终不会返回该衣服。

所以，有一个像这样的归档表实例

clothes - customer - shoes
1          101       2
1          101       6
24         101       null
24         3         2

查询不会返回衣服24，因为顾客3也订购了这些衣服。因此，它将一无所获。

感谢

Answer 1

IN将左侧与右侧匹配，因此右侧的结果集中只需要有一列。如果你愿意，你可以做一个UNION。

clothes_id not in 
( select shoes_id from archive ...
union
select clothes_id from archive ...

或者你可以做两个INs

clothes_id not in 
( select shoes_id from archive ...
and clothes_id not in
( select clothes_id from archive ...

Answer 2

IN子句又缺少一个列比较-shoes_id

select clothes_id, true as iscon 
from archive
where 
customer_id = 101 and 
clothes_id <> 1 and 
shoes_id is null  and 
clothes_id is not null and 
(shoes_id, clothes_id) not in 
(select shoes_id, clothes_id from archive where customer_id in
(select customer_id from archive where customer_id <> 101 and clothes_id = 1)  
)

Answer 3

如果您不在子句中，则不使用列的数量，您应该在选择时减少列的数量

select clothes_id , true as iscon 
from archive
where 
customer_id = 101 and 
clothes_id <> 1 and 
shoes_id is null  and 
clothes_id is not null and 
clothes_id not in 
( select  clothes_id from archive where customer_id in
( select customer_id from archive where customer_id <> 101 and clothes_id = 1 )  
)

或者您可以尝试使用元组来比较NOT IN子句的结果

select clothes_id , true as iscon 
from archive
where 
customer_id = 101 and 
clothes_id <> 1 and 
shoes_id is null  and 
clothes_id is not null and 
(shoes_id, clothes_id) not in 
( select shoes_id, clothes_id from archive where customer_id in
( select customer_id from archive where customer_id <> 101 and clothes_id = 1 )  
)