我有一张桌子的客户喜欢：

ID       Type       Date          Address         SSN
RT124    MASTER     12/15/2005    7 Hill st       12345 
RT542    MASTER     06/14/2006    7 Hill st       12345
HT457    UNIQUE     10/27/2009    10 PARK WAY     24569   
QA987    UNIQUE     08/28/2010    10 PARK WAY     24569
AH825    UNIQUE     10/12/2012    10 PARK WAY     24569
14837    SINGLE     05/05/2010    2 TED ROAD      11111
24579    MARRIED    06/24/2014    2 TED ROAD      11111

我想要为每个重复的地址和SSN创建一个新列#,并且始终将ID#1设为最近的日期.

注意：此表仅具有基于地址和SSN的重复行,但具有唯一ID,并且不需要任何总和.

所以输出应该是这样的(单击图像放大)：

我进行了一些研究并尝试了一些示例,但没有任何工作来获得此输出.

我将不胜感激！

解决方法:

您需要枚举行并进行汇总.在MySQL(V8之前的版本)中,它看起来像：

select address, ssn,
       max(case when rn = 1 then id end) as id1,
       max(case when rn = 1 then type end) as type1,
       max(case when rn = 1 then date end) as date1,
       max(case when rn = 2 then id end) as id2,
       max(case when rn = 2 then type end) as type2,
       max(case when rn = 2 then date end) as date2
       . . .
from (select c.*,
             (@rn := if(@as = concat_ws(':', address, ssn), @rn + 1,
                        if(@as := concat_ws(':', address, ssn), 1, 1)
                       )
             ) as rn
      from (select c.* from customers c order by address, ssn, date desc) c cross join
           (select @as := '', @rn := 0) params
     ) c
group by address, ssn;

请注意,这不会重复address和ssn.这似乎没有用,但是您当然可以在每个组中重复这些列.