时间戳

首页 > 解决方案 > SQL 查询组按包含

sql - SQL 查询组按包含

问题描述

有人可以帮助修改以下 SQL 脚本，以便我能够执行 GROUP BY 并替换字符（null）。

目前我有两张桌子：

表格1

PK_LOYALTYACCOUNT     COUNTRY_ID     CDC_TYPE
int                   varchar(50)    varchar(50)
666                   DE             U
98                    DE             U
27975373              DE             U
666                   DE             N

表 2

PK_LOYALTYACCOUNT     COUNTRY_ID     CDC_TYPE
int                   varchar(50)    varchar(50)
666                   DE             U
75                    DE             U
27975370              DE             U
578                   WE             N
54                    RT             N

sql 脚本的结果生成下表：

is_deleted     PK_LOYALTYACCOUNT     COUNTRY_ID     CDC_TYPE
int            int                   varchar(50)    varchar(50)
1              666                   DE             U
0              666                   DE             U
(null)         769                   DE             U
(null)         578                   WE             N
(null)         54                    RT             N

但是，我想按表分组（以任何方式）并删除或替换空值，以便表显示如下：

COUNTRY_ID     CDC_TYPE     PK_LOYALTYACCOUNT    is_deleted

DE             U            666                  1
DE             U            666                  0
RT             N            54                   replace null
WE             N            578                  replace null
DE             N            769                  replace null

sql脚本如下：

with cmn as 
  ( SELECT a.CDC_TYPE,
           a. PK_LOYALTYACCOUNT, --Add these also in CTE result set 
           a.COUNTRY_ID --Add these also in CTE result set 
    FROM  tabled  a  
    INNER JOIN tablee b  
    ON a.COUNTRY_ID = b.COUNTRY_ID 
    AND a.PK_LOYALTYACCOUNT = b.PK_LOYALTYACCOUNT  
    AND a.CDC_TYPE = 'U'
    )
 SELECT 1 AS is_deleted, 
        a.*  
 FROM  tabled  a  
 INNER JOIN cmn  
 ON a.CDC_TYPE = cmn.CDC_TYPE 
 and  a.COUNTRY_ID = cmn.COUNTRY_ID  
 AND a.PK_LOYALTYACCOUNT = cmn.PK_LOYALTYACCOUNT

 UNION ALL  
 SELECT 0 AS is_deleted, 
        b.*  
 FROM tablee  b  
 INNER JOIN cmn  
 ON b.CDC_TYPE = cmn.CDC_TYPE 
 and b.COUNTRY_ID = cmn.COUNTRY_ID 
 AND b.PK_LOYALTYACCOUNT = cmn.PK_LOYALTYACCOUNT
UNION ALL 
SELECT NULL AS CDC_TYPE,
       a.* 
FROM   tabled a 
WHERE  a.CDC_TYPE = 'N' 
UNION ALL 
SELECT NULL AS CDC_TYPE, 
       b.* 
FROM   tablee b 
WHERE  b.CDC_TYPE = 'N'

提前致谢

标签： sqlsql-server

解决方案

您也许可以在所有这些联合查询中为“is_deleted”使用一个字符串。

但是也许这个查询也可以通过 FULL JOIN 来简化

例如：

SELECT PK_LOYALTYACCOUNT, COUNTRY_ID, CDC_TYPE, IS_DELETED
FROM
(
    SELECT 
    COALESCE(d.PK_LOYALTYACCOUNT, e.PK_LOYALTYACCOUNT) AS PK_LOYALTYACCOUNT, 
    COALESCE(d.COUNTRY_ID, e.COUNTRY_ID) AS COUNTRY_ID, 
    COALESCE(d.CDC_TYPE, e.CDC_TYPE) AS CDC_TYPE,
    (CASE 
     WHEN d.CDC_TYPE = e.CDC_TYPE AND d.CDC_TYPE = 'U' THEN '1' 
     WHEN d.CDC_TYPE = e.CDC_TYPE AND d.CDC_TYPE = 'N' THEN '0' 
     ELSE '' 
     END) AS IS_DELETED
    FROM tabled d
    FULL JOIN tablee e 
      ON e.PK_LOYALTYACCOUNT = d.PK_LOYALTYACCOUNT 
     AND e.COUNTRY_ID = d.COUNTRY_ID 
     AND e.CDC_TYPE = d.CDC_TYPE
    WHERE d.CDC_TYPE = e.CDC_TYPE 
       OR d.CDC_TYPE = 'N'
       OR e.CDC_TYPE = 'N'
) AS cmn 
GROUP BY PK_LOYALTYACCOUNT, COUNTRY_ID, CDC_TYPE, IS_DELETED
ORDER BY IS_DELETED DESC, PK_LOYALTYACCOUNT, COUNTRY_ID, CDC_TYPE;

推荐阅读