在DB2中为SQLJ构建SQL查询

Question

我有一个名为Lives:的表

create table Lives(
    animal varchar(10) not null,
    year int not null,
    zoo varchar(10),
    primary key(animal,year)
);

使用给定数据：

ANIMAL     YEAR        ZOO
---------- ----------- ----------
joe               2000 a
joe               2001 a
joe               2002 d
joe               2004 c
fred              2002 b
fred              2004 c
jane              2000 a
jane              2001 b
jane              2002 b
jack              2000 a
jack              2001 a
jack              2002 d
jack              2004 c
judy              2004 b
ruby              2003 d
alfred            2006 a

它由动物的名字、年份和当年的动物园组成。

我需要一个查询，找到一直在同一动物园的成对动物（a，b）在所有年份中，并且使得a在字典上小于b（即a<b）。更准确地说，这样的配对（a，b）满足以下条件：如果动物a在y年住在z动物园，b在y年也住在z动物园反之亦然

因此，我的示例数据的输出是：

Animal  Animal
------- -------
jack    joe

到目前为止，我已经构建了这个查询：

SELECT l1.animal, l2.animal
FROM Lives as l1, Lives as l2 
WHERE l2.year = l1.year and l1.animal > l2.animal

它给了我在动物园里呆了一年的动物。我现在不知道该如何继续。

我将在sqlj程序中使用这个查询。是否可以构造一个满足我期望结果的查询，或者我应该从当前的查询继续，并在sqlj中实现其余的查询？

Answer 1

我认为你想要的是一个精确的关系除法，它返回所有对，这样一对就不会有另一对没有的动物园或年份。

一种常见的方法是通过两个相关的子查询使用双重否定。这有点难理解，但它应该会给你正确的结果。

-- select all distinct pairs such that...
SELECT * FROM (
    SELECT a.animal AS animal1, b.animal AS animal2
    FROM lives a
    INNER JOIN lives b ON a.zoo = b.zoo AND a.year = b.year AND a.animal < b.animal 
) animals
WHERE NOT EXISTS (
       -- there does not exist any animal that is not...
        SELECT * FROM lives b
        WHERE b.animal = animals.animal2
        AND NOT EXISTS (
             -- in the set of animals that share year and zoo
                SELECT * FROM lives c
                WHERE c.animal = animals.animal1
                AND c.zoo = b.zoo AND c.year = c.year
                )
        )
GROUP BY animals.animal1, animals.animal2

使用简单的计数来确定相等性是行不通的，因为即使一只动物的动物园比另一只动物多，当计数相同时，你也会得到匹配。要对此进行验证，请添加此行：

ANIMAL  YEAR  ZOO
jane    2004  b

你从接受的答案中得到的结果是：

animal  animal
jane    jack
joe     jack
joe     jane

而我的解决方案给出：

animal1 animal2
jack    joe

Answer 2

试试这个：

with years as
(
select 
    animal
   ,count(distinct year) as years
from lives
group by animal
)
select 
    t1.animal as animal1
   ,t2.animal as animal2
   --,t1.year as y1
   --,t2.year as y2
   --,t1.zoo as z1
   --,t2.zoo as z2
from 
    lives t1
left outer join
    lives t2
on
    t1.year=t2.year and t1.zoo=t2.zoo and t1.animal > t2.animal
left outer join
    years
on
    years.animal=t1.animal
group by
    t1.animal 
   ,t2.animal 
having
    count(distinct t2.year)=max(years.years)

SQL FIDDLE演示