使用否定句连接

Question

下面是Postgres数据库中的表格：

表示例

---------------------------
   name     |    number
---------------------------
DefaultName |       1
DefaultName |       2
DefaultName |       3
DefaultName |       4
Charlie     |       1
Charlie     |       3
Charlie     |       4
Charlie     |       5
Amanda      |       2
Amanda      |       3
Amanda      |       4
Amanda      |       5

我需要得到'DefaultName‘中存在的“number”，但是与'DefaultName’不同的“名称”中没有出现的数字。在这种情况下，它将返回：

---------------------------
   names    |    numbers
---------------------------
Charlie     |       2
Amanda      |       1

我正在尝试像下面这样的左联接，但我想不出一种方法让DefaultName数与其他名字的否定交叉。

SELECT Test_Configs.name, Default_Configs.number

FROM Example AS Test_Configs  
    LEFT JOIN Example AS Default_Configs      
        ON Default_Configs.name = 'DefaultName'

Answer 1

我将为基表生成每个名称和LEFT JOIN的整个范围，以消除当前的范围：

SELECT n.name, nr.number
FROM  (
   SELECT DISTINCT name
   FROM   example
   WHERE  name <> 'DefaultName'
   ) n                         -- all names except 'DefaultName'
CROSS  JOIN (
   SELECT number               -- assuming distinct numbers for 'DefaultName'
   FROM   example
   WHERE  name = 'DefaultName'
   ) nr                        -- combine with numbers from 'DefaultName'
LEFT   JOIN example x USING (name, number)
WHERE  x.number IS NULL;       -- minus existing ones

仅列出每个名称的空白：

SELECT n.name, nr.number
FROM  (
   SELECT name, min(number) AS min_nr, max(number) AS max_nr
   FROM   example
   GROUP  BY 1
   ) n
 , generate_series(n.min_nr, n.max_nr) AS nr(number)
LEFT   JOIN example x USING (name, number)
WHERE  x.number IS NULL;

SQL Fiddle

以下是排除另一个表(本例中的派生表)中存在的行的基本技术：

• 选择其他表中不存在的行。

Answer 2

它将进行几次传递，选择default，非默认组名，然后左转join并检查null值。检查SQLFiddle示例。

select Names.name, DefaultConfigs.number
from Example DefaultConfigs
  cross join (
    select name
    from Example
    where name != 'DefaultName'
    group by name
    ) Names
  left join Example Missing on Missing.name = Names.name
    and Missing.number = DefaultConfigs.number
where DefaultConfigs.name = 'DefaultName'
  and Missing.name is null
;

Answer 3

生成名称和默认值的所有组合。那就把那些礼物移走。

select othernames.name, defaultnumbers.number
from (select number from example where name = 'DefaultName') defaultnumbers
cross join (select distinct name from example where name <> 'DefaultName') othernames
except
select name, number from example;