5个记录的SQL更新段
问题描述:
我有一个名称表,我想生成另一列“组”,每5个记录增加1。以下是所需输出的示例。5个记录的SQL更新段
Name Group
Joe 1
Frank 1
Susan 1
Tom 1
Kim 1
Mike 2
John 2
Henry 2
Rick 2
Quinn 2
答
创建CTE与行号将帮助
;WITH cte AS
(
SELECT
Name,
ROW_NUMBER() OVER (ORDER BY Name) AS RowNum
FROM YourTable
)
SELECT
Name,
(RowNum - 1)/5 + 1 AS [Group]
FROM cte
答
你可以尝试这样的事情
select a.name,
case when (a.myrow%5=0) then 5 else a.myrow%5 end as group
from
(select name,row_number() over (order by name) as myrow from YourTable)a
答
另一种方法
Create table #tmpNames
(
Name1 varchar(100)
)
Insert INTO #tmpNames
(Name1)
VALUES
('Joe'),
('Frank'),
('Susan'),
('Tom'),
('Kim'),
('Mike'),
('John'),
('Henry'),
('Rick'),
('Quinn')
Select Name1
,ROUND(rowCnt/6, 0, 1) + 1 as 'grp'
From (
Select
Name1
,ROW_NUMBER() over (order by Name1) as 'rowCnt'
From #tmpNames
) a
你使用什么版本的SQL是?以及如何命令这些人为了生成一个分组(需要一个我相信的命令)? –
我正在使用MS SQL Server 2014.它们可以按字母顺序排列。 –