Soci (Soczó Zsolt) szakmai blogja

2015.02.13.

SQL fejtörő 9.

Filed under: Adatbázisok,SQL Server,Szakmai élet,Teaser — Soczó Zsolt @ 09:00

Van egy táblánk, amiben számokat tárolunk. A számok nem teljesen folytonosak, az összefüggő részeket kell azonosítani.

use tempdb;

IF OBJECT_ID('dbo.Tabla', 'U') IS NOT NULL DROP TABLE dbo.Tabla;
GO
CREATE TABLE dbo.Tabla
(
	szam INT NOT NULL CONSTRAINT PK_Tabla PRIMARY KEY
);
INSERT INTO dbo.Tabla(szam)
VALUES(12),(13),(111),(112),(113),(237),(238),(340),(350),(351);
GO

select * from Tabla;

-- Ezt szeretnénk kinyerni, az összefüggő tartományokat
--tartomanyeleje	tartomanyvege
--12				13
--111				113
--237				238
--340				340
--350				351

A megfejtéseket szokás szerint kommentben várom, amelyeket 2 nap múlva engedek ki.

5 Comments

  1. IF OBJECT_ID(N’dbo.hatarok’, N’IF’) IS NOT NULL
    DROP FUNCTION dbo.hatarok;
    GO
    CREATE FUNCTION dbo.hatarok
    ( @Min int )
    RETURNS table
    AS
    RETURN (
    WITH tartomany(szam_elozo)
    AS
    (
    SELECT MIN(szam) FROM Tabla
    WHERE szam > @Min
    UNION ALL
    SELECT szam FROM Tabla
    INNER JOIN tartomany ON tartomany.szam_elozo + 1 = szam
    )
    SELECT
    MIN(szam_elozo) elso,
    MAX(szam_elozo) utolso,
    (SELECT MIN(szam) FROM Tabla WHERE szam > MAX(szam_elozo)) kovetkezo
    FROM tartomany
    );
    GO

    WITH c(elso, utolso, kovetkezo)
    AS
    (
    SELECT h.elso, h.utolso, h.kovetkezo FROM hatarok(0) h
    UNION ALL
    SELECT h.elso, h.utolso, h.kovetkezo FROM Tabla
    INNER JOIN c ON Tabla.szam = c.kovetkezo
    CROSS APPLY hatarok(Tabla.szam – 1) h
    )
    SELECT elso, utolso FROM c

    Comment by Rici — 2015.02.13. @ 14:23

  2. ;with Tartomany as
    (
    select
    Gyujto = (row_number() over (order by szam) – szam),
    szam
    from
    Tabla
    )
    select
    TartomanyEleje = min(szam)
    ,TartomanyVege = max(szam)
    from
    Tartomany
    group by
    Gyujto
    order by
    TartomanyEleje

    Comment by Atcom — 2015.02.13. @ 15:39

  3. … ket kulon query-vel sikerult csak, raadasul azok is csunyak :)

    –tartKezdete
    SELECT DISTINCT szam AS tartKezdete FROM dbo.Tabla
    EXCEPT
    SELECT
    sq.val + 1
    FROM
    (
    SELECT
    (
    SELECT TOP 1 t.szam FROM dbo.Tabla t WHERE t.szam = tab.szam – 1
    ) val
    FROM dbo.Tabla tab
    ) sq

    –tartVege
    SELECT DISTINCT szam AS tartVege FROM dbo.Tabla
    EXCEPT
    SELECT
    sq.val – 1
    FROM
    (
    SELECT
    (
    SELECT TOP 1 t.szam FROM dbo.Tabla t WHERE t.szam = tab.szam + 1
    ) val
    FROM dbo.Tabla tab
    ) sq

    Comment by Attila — 2015.02.17. @ 17:06

  4. select b.szam, e.szam
    from
    (select ROW_NUMBER() OVER(ORDER BY tb1.szam) AS ord, tb1.szam from tabla tb1
    left outer join tabla tb2 on (tb1.szam = tb2.szam + 1)
    where tb2.szam is null) as b
    inner join
    (select ROW_NUMBER() OVER(ORDER BY te1.szam) AS ord, te1.szam from tabla te1
    left outer join tabla te2 on (te1.szam + 1 = te2.szam)
    where te2.szam is null) as e on (b.ord = e.ord)

    Comment by Pócza Krisztián — 2015.02.17. @ 22:59

  5. WITH Neighbours AS
    ( SELECT
    Previous.szam AS Previous, /* elozo elem azonositoja (ha van)*/
    t.szam AS Actual, /* aktualis elem azonositoja*/
    Next.szam AS Next /* kovetkezo elem azonositoja (ha van)*/
    FROM Tabla t
    LEFT JOIN Tabla Next ON Next.szam = t.szam + 1
    LEFT JOIN Tabla Previous ON Previous.szam = t.szam – 1
    )
    SELECT
    n1.Actual AS tartomanyeleje,
    (SELECT TOP 1 n2.Actual
    FROM Neighbours n2
    WHERE n2.Actual >= n1.Actual AND n2.Next IS NULL
    ORDER BY n2.Actual) AS tartomanyvege
    FROM Neighbours n1
    WHERE n1.Previous IS NULL
    ORDER BY n1.Actual;

    Comment by Nagypál Gábor — 2015.02.20. @ 02:40

RSS feed for comments on this post.

Sorry, the comment form is closed at this time.

Powered by WordPress