填补空值与去不空值在SQL服务器的邮政编码

Question 1

我有两个表格 PostalCodes (一栏中的值00至00至99-999)和 Customers (其中，旁边的所有客户的数据，一个邮政编码和身份证的雇员，其服务的客户)。

因此，这两个，我只加入通过邮政编码：

SELECT DISTINCT
    KP.postal,
    K.IDemp
FROM
    PostalCodes KP 
LEFT JOIN
    [Customers] K ON K.postal = KP.postal

并且我得到这个：

| postal | IDemp |
+--------+-------+
| 00-000 | NULL  |
| 00-001 | NULL  |
| 00-001 | 12PH  |
| 00-002 | NULL  |
| 00-003 | NULL  |
| 00-004 | NULL  |
| 00-004 | 10PH  |
| 00-005 | NULL  |
| ...    | ...   |

所以你可以看到不是所有的邮政编码使用中的 Customers 表，但是对于我的目标我需要你所有的邮政编码分配给一些雇员来创建像"区服务"，因此，要做到这一点，我想要填补的空值与去不null值得到这样的事情:

| postal | IDemp |
+--------+-------+
| 00-000 | NULL  |
| 00-001 | 12PH  |
| 00-002 | 12PH  |
| 00-003 | 12PH  |
| 00-004 | 10PH  |
| 00-005 | 10PH  |
| ...    | ...   |

我试图用 LAG() 功能，但它不是工作(或者至少我不知道如何正确使用)

LAG(K.IDemp) OVER (ORDER BY KP.postal)

我找到了些类似的问题，但不能来了如何使用他们的回答我的情况。

Question 2

SQL服务器不会支持的忽略空值的选择上 LAG (尚)的，但你可以得到解决此，通过创建一个二元的价值从柱你想要的顺序，并列要检索，并呼 MAX 这不会忽略空。一个完整的工作方案将是：

IF OBJECT_ID(N'tempdb..#T', 'U') IS NOT NULL
    DROP TABLE #T;

CREATE TABLE IF NOT EXISTS #T (Postal VARCHAR(6) NOT NULL, IDemp VARCHAR(4) NULL);
INSERT #T (Postal, IDemp)
VALUES
    ('00-000', NULL),
    ('00-001', '12PH'),
    ('00-002', NULL),
    ('00-003', NULL),
    ('00-004', '10PH'),
    ('00-005', NULL);


SELECT  *,
        LastNonNull = CONVERT(VARCHAR(6), 
                            SUBSTRING(
                                MAX(CONVERT(BINARY(6), Postal) + CONVERT(BINARY(4), IDemp)) 
                                    OVER(ORDER BY Postal), 7,4))
FROM    #T;

它可能有助于解释如果这是破了一点我们来看看结果：

SELECT  *,
        BinaryValue = CONVERT(BINARY(6), Postal) + CONVERT(BINARY(4), IDemp)
FROM    #T

邮政	IDemp	BinaryValue
00-000	空	空
00-001	12PH	0x30302D30303131325048
00-002	空	空
00-003	空	空
00-004	10PH	0x30302D30303431305048
00-005	空	空

由于连接空值产生一个空值，你只能得到一个价值，它不是空。然后可以利用的二进制序(左右)，并获得最大价值这一二元内的一个窗口的功能，也就是部分： MAX(...) OVER(ORDER BY Postal).

这消除了所有的空值(因为 MAX 忽略了 NULL)除了第一行，因为没有以前的非空值，并给数据如下：

邮政	IDemp	MaxBinaryValue
00-000	空	空
00-001	12PH	0x30302D30303131325048
00-002	空	0x30302D30303131325048
00-003	空	0x30302D30303131325048
00-004	10PH	0x30302D30303431305048
00-005	空	0x30302D30303431305048

然后只要情况下提取的部分的二进制你有兴趣(符7-10)和转换回来varchar使用 SUBSTRING 和 CONVERT

Question 3

一个相关子查询可能的工作：

SELECT DISTINCT
    KP.postal,
    (SELECT TOP 1 K.IDemp 
     FROM [Customers] K
     WHERE K.postal <= KP.postal
     AND K.IDemp Is Not Null
     ORDER BY K.postal DESC) As IDemp
