时间戳

首页 > 解决方案 > SQL Server：CONCAT 与 CASE

sql-server - SQL Server：CONCAT 与 CASE

问题描述

我一直在玩这个，但未能得到答案。

查询案例：

  1. 如果 Table1 中有 EndDate 而 Table 2 中没有 EndDate，则连接 Table1 SSC + Table1 StartDate + Table1 EndDate

  2. 如果两个表上都没有 EndDate，则显示 Table1 SSC 值 + Table2 数据列中的 Table2 StartDate 空间值

表格样本

CREATE TABLE temp.dbo.Table1
(
     CID INT,
     PID INT,
     SSC VARCHAR(3),
     StartDate VARCHAR(20),
     EndDate VARCHAR(20), 
     Data VARCHAR(20)
)

INSERT INTO temp.dbo.Table1
VALUES
(1001,  1333,   'OP','20011505','19952012','OP2001156519952012'),
(1002,  1245,   'OR','20121005',NULL,'OR20121005'),
(1003,  1116,   'OP','20131215',NULL,'OP20131215'),
(1004,  1234,   'OP','20011505','19952012','OP2001156519952012')    

CREATE TABLE temp.dbo.Table2
(
     CID INT,
     PID INT,
     SSC VARCHAR(3),
     StartDate VARCHAR(20),
     EndDate VARCHAR(20), 
     Data VARCHAR(20)
)

INSERT INTO temp.dbo.Table2
VALUES
(1001,  1333,   'OP','20111015',NULL,'OP20111015'),
(1002,  1245,   'OR','20131005',NULL,'OR20131005'),
(1003,  1116,   'OP','20131415',NULL,'OP2013141520131516'),
(1004,  1235,   'OP','20121015',NULL,'OP20121015')

我正在处理的查询在这里

SELECT 
    T1.CID,
    T1.PID,
    CASE
       WHEN T1.EndDate IS NOT NULL AND ISNULL(T2.EndDate,'') <>'' 
          THEN CONCAT(T1.SSC, T1.StartDate, T1.EndDate)
       WHEN ISNULL(T1.EndDate,'') <> '' AND ISNULL(T2.EndDate,'') <> '' 
          THEN CONCAT(T1.SSC, T2.StartDate, '  ', T2.Data)
       ELSE NULL 
    END
FROM 
    temp.dbo.TABLE1 AS T1
LEFT JOIN  
    temp.dbo.TABLE2 AS T2 ON T1.CID = T2.CID AND T1.PID = T2.PID AND T1.SSC = T2.SSC

此屏幕截图显示了示例数据：

在此处输入图像描述

标签： sql-server

解决方案

你很接近，只是一个小的语法错误。

你的第一个场景：

Query CASES: 1_If there is EndDate in Table1 and no EndDate in Table 2, then concatenate Table1 SSC + Table1 StartDate + Table1 EndDate IF you look at your case statement isnull(t2.enddate,'') should be = '' rather than <> '' 

2_If there is no EndDate on both tables then display Table1 SSC value + Table2 StartDate space value from Table2 Data column

For second case statement it should be (isnull = rather than <> for both of them

此查询将提供您想要的输出。

SELECT T1.CID
,T1.PID
,CASE
    WHEN T1.EndDate IS NOT NULL AND ISNULL(T2.EndDate,'') = '' THEN CONCAT(T1.SSC, T1.StartDate,T1.EndDate)
    WHEN ISNULL(T1.EndDate,'') =  '' AND ISNULL(T2.EndDate,'') =  ''  THEN CONCAT(T1.SSC, T2.StartDate, '  ', T2.Data)
ELSE NULL END as concatedvalues 


FROM temp.dbo.TABLE1 AS T1
LEFT JOIN temp.dbo.TABLE2 AS T2 ON T1.CID = T2.CID AND T1.PID = T2.PID AND T1.SSC = T2.SSC


  CID   PID     concatedvalues
1001    1333    OP2001150519952012
1002    1245    OR20131005  OR20131005
1003    1116    OP20131415  OP2013141520131516
1004    1234    OP2001150519952012

我的建议（如果有默认日期，例如 1900-01-01，SQL Server 会将它们视为空白（''）（对于日期字段空白（''）表示 1900-01-01'））在 1900 的情况下应被视为有效日期。如果您只需要比较空字段，请尝试使用空或非空条件而不是 isnull(date,'')= ''

推荐阅读