sql - SQL 行合并

Question

我有 2 个表，我在执行连接操作后检索以下结果。

在上面的结果中，我想使用 FirstName、LastName 和 Email 列进行分组，并将行合并到单行中。合并后，我还应该使用以下方案获得其他列。

1. 选择创建日期较新的数据。
2. 在第一种情况之后，如果它给出 NULL，则选择第二高的创建日期数据并执行此操作，直到我们找到非空结果。
3. 如果所有行都为空，那么我们可以将其保持为空。

例如，合并后我需要如下行。如果您看到我采用了最近创建的第 2 行日期行，但性别和 Address_1_State_Province 在最近创建的日期行上为空，因此我为此列采用了下一个最近创建的日期行数据值，直到我得到非空值。Address_1_City、Address_1_Street_1、Address_1_State_Province 为空，因为所有行都具有空值。

当前查询

 Select 
 case when u.firstname is null then ep.firstname 
 when u.insertDate > ep.INSERT_DATE then u.firstname else ep.firstname end 
 FirstName,
 case when u.lastname is null then ep.lastname 
 when u.insertDate > ep.INSERT_DATE then u.lastname else ep.lastname end 
 LastName,
 u.email as Email,
 u.gender as Gender,
 u.dob as Birthday,
 u.zip as Address_1_ZIP_Postal_Code,
 u.city as Address_1_City,
 u.street as Address_1_Street_1,
 u.province as Address_1_State_Province,
 Convert(datetime,[dbo].[returnproperdate]((case when 
 isnull(u.insertDate,'')<>'' then [dbo].[returnmmddyyyy](insertDate) else 
 ep.INSERT_DATE end))) as Created,
 u.privacy as Privacy
 from [dbo].[Users] u
 join 
 [dbo].[EventsParticipated] ep on u.email=ep.EMAIL and u.brand=ep.BRAND
 where u.Email='[e-mail]'
 

标签： sqlsql-servertsql

Answer 1

with cte as (Select 
 case when u.firstname is null then ep.firstname 
 when u.insertDate > ep.INSERT_DATE then u.firstname else ep.firstname end 
 FirstName,
 case when u.lastname is null then ep.lastname 
 when u.insertDate > ep.INSERT_DATE then u.lastname else ep.lastname end 
 LastName,
 u.email as Email,
 u.gender as Gender,
 u.dob as Birthday,
 u.zip as Address_1_ZIP_Postal_Code,
 u.city as Address_1_City,
 u.street as Address_1_Street_1,
 u.province as Address_1_State_Province,
 Convert(datetime,[dbo].[returnproperdate]((case when 
 isnull(u.insertDate,'')<>'' then [dbo].[returnmmddyyyy](insertDate) else 
 ep.INSERT_DATE end))) as Created,
 u.privacy as Privacy
 from [dbo].[Users] u
 join 
 [dbo].[EventsParticipated] ep on u.email=ep.EMAIL and u.brand=ep.BRAND
 where u.Email='[e-mail]'
 ),
 finalCTE as (select   FirstName, LastName, Email,
 (case when gender is null then  max(Gender)over (partition by firstname order by Created desc)end) Gender, 
 Birthday, (case when Address_1_ZIP_Postal_Code is null then  max(Address_1_ZIP_Postal_Code)over (partition by firstname order by Created desc)end) Address_1_ZIP_Postal_Code, 
 (case when Address_1_City is null then  max(Address_1_City)over (partition by firstname order by Created desc)end) Address_1_City, 
 (case when Address_1_Street_1 is null then  max(Address_1_Street_1)over (partition by firstname order by Created desc)end) Address_1_Street_1, 
 (case when Address_1_State_Province is null then  max(Address_1_State_Province)over (partition by firstname order by Created desc)end) Address_1_State_Province, 
 Created ,Privacy,row_number()over(partition by firstname  order by Created desc) rn from cte)
 select FirstName, LastName, Email, Gender, Birthday, Address_1_ZIP_Postal_Code, Address_1_City, Address_1_Street_1,
 Address_1_State_Province,  Created,Privacy from finalCTE where rn=1

新查询

       select FirstName, LastName, Email, Gender, Birthday, 
       Address_1_ZIP_Postal_Code, Address_1_City, Address_1_Street_1,
       Address_1_State_Province,  Created,Privacy from
       (
       select  FirstName, LastName, Email,
       (case when gender is null then  max(Gender)over (partition by firstname order by Created desc)end) Gender, 
       Birthday, (case when Address_1_ZIP_Postal_Code is null then  max(Address_1_ZIP_Postal_Code)over (partition by firstname order by Created desc)end) Address_1_ZIP_Postal_Code, 
       (case when Address_1_City is null then  max(Address_1_City)over (partition by firstname order by Created desc)end) Address_1_City, 
       (case when Address_1_Street_1 is null then  max(Address_1_Street_1)over (partition by firstname order by Created desc)end) Address_1_Street_1, 
       (case when Address_1_State_Province is null then  max(Address_1_State_Province)over (partition by firstname order by Created desc)end) Address_1_State_Province, 
       Created ,Privacy,row_number()over(partition by firstname  order by Created desc) rn 
       from [dbo].[JoinedResultUserandEvent])T
       where T.rn=1

第三个查询：

       select FirstName, LastName, Email, Gender, Birthday, Address_1_ZIP_Postal_Code, Address_1_City, Address_1_Street_1,
       Address_1_State_Province,  Created,Privacy from
       (
       select  FirstName, LastName, Email,
       (case when gender is null then  max(Gender)over (partition by firstname,lastname,email)end) Gender, 
       (case when Birthday is null then  max(Birthday)over (partition by firstname,lastname,email)end) Birthday, 
       (case when Address_1_ZIP_Postal_Code is null then  max(Address_1_ZIP_Postal_Code)over (partition by firstname,lastname,email)end) Address_1_ZIP_Postal_Code, 
       (case when Address_1_City is null then  max(Address_1_City)over (partition by firstname,lastname,email)end) Address_1_City, 
       (case when Address_1_Street_1 is null then  max(Address_1_Street_1)over (partition by firstname,lastname,email )end) Address_1_Street_1, 
       (case when Address_1_State_Province is null then  max(Address_1_State_Province)over (partition by firstname,lastname,email )end) Address_1_State_Province, 
       Created ,Privacy,row_number()over(partition by firstname,lastname,email  order by Created desc) rn 
       from [dbo].[JoinedResultUserandEvent])T
       where T.rn=1 and T.Email='[e-mail]'

合并前：

合并后：