时间戳

首页 > 解决方案 > 根据列值选择多行

sql - 根据列值选择多行

问题描述

我有一张这样的桌子：

+----------+----------+----------+----------+----------+
| COLI_KEY | COLI_DAY | COLI_WEE | COLI_MON | COLI_YEA |
+----------+----------+----------+----------+----------+
|        1 | 1        | NULL     |        1 | NULL     |
|        2 | NULL     | NULL     |        1 | 1        |
|        3 | 1        | 1        |        1 | 1        |
+----------+----------+----------+----------+----------+

我想做的是获取 COLI_DAY、COLI_WEE、COLI_MON、COLI_YEA 中每个值的每一行。所以基本上我需要一个SELECT语句，它会给我想要的结果（每行只标记一列 - 其他列为空）：

+----------+----------+----------+----------+----------+
| COLI_KEY | COLI_DAY | COLI_WEE | COLI_MON | COLI_YEA |
+----------+----------+----------+----------+----------+
|        1 | 1        | null     | null     | null     |
|        1 | null     | null     | 1        | null     |
|        2 | null     | null     | 1        | null     |
|        2 | null     | null     | null     | 1        |
|        3 | 1        | null     | null     | null     |
|        3 | null     | 1        | null     | null     |
|        3 | null     | null     | 1        | null     |
|        3 | null     | null     | null     | 1        |
+----------+----------+----------+----------+----------+

创建表的脚本：

declare @CACOLI table
(
    COLI_KEY INT,
    COLI_DAY CHAR(1),
    COLI_WEE CHAR(1),
    COLI_MON CHAR(1),
    COLI_YEA CHAR(1)
)

INSERT INTO @CACOLI (COLI_KEY, COLI_DAY, COLI_WEE, COLI_MON, COLI_YEA)
VALUES (1, '1', NULL, '1', NULL),
       (2, NULL, NULL, '1', '1'),
       (3, '1', '1', '1', '1')

标签： sqlsql-servertsql

解决方案

这是一种直截了当的方法，我为每一列选择输出，同时保持其他列为 NULL 并将它们全部合并。

SELECT
    COLI_KEY, COLI_DAY, NULL AS COLI_WEE, NULL AS COLI_MON, NULL AS COLI_YEA
WHERE 
    COLI_DAY = 1

UNION

SELECT 
    COLI_KEY, NULL AS COLI_DAY, COLI_WEE, NULL AS COLI_MON, NULL AS COLI_YEA
WHERE 
    COLI_WEE = 1

UNION

SELECT
    COLI_KEY, NULL AS COLI_DAY, NULL AS COLI_WEE, COLI_MON, NULL AS COLI_YEA
WHERE 
    COLI_MON = 1

UNION

SELECT
    COLI_KEY, NULL AS COLI_DAY, NULL AS COLI_WEE, NULL AS COLI_MON, COLI_YEA
WHERE 
    COLI_YEA = 1

推荐阅读