有条件的自我连接表

Question

我有以下类型的表：

表dummy1：

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是event_name'A'的升序顺序的时间戳。P1，P2，P3，P4是event_name'B'的升序时间戳。

c是这些事件'a'和'b'的item_number。

我试图编写一个结果，结果应如下：

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

Answer 2

首先，按时间戳按每个事件，然后在排序的表行号上加入。

尝试以下代码。

select l.e_n as e_n_1, m.e_n as e_n_2, isnull(l.item,m.item) as item, l.t_s as t_s_a, 
    m.t_s as t_s_b from 
    (select *,(row_number() over (order by t_s)) as rn from  dummy where e_n = 'a') l 
    full join 
    (select *,(row_number() over (order by t_s)) as rn from  dummy where e_n = 'b') m 
    on l.item = m.item and l.rn=m.rn