我有一个表，它包含3列id，clothes、shoes、customers，并将它们关联起来。
我有一个很好的查询：
select clothes, shoes from table where customers = 101(客户101的所有衣服和鞋子(。这将返回
clothes - shoes (SET A)
1 6
1 2
33 12
24 null
另一个运行良好的查询：
select clothes ,shoes from table
where customers in
(select customers from table where clothes = 1 and customers <> 101 )(除101以外的任何其他客户的所有衣服和鞋子，带有指定的衣服(。这将返回
shoes - clothes(SET B)
6 null
null 24
1 1
2 1
12 null
null 26
14 null
现在我想从A组得到所有不在B组的衣服和鞋子。
所以(例如(select from SET A where NOT IN SET B。这应该只归还33件衣服，对吧？
我尝试将其转换为工作查询：
select clothes, shoes from table where customers = 101
and
(clothes,shoes) not in
(
select clothes,shoes from
table where customers in
(select customers from table where clothes = 1 and customers <> 101 )
) ;
我尝试了不同的语法，但上面看起来更有逻辑性。
问题是我从来没有得到衣服33，只是一套空衣服。
我该如何解决这个问题？出了什么问题？
感谢
编辑，这是表的内容
id shoes customers clothes
1 1 1 1
2 1 4 1
3 1 5 1
4 2 2 2
5 2 3 1
6 1 3 1
44 2 101 1
46 6 101 1
49 12 101 33
51 13 102
52 101 24
59 107 51
60 107 24
62 23 108 51
63 23 108 2
93 124 25
95 6 125
98 127 25
100 3 128
103 24 131
104 25 132
105 102 28
106 10 102
107 23 133
108 4 26
109 6 4
110 4 24
111 12 4
112 14 4
116 102 48
117 102 24
118 102 25
119 102 26
120 102 29
122 134 31
PostgreSQL中的except子句的工作方式与Oracle中minus运算符的工作方式相同。我想这会给你想要的。
我认为名义上你的查询看起来是对的，但我怀疑那些讨厌的null正在影响你的结果。就像null不等于5一样(它什么都不是，因此它既不等于也不等于任何东西(，null也不"在"任何东西中。。。
select clothes, shoes
from table1
where customers = 101
except
select clothes, shoes
from table1
where customers in (
select customers
from table1
where clothes = 1 and customers != 101
)
对于PostgreSQL，null是未定义的值，因此您必须在结果中消除潜在的null：
select id,clothes,shoes from t1 where customers = 101 -- or select id...
and (
clothes not in
(
select COALESCE(clothes,-1) from
t1 where customers in
(select customers from t1 where clothes = 1 and customers <> 101 )
)
OR
shoes not in
(
select COALESCE(shoes,-1) from
t1 where customers in
(select customers from t1 where clothes = 1 and customers <> 101 )
)
)
如果你想要独特的配对，你会使用：
select clothes, shoes from t1 where customers = 101
and
(clothes,shoes) not in
(
select coalesce(clothes,-1),coalesce(shoes,-1) from
t1 where customers in
(select customers from t1 where clothes = 1 and customers <> 101 )
) ;
如果同时选择衣服和鞋子栏，则无法获得"衣服33"。。。
此外，如果你需要确切地知道哪个栏目、衣服或鞋子是这个客户独有的，你可以使用这个小"黑客"：
select id,clothes,-1 AS shoes from t1 where customers = 101
and
clothes not in
(
select COALESCE(clothes,-1) from
t1 where customers in
(select customers from t1 where clothes = 1 and customers <> 101)
)
UNION
select id,-1,shoes from t1 where customers = 101
and
shoes not in
(
select COALESCE(shoes,-1) from
t1 where customers in
(select customers from t1 where clothes = 1 and customers <> 101)
)
你的结果是：
id=49, clothes=33, shoes=-1
(我假设没有任何id-1的衣服或鞋子，你可以在这里输入任何异国情调的值(
干杯