甲骨文连接(左外、右等:S)

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

这是接受的答案：比尔·卡文

感谢大家的帮助(我想给大家双倍投票(

我的查询是这样的(这是真实的查询(

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(新票，in_progress票，拒绝票证，关闭票

证等(

参与者：是当时谁在参加门票的FK

关闭日期：是该活动完成的日期。

编辑：我应该说"完成日期"而不是关闭日期。这是活动完成的日期，不是工单关闭时的日期。

例如，典型的历史记录可能是这样的：

标识符|活动|参与者|关闭日期-------------------------------------------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|-------------------------------------------

参与者1=乔恩，2=斯科特，3=迈克，4=罗布

和活动 1 = 新建，2 = 进行中，3 = 等待批准，4 = 已关闭

等。以及数十个其他不相关的信息。

好吧，我的问题如下。

我已经设法创建了一个查询，我可以知道工单何时打开和关闭

它是这样的：

 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..

但并非所有"新"中的行都是"正在进行"的，通过该查询，我删除了所有这些行。

我需要的是显示所有"正在进行"的参与者，如果票证不是"正在进行"，它将显示为空。

像

    标识符|活动|参与者|关闭日期-------------------------------------------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|

在这种情况下

NA002

、NA003 和 NA004 处于"新"状态，因此不显示任何参与者

而

NA005

和 NA006 正在"侵入(行为 = 2("，他们正在由 rob 参加(参与者 4 (

所以我记得有一种叫做左外连接或类似的东西，但我从来没有理解过它。我想知道的是，如何获取"正在进行"和"新"且未关闭的标识符。

也许休息一下会帮助我理清思路。如果有人知道如何做到这一点，我将不胜感激。

顺便说一下，我试过：

 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..

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

尝试这样的事情(我还没有测试过(：

SELECT p_new.identifier, COALESCE(p_inprog.activity, p_new.activity) AS activity,
  p_inprog.participant, COALESCE(p_inprog.closedate, p_new.closedate) AS closedate
FROM performance p_new
  LEFT OUTER JOIN performance p_inprog 
    ON (p_new.identifier = p_inprog.identifier AND p_inprog.activity = 2)
  LEFT OUTER JOIN performance p_closed 
    ON (p_new.identifier = p_closed.identifier AND p_closed.activity = 4)
WHERE p_new.activity = 1
  AND p_closed.identifier IS NULL;

我认为人们认为外连接比实际更难。例如：

A LEFT OUTER JOIN B ON (...condition...)

这将返回 A 中的所有行，无论 B 中是否有任何匹配的行。如果 B 中没有行匹配，则将该行 A 的结果集中的所有列 B.* 视为 NULL。连接条件可以是 B 中的行必须满足的表达式，否则它不包含在连接中。因此，A 中的更多行将是单独的。

通常，编写这些内容的更好方法是使用 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) 中构建大量标识符列表。

我认为这应该可以做到。

第一部分获取所有新记录、未关闭记录和未进行中的记录。第二部分获取所有正在进行的记录。然后我们将它们连接在一起，我们还可以通过在这个查询周围包装一个"SELECT * FROM"来按标识符排序。

select 
  a.identifier,
  a.participant,
  a.closedate as start
from 
  performance a
where
  a.activity = 1
  and not exists ( select identifier 
                   from performance b 
                   where b.activity = 4 
                   and b.identifier = a.identifier) 
  and not exists ( select identifier 
                   from performance c 
                   where c.activity = 2 
                   and c.identifier = a.identifier) 
UNION ALL
select 
  a.identifier,
  a.participant,
  a.closedate as start
from 
  performance a
where
  a.activity = 2
  and not exists ( select identifier 
                   from performance b 
                   where b.activity = 4 
                   and b.identifier = a.identifier);

我建议你想要的是最早的记录(大概，但不一定是活动=1的记录(和最近的记录(无论活动数量如何(。如果最近记录的活动为 4，则票证关闭。否则，参与者是门票的当前持有人。如果可以重新打开票证，则仅匹配活动 = 4 会引入一个潜在的错误。

实际上，根据您的示例，您甚至可能不需要最早的记录。以下情况如何：

SELECT
        identifier,
        activity,
        participant,
        closedate
    FROM
        performance a
    WHERE
        (a.identifier, a.closedate) in
            (select b.identifier, max(b.closedate)
                from performance b
                group by b.identifier
            )
;

这个怎么样：

SELECT * FROM (
  SELECT identifier,
         MAX(activity) activity,
         MAX(participant) KEEP (DENSE_RANK LAST ORDER BY activity)
    FROM performance
    GROUP BY identifier
)
WHERE activity in (1,2)

内部查询提供每个票证及其相应参与者的最新活动。外部查询将其筛选为活动为"新"或"正在进行"的活动。

我喜欢DENSE_RANK功能。

首先，如果您可以让客户同时打开多个票证，则可能会遇到设计问题。理想情况下，您应该有一个ticket_id，然后您可以使用ticket_id而不是标识符来执行 Andy 的查询。

哪些门票没有关闭：

select identifier as closed_identifier 
  from performance where identifier not exists
  (select identifier from performance where activity=4)

正在参加的门票：

select identifier as inprogress_identifier, participant performance 
  from performance where activity=2

未关闭的门票，参与者正在参加：

select * from 
  (select identifier as notclosed_identifier 
    from performance where identifier not exists
    (select identifier from performance where activity=4)) closed 
left join 
  (select identifier as inprogress_identifier, participant performance 
    from performance where activity=2) attended 
on notclosed_identifier=inprogress_identifier

也许您可以使用这种查询作为起点。

select x.identifier, 
       max(x.p_1) as new_participant, max(x.c_1) as new_date,
       max(x.p_2) as inprogress_participant, max(x.c_2) as inprogress_date,
       max(x.p_3) as approval_participant, max(x.c_3) as approval_date,
       max(x.p_4) as closing_participant, max(x.c_4) as closing_date
  from (
        select a.identifier, 
               decode (activity, 1, participant, null) as p_1,  decode (activity, 1, closedate, null) as c_1,
               decode (activity, 2, participant, null) as p_2,  decode (activity, 2, closedate, null) as c_2,
               decode (activity, 3, participant, null) as p_3,  decode (activity, 3, closedate, null) as c_3,
               decode (activity, 4, participant, null) as p_4,  decode (activity, 4, closedate, null) as c_4
          from performance a
        ) x
 group by x.identifier

这个想法是将表从行序列化为字段，并基于它创建一个视图。您可以基于此视图创建报表。

问候

只是一个其他人可能会建立的快速想法(未经测试，但我希望这个想法能实现(：

首先，选择所有尚未关闭的活动(由其他人发布(：

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

然后，您可以通过在 select 子句中添加子查询来添加参加活动的人员：

select id,
 (select participant 
  from performance p3 
  where p3.activity=3 and p1.id=p2.id)
from performance p1 where identifier not exists
  (select * from performance p2 where activity=4 and p1.id=p2.id)

如果此 id 没有活动 3 记录，则子查询返回 null，这正是我们所需要的。

希望这有帮助 - 如有必要，请扩展。

相关内容

最新更新

热门标签：