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.
Could you hire me? Contact me if you like what I’ve done in this article and think I can create value for your company with my skills.
LEAVE A COMMENT
5 COMMENTS
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
;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
… 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
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)
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;