Início / dba / Perguntas / 250573
Accepted
SQLserving
Asked: 2019-10-09 09:37:18 +0800 CST

Limites de milhas divididos ao milésimo com base no ID

  • 772

Eu tenho uma tabela assim no sql server 2014: 

ID | Start_mile|End_mile  
1  |5.23       |7.464  
2  |2.333      |6.124

O que eu quero fazer é dividir as milhas em milésimos por ID assim: 

ID |start_mile|end_mile  
1  |5.230     |5.231  
1  |5.231     |5.232  
1  |5.232     |5.233  
....  
1  |7.463     |7.464  
...

Alguma ideia de como fazer isso? Estou tentando ficar longe dos cursores, a menos que essa seja a única maneira de fazer isso.

Consegui reunir essa consulta, mas não tenho certeza de como incorporá-la ao ID e aos limites de milhas que tenho para que ela seja executada em toda a tabela sem as variáveis ​​De e Para declaradas: 

DECLARE @from decimal(15, 3) = 0.980;  
DECLARE @to decimal(15, 3) = 1.024;  

;WITH cte AS  
    (SELECT @from AS Value  
     UNION ALL  
     SELECT CONVERT(decimal(15, 3), Value + 0.001)  
     FROM cte  
     WHERE Value < @to)  
SELECT *  
FROM cte  
option (maxrecursion 0)
sql-server

3 respostas

  1. Se você não quiser usar um CTE recursivo, use o exemplo de consulta abaixo:

    SELECT m.ID, 
       a.rn as start_mile, 
       a.rn+ CONVERT(decimal(15, 3),0.001) as end_mile
FROM dbo.Miles m
CROSS APPLY
(
    SELECT TOP(CONVERT(int,(( end_mile -  Start_mile)* 1000)))
    CONVERT(decimal(15, 3),(Start_mile + (CONVERT(decimal(15, 3),ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1) / 1000))) as rn
    FROM master..spt_values spt1
    CROSS APPLY master..spt_values spt2
) as a;

    Isso deve funcionar até 6 milhões de milhas para um id.

    A tabela de valores spt pode ser substituída por uma tabela de números de algum tipo.

    DB<>Fiddle

    • 5
  2. Best Answer

    Você só precisa referenciar os valores iniciais e finais na âncora.

    CREATE TABLE #tmp (
    ID INT NOT NULL PRIMARY KEY
    ,[Start] DECIMAL(15, 3)
    ,[To] DECIMAL(15, 3)
    )
GO
INSERT INTO #tmp (
    ID
    ,[Start]
    ,[To]
    )
VALUES (
    1
    ,5.00
    ,5.99
    )
    ,(
    2
    ,10.00
    ,10.4
    );
WITH cte
AS (
    SELECT ID
        ,[Start]
        ,[To]
        ,CAST([Start] AS DECIMAL(15, 3)) AS [Value]
    FROM #tmp

    UNION ALL

    SELECT ID
        ,[Start]
        ,[To]
        ,CAST(([Value] + 0.001) AS DECIMAL(15, 3))
    FROM cte
    WHERE CAST(([Value] + 0.001) AS DECIMAL(15, 3)) < [To]
    )
SELECT *
FROM cte
ORDER BY ID
    ,Start
    ,Value
OPTION (MAXRECURSION 0)
    • 4

  3. Esta é uma maneira de fazê-lo. Esta é apenas uma maneira. Existem várias outras maneiras de fazer isso, e todas as maneiras envolvem alguns loops e compensações, mas uma vez que essa maneira é preparada, nenhum outro loop é necessário.

    CREATE TABLE #Intervals (
    i decimal(7,3) not null primary key
    )

INSERT INTO #Intervals Values (0.001), (0.002), (0.003), (0.004), (0.005), (0.006),
    (0.007), (0.008), (0.009), (0.01), (0.011), (0.012), (0.013), (0.014), (0.015), (0.016)

DECLARE @ct int = 18        -- Up to 4100 miles. Reduce iterations if you don't need this much.
DECLARE @mx decimal(7,3)

SELECT @mx = Max(i) from #Intervals

WHILE @ct > 0    -- loop for loading the #Intervals table
BEGIN
    INSERT INTO #Intervals SELECT i + @mx FROM #intervals
    SELECT @mx = Max(i) from #Intervals
    SET @ct -=1
END

INSERT INTO #Intervals Values(0.000)    -- just in case milepost zero

CREATE TABLE #miles (
    ID int not null,
    Start_Mile decimal(7,3) Not Null,
    End_Mile decimal(7,3) Not Null
    )

CREATE TABLE #NewMiles (
    ID int not null,
    Start_Mile decimal(7,3) Not Null,
    End_Mile decimal(7,3) Not Null
    )

INSERT INTO #miles Values
    (1, 5.23, 7.464),
    (2, 2.333, 6.124)

INSERT INTO #NewMiles
SELECT m.id, i.i, i.i + 0.001
FROM #miles m
INNER JOIN #Intervals i
    ON i.i >= m.Start_Mile and i.i < m.End_Mile

SELECT *
FROM #NewMiles
ORDER BY id, Start_Mile


DROP TABLE #Intervals
DROP TABLE #miles
DROP TABLE #NewMiles

    Outros métodos possíveis incluem uma tabela de números verdadeiros (valores de 0 a qualquer um) com cálculos adicionais para determinar o corte superior, uma versão do seu CTE incorporado em uma função com valor de tabela como esta:

    CREATE FUNCTION dbo.GetMileThousandths (
    @ID int,
    @from decimal(7, 3),
    @to decimal(7, 3)
)
RETURNS TABLE
AS
RETURN (
    WITH cte AS (
        SELECT @id as id, 
            @from AS Value  
        UNION ALL  
        SELECT id, 
            Cast(Value + 0.001 as decimal(7,3))
        FROM cte  
        WHERE Value < @to
        )
    SELECT *
    FROM cte
    )
GO

SELECT id, Value, Value+0.001
FROM dbo.GetMileThousandths(1, 1.111, 2.222)
    OPTION (maxRecursion 0)

    e muitos outros

    • 3

relate perguntas