时间戳

首页 > 解决方案 > 如何通过 ID 值增加列组？

sql - 如何通过 ID 值增加列组？

问题描述

我有 3 列：ID（Varchar 类型）、Age（INT 类型）和金额（$）。真正的表是 100K 行：该表列出了客户在每个年龄段收到的每个金额。

ID 年龄 数量
A01 1 10
A01 2 11
A01 3 12
A02 90 50
A02 100 51
A02 110 52

我需要将每个客户的每个年龄增加到 120 并得到类似的东西

ID 年龄 数量
A01 1 10
A01 2 11
A01 3 12
A01 4 13
A01 n+1 ...
A01 120 1500
A02 90 50
A02 100 51
A02 110 52
A02 111 53
A02 n+1 ...
A02 120 600

这是我第一次做循环，经过几次尝试，这是我能写的最好的，但它不起作用。

  SELECT DISTINCT [id], [Age], ROW_NUMBER() OVER (PARTITION BY [id] ORDER BY 
     [Age])AS Rnk INTO tableRanked FROM  MyTable
        
           DECLARE @Agefirst AS INT 
           DECLARE @Agelast AS INT 
           DECLARE @AgeCurent AS INT 
           DECLARE @id as nvarchar(max)
               
           SET @Agelast = 120
           SET @id = 0
           SET @AgeCurent = 0
                
           WHILE(@AgeCurent <= @Agelast)
           BEGIN
           SET @AgeCurent = @AgeCurent+1                
           INSERT INTO tableRanked ([Id],[Age])
           SELECT [Rnk], [Id], [Age] FROM  tableRanked 
            SET @id = @id+1

            END

太感谢了！

标签： sqlsql-serversql-server-2012

解决方案

您可以使用numbers 函数或 tally table来代替循环（或递归 CTE，这也是一个循环）。Numbers 函数使用的资源很少，而且速度非常快。从您的代码看来，每个 ID 应该跨越 1 到 120 的年龄范围。像这样的东西

;with unq_id(id) as (
    select distinct id
    from MyTable)
insert into tableRanked
select ui.id as ID, fn.n as Age, isnull(m.Amount, 0) Amount
from unq_id ui
     cross join dbo.fnTally(1, 120) fn
     left join MyTable m on ui.id=m.Id
                            and fn.n=m.Age
order by ui.id, fn.n;

推荐阅读