Wie man Zeilen von count in tsql nur erweitern

Question

Ich habe eine Tabelle, die eine Zahl und einen Bereichswert enthält. Zum Beispiel hat eine Spalte den Wert 40 und die andere Spalte hat den Wert 100, was bedeutet, dass der Bereich ab 40 100 Werte hat, die mit 139 enden, einschließlich der Zahl 40. Ich möchte eine tsql-Anweisung schreiben, die meine Daten in einzelne Zeilen auflöst.

Ich denke, ich brauche dafür einen CTE, weiß aber nicht, wie ich das erreichen kann.

Hinweis: Beim Erweitern erwarte ich 7 Millionen Zeilen.

Answer 1

Wenn Sie CTE wollen, hier ist ein Beispiel:

Ursprünglicher Einsatz:

insert into rangeTable (StartValue, RangeValue)
select 40,100
union all select 150,10
go

die Abfrage:

with r_CTE (startVal, rangeVal, generatedVal)
as
(
    select r.startValue, r.rangeValue, r.startValue
    from rangeTable r
    union all
    select r.startValue, r.rangeValue, generatedVal+1
    from rangeTable r
    inner join r_CTE rc 
        on r.startValue = rc.startVal
        and r.rangeValue = rc.rangeVal
        and r.startValue +  r.rangeValue > rc.generatedVal + 1
)
select * from r_CTE
order by startVal, rangeVal, generatedVal

Beachten Sie jedoch, dass die maximale Anzahl von Wiederholungen standardmäßig 100 beträgt. Sie können sie auf das Maximum von 32767 ändern, indem Sie

option (maxrecursion 32767)

oder bis zu keiner Grenze

option (maxrecursion 0)

Ver BOL für Details

Answer 2

Ich weiß nicht, wie dies mit gewöhnlichen Tabellenausdrücken erreicht werden kann, aber hier ist eine Lösung, die eine temporäre Tabelle verwendet:

   SET NOCOUNT ON

   DECLARE @MaxValue INT
   SELECT @MaxValue = max(StartValue + RangeValue) FROM MyTable

   DECLARE @Numbers table ( 
      Number INT IDENTITY(1,1) PRIMARY KEY 
   )

   INSERT @Numbers DEFAULT VALUES 

   WHILE COALESCE(SCOPE_IDENTITY(), 0) <= @MaxValue 
      INSERT @Numbers DEFAULT VALUES 

    SELECT n.Number
    FROM   @Numbers n
    WHERE  EXISTS(
        SELECT *
        FROM   MyTable t
        WHERE  n.Number BETWEEN t.StartValue AND t.StartValue + t.RangeValue - 1
    )

    SET NOCOUNT OFF

Könnte optimiert werden, wenn die Tabelle Numbers eine normale Tabelle wäre. So müssten Sie nicht bei jedem Aufruf die temporäre Tabelle füllen.

Answer 3

Könnten Sie diesen Ansatz versuchen:

create function [dbo].[fRange](@a int, @b int)
    returns @ret table (val int)
as 
begin
    declare @val int
    declare @end int
    set @val = @a
    set @end = @a + @b
    while @val < @end
    begin
        insert into @ret(val)
        select @val
        set @val = @val+1
    end
return
end

go

declare @ranges table(start int, noOfEntries int)

insert into @ranges (start, noOfEntries)
select 40,100
union all select 150, 10

select * from @ranges r
    cross apply dbo.fRange(start,noOfEntries ) fr

nicht die schnellste, aber sollte funktionieren

Answer 4

Ich würde etwas anderes machen als splattne...

SET NOCOUNT ON

DECLARE @MaxValue INT
DECLARE @Numbers table (
    Number INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
 )

SELECT @MaxValue = max(RangeValue) FROM MyTable
INSERT @Numbers DEFAULT VALUES

WHILE COALESCE(SCOPE_IDENTITY(), 0) <= @MaxValue
    INSERT @Numbers DEFAULT VALUES

SELECT
    t.startValue + n.Number
FROM
    MyTable t
INNER JOIN
    @Numbers n
        ON n.Number < t.RangeValue

SET NOCOUNT OFF

Dadurch wird die Anzahl der Zeilen, die Sie in die Tabellenvariable einfügen müssen, minimiert, und Sie können dann einen Join verwenden, um eine Tabelle mit der anderen zu "multiplizieren"...

Aufgrund der Art der Abfrage muss die Tabelle der Quelltabelle nicht indiziert werden, aber die Tabelle "Zahlen" sollte einen Index (oder Primärschlüssel) haben. Geclusterte Indizes beziehen sich darauf, wie sie auf der Festplatte gespeichert werden, daher sehe ich nicht, dass CLUSTERED hier relevant ist, aber ich habe es drin gelassen, da ich gerade von Splattne kopiert habe.

(Große Verbindungen wie diese sind zwar langsam, aber immer noch viel schneller als Millionen von Einfügungen).