sql - 有条件的自连接表

Question

我有以下类型的表格:

表虚拟 1:

e_n  t_s  item
a     t1   c
a     t2   c
a     t3   c
a     t4   c
b     p1   c
b     p2   c
b     p3   c 
b     p4   c

t1, t2, t3, t4, p1, p2, p3, p4 是按升序排列的时间戳。t1、t2、t3、t4 是事件名称“a”的升序时间戳。p1、p2、p3、p4 是 event_name 'b' 的升序时间戳。

c 是发生这些事件“a”和“b”的项目编号。

我正在尝试编写一个查询，其结果应如下所示:

e_n1 e_n2  item  t_s_1 t_s_2
a     b     c     t1    p1
a     b     c     t2    p2 
a     b     c     t3    p3
a     b     c     t4    p4

我试过下面的代码:

select l.e_n as e_n_1, m.e_n as e_n_2, l.item, l.t_s as t_s_a, 
m.t_s as t_s_b from (
(select * from  dummy where e_n = 'a') l 
join 
(select * from  dummy where e_n = 'b') m 
on l.item = m.item and l.t_s < m.t_s

连接 l.item = m.item 是必需的，因为有许多其他项目 c1、c2、c3 具有相同的结构

结果是:

   e_n1 e_n2  item  t_s_a t_s_b
    a     b     c     t1    p1
    a     b     c     t1    p2
    a     b     c     t1    p3
    a     b     c     t1    p4
    a     b     c     t2    p1 
    a     b     c     t2    p2
    a     b     c     t2    p3

so on

我怎样才能有效地实现我的结果？

Answer 1

select      min (case when e_n = 'a' then 'a' end)  as e_n1
           ,min (case when e_n = 'b' then 'b' end)  as e_n2
           ,item
           ,min (case when e_n = 'a' then t_s end)  as t_s_1
           ,min (case when e_n = 'b' then t_s end)  as t_s_2

from       (select      d.*
                       ,row_number () over (partition by item,e_n order by t_s) as rn

            from        dummy as d
            ) d

group by    item
           ,rn

---

+------+------+------+-------+-------+
| e_n1 | e_n2 | item | t_s_1 | t_s_2 |
+------+------+------+-------+-------+
| a    | b    | c    | t1    | p1    |
| a    | b    | c    | t2    | p2    |
| a    | b    | c    | t3    | p3    |
| a    | b    | c    | t4    | p4    |
+------+------+------+-------+-------+