FROM
    PostalCodes KP

GarethD · Answer 1 · 2021-11-23T13:48:50

SQL服务器不会支持的忽略空值的选择上 LAG (尚)的，但你可以得到解决此，通过创建一个二元的价值从柱你想要的顺序，并列要检索，并呼 MAX 这不会忽略空。一个完整的工作方案将是：

IF OBJECT_ID(N'tempdb..#T', 'U') IS NOT NULL
    DROP TABLE #T;

CREATE TABLE IF NOT EXISTS #T (Postal VARCHAR(6) NOT NULL, IDemp VARCHAR(4) NULL);
INSERT #T (Postal, IDemp)
VALUES
    ('00-000', NULL),
    ('00-001', '12PH'),
    ('00-002', NULL),
    ('00-003', NULL),
    ('00-004', '10PH'),
    ('00-005', NULL);


SELECT  *,
        LastNonNull = CONVERT(VARCHAR(6), 
                            SUBSTRING(
                                MAX(CONVERT(BINARY(6), Postal) + CONVERT(BINARY(4), IDemp)) 
                                    OVER(ORDER BY Postal), 7,4))
FROM    #T;

它可能有助于解释如果这是破了一点我们来看看结果：

SELECT  *,
        BinaryValue = CONVERT(BINARY(6), Postal) + CONVERT(BINARY(4), IDemp)
FROM    #T

邮政	IDemp	BinaryValue
00-000	空	空
00-001	12PH	0x30302D30303131325048
00-002	空	空
00-003	空	空
00-004	10PH	0x30302D30303431305048
00-005	空	空

由于连接空值产生一个空值，你只能得到一个价值，它不是空。然后可以利用的二进制序(左右)，并获得最大价值这一二元内的一个窗口的功能，也就是部分： MAX(...) OVER(ORDER BY Postal).

这消除了所有的空值(因为 MAX 忽略了 NULL)除了第一行，因为没有以前的非空值，并给数据如下：

邮政	IDemp	MaxBinaryValue
00-000	空	空
00-001	12PH	0x30302D30303131325048
00-002	空	0x30302D30303131325048
00-003	空	0x30302D30303131325048
00-004	10PH	0x30302D30303431305048
00-005	空	0x30302D30303431305048

然后只要情况下提取的部分的二进制你有兴趣(符7-10)和转换回来varchar使用 SUBSTRING 和 CONVERT

Richard Deeming · Answer 2 · 2021-11-23T13:38:05

1

一个相关子查询可能的工作：

SELECT DISTINCT
    KP.postal,
    (SELECT TOP 1 K.IDemp 
     FROM [Customers] K
     WHERE K.postal <= KP.postal
     AND K.IDemp Is Not Null
     ORDER BY K.postal DESC) As IDemp
FROM
    PostalCodes KP

Richard Deeming

2021-11-23 13:38:05

我猜这是类似于我提出上述，但交申请，然后更快。可能仍然是空的，如果第一个是空。因此，应该在两个方向和排序的差异的目标的邮政

vikjon0

我想这将取决于该索引的，但是我期望的相关子查询以及 CROSS APPLY 生产出非常相似的计划。

Richard Deeming

可能我有过不好的经验，在过去，但当然SQL服务器已有所演变。有时，当重新写入老套代码交申请一定的成功，但是当然的情况下，工作得很好我从来没有看到。

vikjon0

添加 AND K.IDemp IS NOT NULL 到子查询以忽视空。

Thorsten Kettner

@ThorstenKettner它不清楚从的问题，但我将认为 IDemp 列在 Customers 表 NOT NULL.

Richard Deeming

看看结果运的查询。邮政编码00-001结果在两个排，一个与IDemp'12PH一个与IDemp空。所以NULL不能来自外部的加入，但必须存在的客户表。

Thorsten Kettner

@ThorstenKettner好赶上。

Richard Deeming

填补空值与去不空值在SQL服务器的邮政编码

的问题

最好的答案

其他语言

此页面有其他语言版本

受欢迎的此类别

流行的问题，在这个类别