Posta kodları SQL Server'da son not null değeri ile boş değerlere doldurma

Question 1

İki masam var. PostalCodes (00-00 ile 99-999 arasındaki değerlere sahip bir sütun ile) ve Customers (müşterinin tüm verilerinin yanında, müşteriye hizmet veren çalışanın posta kodu ve kimliği vardır).

Bu yüzden bu ikisine sadece posta koduyla katılıyorum:

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

ve bunu alıyorum:

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

Gördüğünüz gibi, tüm posta kodları Customers ancak amacım için, "hizmet alanı" gibi bir şey oluşturmak için bazı çalışanlara atanan tüm posta kodlarına ihtiyacım var, bu yüzden bunun gibi bir şey elde etmek için boş değerleri son boş olmayan değerle doldurmak istiyorum:

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

Kullanmaya çalışıyordum LAG() işlev, ancak çalışmıyordu (ya da en azından nasıl düzgün kullanılacağını bilmiyorum)

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

Zaten birkaç benzer soru buldum, ancak cevaplarını davama nasıl kullanacağımı bulamadım.

Question 2

SQL Server boş değerlere yoksay seçeneğini desteklemiyor LAG (henüz), ancak sipariş etmek istediğiniz sütundan ve almak ve çağırmak istediğiniz sütundan ikili bir değer oluşturarak bunu atlatabilirsiniz MAX boşluk görmezden yapar. Tam bir çalışma çözümü şöyle olurdu:

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;

Bunun biraz bozulup bozulmadığını açıklamaya yardımcı olabilir ve bunun sonuçlarına bakarız:

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

Posta	Idempname	BinaryValue
00-000	boş	boş
00-001	12PH	0x30302D30303131325048
00-002	boş	boş
00-003	boş	boş
00-004	10PH	0x30302D30303431305048
00-005	boş	boş

Null değerin birleştirilmesi null değer verdiğinden, yalnızca null olmadığı bir değer elde edersiniz. Daha sonra ikili sıralamadan (soldan sağa) yararlanabilir ve pencereli bir işlev içinde bu ikilinin maksimum değerini elde edebilirsiniz, yani bölüm: MAX(...) OVER(ORDER BY Postal).

Bu, tüm boş değerleri kaldırır (çünkü MAX aldırıyor NULL) ilk satırdan ayrı olarak, önceki boş olmayan bir değer olmadığından ve aşağıdaki gibi veri verdiğinden:

Posta	Idempname	MaxBinaryValue
00-000	boş	boş
00-001	12PH	0x30302D30303131325048
00-002	boş	0x30302D30303131325048
00-003	boş	0x30302D30303131325048
00-004	10PH	0x30302D30303431305048
00-005	boş	0x30302D30303431305048

Daha sonra, ilgilendiğiniz ikilinin bir kısmını (7-10 karakterleri) çıkarmak ve kullanarak varchar'a geri dönüştürmek sadece bir durumdur SUBSTRING ve CONVERT

Question 3

İlişkili bir alt sorgu işe yarayabilir:

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 boş değerlere yoksay seçeneğini desteklemiyor LAG (henüz), ancak sipariş etmek istediğiniz sütundan ve almak ve çağırmak istediğiniz sütundan ikili bir değer oluşturarak bunu atlatabilirsiniz MAX boşluk görmezden yapar. Tam bir çalışma çözümü şöyle olurdu:

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;

Bunun biraz bozulup bozulmadığını açıklamaya yardımcı olabilir ve bunun sonuçlarına bakarız:

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

Posta	Idempname	BinaryValue
00-000	boş	boş
00-001	12PH	0x30302D30303131325048
00-002	boş	boş
00-003	boş	boş
00-004	10PH	0x30302D30303431305048
00-005	boş	boş

Null değerin birleştirilmesi null değer verdiğinden, yalnızca null olmadığı bir değer elde edersiniz. Daha sonra ikili sıralamadan (soldan sağa) yararlanabilir ve pencereli bir işlev içinde bu ikilinin maksimum değerini elde edebilirsiniz, yani bölüm: MAX(...) OVER(ORDER BY Postal).

Bu, tüm boş değerleri kaldırır (çünkü MAX aldırıyor NULL) ilk satırdan ayrı olarak, önceki boş olmayan bir değer olmadığından ve aşağıdaki gibi veri verdiğinden:

Posta	Idempname	MaxBinaryValue
00-000	boş	boş
00-001	12PH	0x30302D30303131325048
00-002	boş	0x30302D30303131325048
00-003	boş	0x30302D30303131325048
00-004	10PH	0x30302D30303431305048
00-005	boş	0x30302D30303431305048

Daha sonra, ilgilendiğiniz ikilinin bir kısmını (7-10 karakterleri) çıkarmak ve kullanarak varchar'a geri dönüştürmek sadece bir durumdur SUBSTRING ve CONVERT

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

1

İlişkili bir alt sorgu işe yarayabilir:

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

Sanırım bu yukarıda önerdiğime benzer, ancak çapraz uygulama daha hızlıdır. İlki null ise yine de null olabilir. Bu nedenle, her iki yöne de bakmalı ve hedef postaya göre farklılıklara göre sıralanmalıdır

vikjon0

Dizinlere bağlı olacağını tahmin ediyorum, ancak ilişkili alt sorguyu ve a'yı beklerdim. CROSS APPLY çok benzer planlar üretmek.

Richard Deeming

Muhtemelen, geçmişte kötü deneyimler yaşadım ama elbette SQL server gelişti . Eski iç içe geçmiş kodu çapraz uygulamaya yeniden yazmanın kesin bir başarı olduğu bir zaman vardı, ancak elbette iyi çalışan vakaları hiç göremezdim.

vikjon0

Eklemek AND K.IDemp IS NOT NULL boş değerleri ihmal etmek için alt sorguya.

Thorsten Kettner

@ ThorstenKettner Bu sorudan net değil, ama şunu varsayıyorum IDemp sütun içinde Customers tablo NOT NULL.

Richard Deeming

Op'nin sorgusunun sonucuna bakın. 00-001 posta kodu, biri IDEMP' 12PH ' diğeri IDEMP NULL olan iki satırla sonuçlanır. Bu nedenle, NULL dış birleşimden kaynaklanamaz, ancak müşteriler tablosunda bulunmalıdır.

Thorsten Kettner

@ ThorstenKettner İyi yakalama.

Richard Deeming

Posta kodları SQL Server'da son not null değeri ile boş değerlere doldurma

Soru

En iyi cevabı

Diğer dillerde

Bu sayfa diğer dillerde

Bu kategoride popüler

Popüler soruları bu kategoride