sql - 根据条件将 Rowdata 转换为多行

Question

我正在尝试根据日期从单个列中提取父 ID 和子 ID

     Cust_id     ID       Date 

    75407014    603    2018-04-27 
    79807014    603    2018-04-30 
    75407016    604    2018-04-23 
    79807016    604    2018-04-30 
    75407018    605    2018-04-24 
    79807018    605    2018-04-30 
    75407020    606    2018-04-24 
    79807020    606    2018-04-30 
    75407014    608    2018-04-27 

我想排除 id = 608 因为只有一行，我的预期输出是指 parent 和 child 的 id

Select Row_number () over (partition by Cust_id, id order by date ) rn 

我的预期输出:

Parent_id  Child_id 
75407014   79807014
75407016   79807016
75407018   79807018
75407020   79807020

因此，第一个日期的 Cust_id 将是 parent_id，下一个日期将是 child_id。ID 是 customer_id 的公共(public)链接列

非常感谢您的帮助

Answer 1

基于示例数据？有一个简单的方法，一个更高级的方法。

请参阅下面的示例片段:

declare @TestTable table (Cust_id int, ID int, [Date] date);

insert into @TestTable (Cust_id, ID, [Date]) values 
(75407014, 603, '2018-04-27'), 
(79807014, 603, '2018-04-30'), 
(75407016, 604, '2018-04-23'), 
(79807016, 604, '2018-04-30'), 
(75407018, 605, '2018-04-24'), 
(79807018, 605, '2018-04-30'), 
(75407020, 606, '2018-04-24'), 
(79807020, 606, '2018-04-30'), 
(75407014, 608, '2018-04-27');

--
-- Method 1: A simple self-join on ID and Date
-- This assumes that there are maximum 2 records per ID
--
select t1.Cust_id as Parent_id, t2.Cust_id as Child_id 
from @TestTable t1
join @TestTable t2 on (t2.ID = t1.ID and t2.[Date] > t1.[Date]);

--
-- Method 2: use a CTE with a row_number and self-join the CTE
--
;WITH CTE as (
  select 
    ID, 
    row_number() over (partition by ID order by [Date], Cust_id) as rn, 
    Cust_id 
  from @TestTable t
)
select t1.Cust_id as Parent_id, t2.Cust_id as Child_id
from CTE t1
join CTE t2 on (t1.ID = t2.ID and t1.rn = 1 and t2.rn > 1);

第二种方法允许每个 parent 有超过 1 个 child 。