主页 / dba / 问题 / 250573
Accepted
SQLserving
Asked: 2019-10-09 09:37:18 +0800 CST

根据 ID 将英里数限制到千分之一

  • 772

我在 sql server 2014 中有一个这样的表： 

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

我想要做的是将英里分成每个 ID 的千分之一，如下所示： 

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

关于如何去做的任何想法？我试图远离 Cursors，除非这是唯一的方法。

我已经能够将这个查询放在一起，但不确定如何将它与我的 ID 和英里限制结合起来，以便它在没有声明的 From 和 To 变量的情况下运行整个表： 

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 个回答

  1. 如果您不想使用递归 CTE，您可以使用以下查询示例：

    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;

    对于一个 id，这应该可以工作到6M英里。

    spt 值表可以用某种数字表代替。

    数据库<>小提琴

    • 5
  2. Best Answer

    您只需要在锚点中引用初始起始值和结束值。

    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. 这是一种方法。这只是一种方式。有多种其他方法可以做到这一点，每种方法都会涉及一些循环和权衡，但一旦准备好这种方法，就不需要其他循环了。

    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

    其他可能的方法包括一个真实数字表（值从 0 到任何值）和额外的计算以确定顶部截止值，您的 CTE 版本嵌入到表值函数中，如下所示：

    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)

    和许多其他人

    • 3

相关问题