时间戳

首页 > 解决方案 > 将数据从列转换为行，仅当列的并集不为空时才选择，使用 where 子句检查不为空和 != ''

sql - 将数据从列转换为行，仅当列的并集不为空时才选择，使用 where 子句检查不为空和 != ''

问题描述

我需要选择查询仅在电话列不为空时进行选择。

我在 CRM 中有一条电话记录，其中三列中有电话号码。ERP 有一个包含一列的电话记录，因此它需要记录每种电话类型：工作、家庭手机。我想将记录从 CRM 导入到 ERP。插入查询的 select 语句有效，但是当电话号码为空时，它会插入一条电话号码为空的记录。我需要它仅在填充电话号码时插入记录。下面列出了一个查询示例，其中包含 from 语句的子查询中的测试数据。

SQL Server 2008 R2

SELECT
    ID,
    Label,
    PHONE
FROM
    (SELECT 
         90864 AS ID, 'HOME' AS LABEL, '9185410013' AS PHONE
     UNION 
     SELECT 
         90864 AS ID, 'WORK' AS LABEL, '' AS PHONE
     UNION 
     SELECT 
         90864 AS ID, 'CELL' AS LABEL, '' AS PHONE
    ) AS Test_Data

标签： sql

解决方案

显式版本（更新，添加了一些空值只是为了显示它在每个描述中有效）

SELECT
    ID,
    Label,
    PHONE
FROM
(        
SELECT 90864 AS ID, 'HOME' AS LABEL, '9185410013' AS PHONE
UNION 
SELECT 90864 AS ID, 'WORK' AS LABEL, '' AS PHONE
UNION 
SELECT 90864 AS ID, 'CELL' AS LABEL, '' AS PHONE
UNION
SELECT 90865 AS ID, 'HOME' AS LABEL, '9185410013' AS PHONE
UNION 
SELECT 90865 AS ID, 'WORK' AS LABEL, null AS PHONE
UNION 
SELECT 90865 AS ID, 'CELL' AS LABEL, null AS PHONE
) as Test_Data
WHERE PHONE is not null AND PHONE != ''

结果：

ID  Label   PHONE
90864   HOME    9185410013
90865   HOME    9185410013

推荐阅读