Umplere null-uri cu ultima nu valoarea null în SQL Server pe Coduri Poștale

Question 1

Am doua tabele PostalCodes (cu o coloană cu valori de la 00-00 la 99-999) și Customers (care are, pe lângă toate datele clienților, un cod poștal și ID-ul de angajat care este servirea clientului).

Deci, aceste două sunt pur și simplu prin aderarea cod postal:

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

și voi face acest lucru:

| 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  |
| ...    | ...   |

Deci, după cum puteți vedea, nu toate codurile poștale sunt utilizate în Customers masă, dar pentru scopul meu am nevoie de toate codurile poștale atribuit un angajat a creat ceva de genul "zona de serviciu", așa că vreau să umplu valorile nule cu ultima nu valoarea null pentru a obține ceva de genul asta:

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

Am fost încercarea de a utiliza LAG() funcție, dar nu a fost de lucru (sau cel puțin eu nu știu cum l folosească în mod corespunzător)

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

Am găsit câteva întrebări similare deja, dar nu a putut veni cum să folosiți răspunsurile lor la cazul meu.

Question 2

SQL Server nu acceptă ignore null opțiune pe LAG (încă), dar puteți obține în jurul valorii de acest lucru prin crearea unui binar valoarea din coloana în care doriți să comandați de către, și coloana în care doriți să preluați și de asteptare MAX care nu ignore null-uri. Un lucru complet soluție ar fi:

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;

S-ar putea explica dacă acesta este rupt în jos un pic și ne uităm la rezultatele de asta:

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

Poștale	IDemp	BinaryValue
00-000	NULL	NULL
00-001	12PH	0x30302D30303131325048
00-002	NULL	NULL
00-003	NULL	NULL
00-004	10PH	0x30302D30303431305048
00-005	NULL	NULL

Din concatenarea valoarea null dă o valoare nulă, veți obține doar o valoare în cazul în care nu este null. Apoi, puteți profita de binar de sortare (de la stânga la dreapta) și se obține valoarea maximă a acestui binare într-o fereastră funcție, care este parte: MAX(...) OVER(ORDER BY Postal).

Acest lucru elimină toate valorile NULE (din MAX ignoră NULL) în afară de primul rând, deoarece nu există nici o anterior valoare nenulă și oferă date după cum urmează:

Poștale	IDemp	MaxBinaryValue
00-000	NULL	NULL
00-001	12PH	0x30302D30303131325048
00-002	NULL	0x30302D30303131325048
00-003	NULL	0x30302D30303131325048
00-004	10PH	0x30302D30303431305048
00-005	NULL	0x30302D30303431305048

Apoi este doar un caz de a extrage porțiunea de binar sunteți interesat în (personaje 7-10) și conversia înapoi la varchar folosind SUBSTRING și CONVERT

Question 3

Un corelate sub-interogare ar putea lucra:

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 Server nu acceptă ignore null opțiune pe LAG (încă), dar puteți obține în jurul valorii de acest lucru prin crearea unui binar valoarea din coloana în care doriți să comandați de către, și coloana în care doriți să preluați și de asteptare MAX care nu ignore null-uri. Un lucru complet soluție ar fi:

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;

S-ar putea explica dacă acesta este rupt în jos un pic și ne uităm la rezultatele de asta:

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

Poștale	IDemp	BinaryValue
00-000	NULL	NULL
00-001	12PH	0x30302D30303131325048
00-002	NULL	NULL
00-003	NULL	NULL
00-004	10PH	0x30302D30303431305048
00-005	NULL	NULL

Din concatenarea valoarea null dă o valoare nulă, veți obține doar o valoare în cazul în care nu este null. Apoi, puteți profita de binar de sortare (de la stânga la dreapta) și se obține valoarea maximă a acestui binare într-o fereastră funcție, care este parte: MAX(...) OVER(ORDER BY Postal).

Acest lucru elimină toate valorile NULE (din MAX ignoră NULL) în afară de primul rând, deoarece nu există nici o anterior valoare nenulă și oferă date după cum urmează:

Poștale	IDemp	MaxBinaryValue
00-000	NULL	NULL
00-001	12PH	0x30302D30303131325048
00-002	NULL	0x30302D30303131325048
00-003	NULL	0x30302D30303131325048
00-004	10PH	0x30302D30303431305048
00-005	NULL	0x30302D30303431305048

Apoi este doar un caz de a extrage porțiunea de binar sunteți interesat în (personaje 7-10) și conversia înapoi la varchar folosind SUBSTRING și CONVERT

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

1

Un corelate sub-interogare ar putea lucra:

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

Cred că acest lucru este similar cu ceea ce propun eu mai sus, dar crucea aplica este atunci mai repede. Ar putea fi încă null dacă primul este nul. Așa ar trebui să arate în ambele direcții și fel de dif la țintă poștale

vikjon0

Cred că ar depinde de indicii, dar m-aș aștepta corelate sub-interogare și un CROSS APPLY pentru a produce foarte planuri similare.

Richard Deeming

Eventual, am avut experiențe neplăcute în trecut, dar, desigur, SQL server a evoluat . Acolo au fost un timp când re-scriere veche imbricate codul de la cruce se aplică fost un succes sigur dar, desigur, cazurile la care a lucrat bine n-aș fi văzut.

vikjon0

Adauga AND K.IDemp IS NOT NULL la subinterogare în scopul de a neglija null-uri.

Thorsten Kettner

@ThorstenKettner nu e clar la întrebare, dar aș presupune că IDemp coloană în Customers masa este NOT NULL.

Richard Deeming

Uita-te la rezultatul de OP interogare. Codul poștal 00-001 rezultatele în două rânduri, unul cu IDemp '12PH' una cu IDemp NULL. Deci NULĂ nu poate proveni din exterior se alăture, dar trebuie să existe pe masa clientilor.

Thorsten Kettner

@ThorstenKettner captură Bună.

Richard Deeming

Umplere null-uri cu ultima nu valoarea null în SQL Server pe Coduri Poștale

Problema

Cel mai bun răspuns

În alte limbi

Această pagină este în alte limbi

Popular în această categorie

Întrebări frecvente în această categorie