代码之家 › 专栏 › 技术社区 › jnoltee

多个case语句返回一列多行

case-when unpivot alias multiple-columns sql

jnoltee · 技术社区 · 6 年前

我需要评估两组时间戳,并创建一个可能有多行的列。为了使事情更加复杂,我需要使用函数来收集这些时间戳。我想要什么:

SELECT DISTINCT

mpd.ccn AS CCN,
mpd.date AS Date,
[functionAStartTime] AS A_Start,
[functionAStopTime] AS A_Stop,
[functionBStartTime] AS B_Start,
[functionBStopTime] AS B_Stop,
mpd.fac AS Facility,
bmpi.mod AS Module,
CASE WHEN [functionAStartTime] <= '05:59' AND [functionAStopTime] >= '00:00' THEN 'A' END AS TimeGroup,
CASE WHEN [functionBStartTime] <= '05:59' AND [functionBStopTime] >= '00:00' THEN 'A' END AS TimeGroup,
CASE WHEN [functionAStartTime] <= '11:59' AND [functionAStopTime] >= '06:00' THEN 'B' END AS TimeGroup,
CASE WHEN [functionBStartTime] <= '11:59' AND [functionBStopTime] >= '06:00' THEN 'B' END AS TimeGroup,
CASE WHEN [functionAStartTime] <= '17:59' AND [functionAStopTime] >= '12:00' THEN 'C' END AS TimeGroup,
CASE WHEN [functionBStartTime] <= '17:59' AND [functionBStopTime] >= '12:00' THEN 'C' END AS TimeGroup,
CASE WHEN [functionAStartTime] <= '23:59' AND [functionAStopTime] >= '18:00' THEN 'D' END AS TimeGroup,
CASE WHEN [functionBStartTime] <= '23:59' AND [functionBStopTime] >= '18:00' THEN 'D' END AS TimeGroup

FROM

bmpi,
mpd

WHERE

mpd.pid = bmpi.pid
AND mpd.cec = bmpi.cec

每个CCN可以具有一组或两组时间戳,并且任一组可以跨越多个组。这显然会创建多个列(Time\u Group、Time\u Group\u 1、Time\u Group\u 3等)。我需要一个“Time Group”列,每个结果都有一行。例如:

CCN   |  Date    | A_Start | A_Stop | B_Start | B_Stop | Facility | Module | Time Group
1234  | 01/01/01 | 07:00   | 12:00  | 17:00   | 21:00  | WPDH     | 0012   | B
1234  | 01/01/01 | 07:00   | 12:00  | 17:00   | 21:00  | WPDH     | 0012   | C
1234  | 01/01/01 | 07:00   | 12:00  | 17:00   | 21:00  | WPDH     | 0021   | C
1234  | 01/01/01 | 07:00   | 12:00  | 17:00   | 21:00  | WPDH     | 0021   | D
4321  | 02/02/02 | 02:00   | 03:00  | 13:00   | 14:00  | ABCD     | 0012   | A
4321  | 02/02/02 | 02:00   | 03:00  | 13:00   | 14:00  | ABCD     | 0021   | C
0001  | 03/03/03 | 06:00   | 08:00  | NULL    | NULL   | WPDH     | 0012   | B

我一直在教自己在需要的时候寻找我需要的东西,所以如果能举个例子和解释,我将不胜感激。

1 回复 | 直到 6 年前

tysonwright 6 年前

因此,CASE语句是SQL中执行“if-then-else”逻辑的一种方式,而不是获得多个结果的方式。

我认为您需要一个子查询,该子查询将每个时间组值作为单独的列,然后主查询将取消对这些列的IVOT。类似的方法可能会奏效:

SELECT 
    CCN,
    [Date],
    A_Start,
    A_Stop,
    B_Start,
    B_Stop,
    Facility,
    Module,
    TimeGroup,
    TimeGroupValues
FROM
    (
    SELECT DISTINCT
        mpd.ccn AS CCN,
        mpd.date AS [Date],
        [functionAStartTime] AS A_Start,
        [functionAStopTime] AS A_Stop,
        [functionBStartTime] AS B_Start,
        [functionBStopTime] AS B_Stop,
        mpd.fac AS Facility,
        bmpi.mod AS Module,
        CASE 
            WHEN [functionAStartTime] <= '05:59' AND [functionAStopTime] >= '00:00' 
            THEN 'A' 
            WHEN [functionBStartTime] <= '05:59' AND [functionBStopTime] >= '00:00' 
            THEN 'A' 
            END AS TimeGroupA,
        CASE 
            WHEN [functionAStartTime] <= '11:59' AND [functionAStopTime] >= '06:00' 
            THEN 'B' 
            WHEN [functionBStartTime] <= '11:59' AND [functionBStopTime] >= '06:00' 
            THEN 'B' 
            END AS TimeGroupB,
        CASE 
            WHEN [functionAStartTime] <= '17:59' AND [functionAStopTime] >= '12:00' 
            THEN 'C' 
            WHEN [functionBStartTime] <= '17:59' AND [functionBStopTime] >= '12:00' 
            THEN 'C' 
            END AS TimeGroupC,
        CASE 
            WHEN [functionAStartTime] <= '23:59' AND [functionAStopTime] >= '18:00' 
            THEN 'D'
            WHEN [functionBStartTime] <= '23:59' AND [functionBStopTime] >= '18:00' 
            THEN 'D' 
            END AS TimeGroupD
    FROM
        bmpi
    JOIN
        mpd
    ON
        mpd.pid = bmpi.pid
    AND 
        mpd.cec = bmpi.cec
    ) AS u
    UNPIVOT
        (TimeGroup FOR TimeGroupValues IN
            (
                TimeGroupA, 
                TimeGroupB, 
                TimeGroupC, 
                TimeGroupD
            )
        ) AS p