时间戳

首页 > 解决方案 > Teradata/Sql_server 查询以合并特定条件的连续行

sql - Teradata/Sql_server 查询以合并特定条件的连续行

问题描述

我有一个数据表如下

DB  DBMS INST   SCHEMA  TABLE   COLUMN  HDFT    N_Identity  Class
IDS TD   SBD    IDS   Data_Val  cust_t                      HIGH
IDS TD   SBD    IDS   Data_Val  cust_t  GID     
IDS TD   SBD    IDS   Data_Val  cust_t  Phone       
IDS TD   SBD    IDS   Data_Val  cust_t  Account     
IDS TD   SBD    IDS   Data_Val  cust_t  Visa        
IDS TD   SBD    IDS   Data_Val  cust_t  Mail        
IDS TD   SBD    IDS   Data_Val  cust_t  Email       
IDS TD   SBD    IDS   Data_Val  cust_t  Login   Yes 
TDS TD   FDT    TDS   Expense   Exp_t   Name                 LOW
TDS TD   FDT    TDS   Expense   Exp_t           Yes

我想要输出如下：

DB  DBMS INST   SCHEMA  TABLE   COLUMN  HDFT    N_Identity  Class
IDS TD   SBD    IDS   Data_Val  cust_t  GID     Yes         HIGH
IDS TD   SBD    IDS   Data_Val  cust_t  Phone   Yes         HIGH
IDS TD   SBD    IDS   Data_Val  cust_t  Account Yes         HIGH
IDS TD   SBD    IDS   Data_Val  cust_t  Visa    Yes         HIGH
IDS TD   SBD    IDS   Data_Val  cust_t  Mail    Yes         HIGH
IDS TD   SBD    IDS   Data_Val  cust_t  Email   Yes         HIGH
IDS TD   SBD    IDS   Data_Val  cust_t  Login   Yes         HIGH
TDS TD   FDT    TDS   Expense   Exp_t   Name    Yes         LOW

对于特定列，N_identity 将具有 Yes 或 No 值。到目前为止，我已尝试使用以下查询，但它没有给我想要的结果：

SELECT * FROM
(
   SELECT * FROM
   (
        SELECT DB,DBMS,INST,SCHEMA,TABLE,COLUMN, MAX(HDFT) as HDFT, MAX(N_Identity) as N_Identity, MAX(Class) as Class  
        FROM Table
        GROUP BY DB,DBMS,INST,SCHEMA,TABLE,COLUMN
   )a 

  UNION

  SELECT DB,DBMS,INST,SCHEMA,TABLE,COLUMN, HDFT, N_Identity, Class FROM Table

)b

WHERE HDFT IS NOT NULL
AND N_Identity IS NOT NULL
AND Class IS NOT NULL

此外，HDFT 值可以为空，以下是一种情况：

DB  DBMS INST   SCHEMA  TABLE   COLUMN  HDFT    N_Identity  Class
IDS TD   SBD    IDS   Data_Val  cust_t          No          INT
IDS TD   SBD    IDS   Data_Val  cust_t                      INT
IDS TD   SBD    IDS   Data_Val  cust_t          No

预期结果：

DB  DBMS INST   SCHEMA  TABLE   COLUMN  HDFT    N_Identity  Class
IDS TD   SBD    IDS   Data_Val  cust_t          No          INT

标签： sqlsql-serverteradata

解决方案

计算N_Identity并与看起来Class分开，HDFT因为它们的业务规则不同。加入结果。如果存在任何非 NULL 值，则HDFT子查询将消除。NULL它建立在被NULL视为最小值的 Sql-server 排序之上。

   SELECT nic.DB, nic.DBMS, nic.INST, nic.SCHEMA, nic.TABLE, nic.COLUMN
       , h.HDFT
       nic.N_Identity, nic.Class
   FROM
   (
        SELECT DB,DBMS,INST,SCHEMA,TABLE,COLUMN, MAX(N_Identity) as N_Identity, MAX(Class) as Class  
        FROM Table
        GROUP BY DB,DBMS,INST,SCHEMA,TABLE,COLUMN
   ) nic 
   JOIN   
   (
     SELECT DB,DBMS,INST,SCHEMA,TABLE,COLUMN, max(HDFT) HDFT
     FROM 
     (
         SELECT DB,DBMS,INST,SCHEMA,TABLE,COLUMN, HDFT
          , case when HDFT is null then 1
                 else dense_rank() over(
                   partition by DB,DBMS,INST,SCHEMA,TABLE,COLUMN
                   order by HDFT desc) 
             end rnk
         FROM Table
     ) t
     GROUP BY DB,DBMS,INST,SCHEMA,TABLE,COLUMN, rnk
   ) h ON h.DB = nic.DB AND h.DBMS = nic.DBMS AND h.INST = nic.INST AND h.SCHEMA = nic.SCHEMA AND h.TABLE = nic.TABLE AND h.COLUMN = nic.COLUMN

推荐阅读