SQL类别表值
大家好我有与我的SQL表tbl_myTable和价值观像SQL类别表值
rollid | Name | fileName
------------------
1 | aaaa |
2 | bbbb |
3 | cccc |
4 | dddd |
5 | eeee |
6 | ffff |
7 | gggg |
8 | hhhh |
9 | iiii |
10 | jjjj |
11 | kkkk |
12 | llll |
一个问题，我需要归类这些值5个项目的块，然后我需要先更新fileName场每个块如book1，book2，book3 ...等。即只有5个记录存在于每本书和我的预期输出的样子
rollid | Name | fileName
------------------
1 | aaaa | book1
2 | bbbb | book1
3 | cccc | book1
4 | dddd | book1
5 | eeee | book1
6 | ffff | book2
7 | gggg | book2
8 | hhhh | book2
9 | iiii | book2
10 | jjjj | book2
11 | kkkk | book3
12 | llll | book3
update tbl_myTable set fileName ='book1' where rollid < 6
update tbl_myTable set fileName ='book2' where rollid between 7 and 10
update tbl_myTable set fileName ='book3' where rollid >10
尝试此查询：
update myTable
set filename = 'book' + CAST((id/6) + 1 as varchar)
更新
update a
set a.filename = 'book' + CAST((b.rownumber/6) + 1 as varchar)
from myTable a
left join
(
select id , ROW_NUMBER() OVER(order by id) as rownumber
from myTable
) b
on a.id = b.id
我不知道如何对行进行排序。但我认为你可以改变order by id以适合你的模式。
但在我的表中有35000行和卷ID是不是一个订单，即一些卷ID缺失。 – 2012-03-15 05:23:37
@RER我看到了，如果是这样的话，我认为你需要使用ROW_NUMBER（）来将一个订单列一般化，然后加入你的表来使用上面的方法。我试着给你写一个样本。 – shenhengbin 2012-03-15 05:29:47
谢谢@沉恒斌我也一样。 – 2012-03-15 05:35:58
试试用下面的查询：
UPDATE tbl_myTable
SET fileName='book' + (1 + (CAST((CAST(rollid AS INT) - 1)/5) AS INT))
如果需要立足于rollId或名称的“通讯录群组”你没有指定。我假设这是你说rollId可能会跳过一些值的名字。如果没有，你可以在ROW_NUMBER（）窗口功能容易掉出并改正：
with tbl_myTable_mimic as (
select 1 as rollid, 'aaaa' as name, '' as filename
union select 2 as rollid, 'bbbb' as name, '' as filename
union select 3 as rollid, 'cccc' as name, '' as filename
union select 4 as rollid, 'dddd' as name, '' as filename
union select 5 as rollid, 'eeee' as name, '' as filename
union select 6 as rollid, 'ffff' as name, '' as filename
union select 7 as rollid, 'gggg' as name, '' as filename
union select 10 as rollid, 'hhhh' as name, '' as filename
union select 11 as rollid, 'iiii' as name, '' as filename
union select 12 as rollid, 'jjjj' as name, '' as filename
union select 13 as rollid, 'kkkk' as name, '' as filename
union select 15 as rollid, 'llll' as name, '' as filename
), j as (
select
*
, row_number() over (partition by 1 order by name asc, rollid asc) as rn
from tbl_myTable_mimic
), i as (
select
*
, 'book' + cast(((rn-1)/5)+1 as varchar(16)) as newFileName
from
j
)
select * from i
如果你需要更新现有的表，只需用tbl_myTable中的'update tbl_myTable替换掉最后一条select语句就可以加入我...' – 2012-03-15 05:44:11
谢谢@Giscard让我检查 – 2012-03-15 05:55:20
为什么PARTITION BY 1？ PARTITION BY是可选的。 – 2012-03-15 12:32:12
declare @T table
(
rollid int primary key,
Name varchar(10),
fileName varchar(10)
)
insert into @T(rollid, Name) values
(1, 'aaaa'),
(2, 'bbbb'),
(3, 'cccc'),
(4, 'dddd'),
(5, 'eeee'),
(6, 'ffff'),
(7, 'gggg'),
(8, 'hhhh'),
(9, 'iiii'),
(10, 'jjjj'),
(11, 'kkkk'),
(12, 'llll')
update T
set filename = 'book'+cast((rn + 4)/5 as varchar(10))
from
(
select filename,
row_number() over(order by rollid) as rn
from @T
) as T
搭上这一个无声的downvote所以我猜需要在我的部分的解释。
我用row_number在派生表来生成一个完整的系列，可以用来与5将在SQL Server计算书号它是可以运行对派生表的更新语句整数其中意味着不需要将结果返回到表格。
那些你们谁还有疑问可以试一下上SE-Data
但在我的桌子上有35000行和卷ID是不是即几个滚ID丢失订单。 – 2012-03-15 05:23:32
+1，虽然我认为OP想要更通用的解决方案。 – 2012-03-15 05:24:04