sql - Oracle 联接(左外、右等。:S )

Question

我知道除了知道什么是“最喜欢的编程卡通”之外，stackoverflow 会对我有所帮助:P

这是接受的答案:
Bill Karwin

感谢大家的帮助(我想给你们加倍投票)

我的查询结果是这样的(这是真实的)

SELECT 
    accepted.folio,
    COALESCE( inprog.activityin, accepted.activityin ) as activityin,
    inprog.participantin,
    accepted.completiondate 
FROM performance accepted
    LEFT OUTER JOIN performance inprog 
        ON( accepted.folio = inprog.folio 
            AND inprog.ACTIVITYIN 
            IN ( 4, 435 )                    -- both are ids for inprogress
            AND inprog.PARTICIPANTIN != 1  ) -- Ignore the "bot" participant
    LEFT OUTER JOIN performance closed
        ON( accepted.folio = closed.folio 
            AND closed.ACTIVITYIN IN ( 10,436, 4, 430  ) )  -- all these are closed or cancelled
WHERE accepted.ACTIVITYIN IN ( 3, 429 )      --- both are id for new 
AND accepted.folio IS NOT NULL
AND closed.folio IS NULL;

现在我只需要加入其他表格即可获得人类可读的报告。

原帖

你好。

我挣扎了大约 6 个小时。现在使用数据库查询(我的长期克星)

我有一个包含一些字段的数据表，例如:

table performance( 
     identifier varchar, 
     activity    number, 
     participant number, 
     closedate   date, 
)

它用于跟踪票证的历史记录

标识符 : 是一个像 (NAF0000001) 这样的客户 ID

事件 : 是票所在位置的 fk(新的、正在进行的、拒绝的、已关闭的等)

参与者 : 是那个时候参加票的人的fk

已关闭 : 是该事件完成的日期。

编辑:我应该说“完成日期”而不是关闭日期。这是事件完成的日期，而不是票证关闭时的日期。

例如，典型的历史可能是这样的:

identifier|activity|participant|closedate-------------------------------------------NA00000001|       1|          1|2008/10/08 15:00|-------------------------------------------NA00000001|       2|          2|2008/10/08 15:20|-------------------------------------------NA00000001|       3|          2|2008/10/08 15:40|-------------------------------------------NA00000001|       4|         4|2008/10/08 17:05|-------------------------------------------

And participant 1=jonh, 2=scott, 3=mike, 4=rob

and activties 1=new, 2=inprogress, 3=waitingforapproval, 4=closed

etc. And tens of other irrelevant info.

Well my problem is the following.

I have managed to create a query where I can know when a ticket was opened and closed

it is like this:

 select 
     a.identifier,
     a.participant,
     a.closedate as start,
     b.closedate as finish      
from 
    performance a,
    performance b
where
    a.activity = 1 -- new
    and b.activity = 4 -- closed
    and a.identifier = b.identifier

但是不知道是什么票 不是 关闭以及谁在参加他们。

到目前为止，我有这样的事情:

 select 
     a.identifier,
     a.participant,
     a.closedate as start
from 
    performance a        
where
    a.activity = 1 -- new
    and a.identifier not in ( select identifier from performance where activity = 4 ) --closed

那就是给我所有有开始(新= 1)但没有关闭(关闭= 4)的人

但这里的大问题是它打印了打开票的参与者，但我需要参加它的参与者。所以我将“进行中”事件添加到查询中。

 select 
     a.identifier,
     a.participant,
     a.closedate as start
from 
    performance a,
    performance b        
where
    a.activity = 1 -- new        
    and a.identifier not in ( select identifier from performance where activity = 4 ) --closed
    and b.identifier = a.identifier
    and b.activity = 2  -- inprogress..

但并非"new"中的所有行都是“进行中”，并且通过该查询我删除了所有行。

我需要的是显示所有“进行中”参与者，如果票不是“进行中”，它将显示为空。

有点像

    identifier|activity|participant|closedate-------------------------------------------NA00000002|       1|           |2008/10/08 15:00|-------------------------------------------NA00000003|       1|           |2008/10/08 15:20|-------------------------------------------NA00000004|       1|           |2008/10/08 15:40|-------------------------------------------NA00000005|       2|          4|2008/10/08 15:40|-------------------------------------------NA00000006|       2|          4|2008/10/08 15:40|

In this case

NA002, NA003 and NA004 are in "new", so no participant is shown

While

NA005 and NA006 are being "inprgress (act = 2 )" and they are being attended by rob ( participant 4 )

So I remember there was this thing called left outer join or something like that but I never ever understand it. What I would like to know is how can I fetch the identifiers that are "inprogress" and "new" and that are not closed.

Probably taking a little rest would help me to clear my mind. If anyone knows how to do it I'll appreciate it.

By the way I've tried:

 select 
     a.identifier,
     a.participant,
     a.closedate as start
from 
    performance a
    left outer join
    performance b  
    on      
    b.identifier = a.identifier
where
    a.activity = 1 -- new        
    and a.identifier not in ( select identifier from performance where activity = 4 ) --closed
    and b.activity = 2  -- inprogress..

但是给了我与之前相同的结果(删除"new"记录中唯一的)

Answer 1

通常，编写它们的更好方法是使用 EXISTS。第一个是:

select * from performance p1
where not exists 
    ( select * from performance p2 
      where p2.identifier = p1.identifier and p2.activity = 4 )

通过这种方式，您可以对 performance.identifier 进行键控查找，而不必在 (select identifier from performance where activity=4) 中构建大量标识符列表。 .