有一表T:
编号 姓名
1 AA
2 BB
3 CC
...
该表的总人数n=select count(编号) from T
有一个给定的字符假设='CZ2008'
有一个给定的分段数字=5
我要的效果:
CZ20081~CZ20085
CZ20086~CZ200810
...
.......~CZ2008n 说明:[给定的字符]和[分段数字]是变量
编号 姓名
1 AA
2 BB
3 CC
...
该表的总人数n=select count(编号) from T
有一个给定的字符假设='CZ2008'
有一个给定的分段数字=5
我要的效果:
CZ20081~CZ20085
CZ20086~CZ200810
...
.......~CZ2008n 说明:[给定的字符]和[分段数字]是变量
--> 测试数据: @s
declare @s table (编号 int,姓名 varchar(2))
insert into @s
select 1,'AA' union all
select 2,'BB' union all
select 3,'CC' union all
select 4,'DD' union all
select 5,'EE' union all
select 6,'FF' union all
select 7,'GG' union all
select 8,'HH'declare @t table(a varchar(50))
declare @str varchar(50),@i int,@n int,@c int
set @str='CZ2008'
set @i=5
set @c=0
select @n=count(编号) from @s
while(@n/@i>=@c)
begin
insert into @t select 'CZ2008'+ltrim(@i*@c+1)+'~'+'CZ2008'+ltrim((@c+1)*@i)
set @c=@c+1
end
select * from @t
--修正下上面的错误:
--> 测试数据: @s
declare @s table (编号 int,姓名 varchar(2))
insert into @s
select 1,'AA' union all
select 2,'BB' union all
select 3,'CC' union all
select 4,'DD' union all
select 5,'EE' union all
select 6,'FF' union all
select 7,'GG' union all
select 8,'HH' declare @t table(a varchar(50))
declare @str varchar(50),@i int,@n int,@c int
set @str='CZ2008'
set @i=5
set @c=0
select @n=count(编号) from @s
while(@n/@i>=@c)
begin
insert into @t select 'CZ2008'+ltrim(@i*@c+1)+'~'+'CZ2008'+ltrim( case when (@c+1)*@i>@n then @n else (@c+1)*@i end)
set @c=@c+1
end
select * from @t
--修正下上面的错误:
--> 测试数据: @s
declare @s table (编号 int,姓名 varchar(2))
insert into @s
select 1,'AA' union all
select 2,'BB' union all
select 3,'CC' union all
select 4,'DD' union all
select 5,'EE' union all
select 6,'FF' union all
select 7,'GG' union all
select 8,'HH' declare @t table(a varchar(50))
declare @str varchar(50),@i int,@n int,@c int
set @str='CZ2008'
set @i=5
set @c=0
select @n=count(编号) from @s
while(@n/@i>=@c)
begin
insert into @t select @str+ltrim(@i*@c+1)+'~'+@str+ltrim( case when (@c+1)*@i>@n then @n else (@c+1)*@i end)
set @c=@c+1
end
select * from @t
insert into @s
select 1,'AA' union all
select 2,'BB' union all
select 3,'CC' union all
select 4,'DD' union all
select 5,'EE' union all
select 6,'FF' union all
select 7,'GG' union all
select 8,'HH'declare @t table(a varchar(50))
declare @str varchar(50),@i int,@n int,@c int
set @str='CZ2008'
set @i=5select @str + cast(n.cnt1 as varchar) col1, @str + cast(@i as varchar) col2 from @s m,
(select count(*)/@i cnt1, count(*)%@i cnt2 from @s) n where m.编号 <= n.cnt1
union all
select @str + cast(n.cnt1 * @i + 1 as varchar) col1, @str + cast(n.cnt1 * @i + n.cnt2 as varchar) col2 from @s m,
(select count(*)/@i cnt1, count(*)%@i cnt2 from @s) n where m.编号 <= n.cnt1/*
col1 col2
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
CZ20081 CZ20085
CZ20086 CZ20088(所影响的行数为 2 行)
*/
3楼的SQL:当@i=奇数的时候正常,当当@i=偶数的时候不正常(最后一行显示不正常)
DECLARE @flag int
DECLARE @T table(id int, [name] nvarchar(20))
INSERT INTO @t
SELECT 1,'AA'
UNION ALL SELECT 2,'BB'
UNION ALL SELECT 3, 'CC'
UNION ALL SELECT 4,'DD'
UNION ALL SELECT 5,'EE'
UNION ALL SELECT 6,'FF'
SET @px ='CZ2008'
SET @flag = 5SELECT SEQ FROM
(
SELECT A.NUM + B.NUM as NUM,
@px + CONVERT(varchar(10),A.NUM + B.NUM ) +'~' + @px + CONVERT(varchar(10),A.NUM + B.NUM + (@flag-1)) as SEQ
FROM (
SELECT 0 AS NUM
UNION ALL
SELECT 1 AS NUM
UNION ALL
SELECT 2 AS NUM
UNION ALL
SELECT 3 AS NUM
UNION ALL
SELECT 4 AS NUM
UNION ALL
SELECT 5 AS NUM
UNION ALL
SELECT 6 AS NUM
UNION ALL
SELECT 7 AS NUM
UNION ALL
SELECT 8 AS NUM
UNION ALL
SELECT 9 AS NUM
)A,
(
SELECT 0 AS NUM
UNION ALL
SELECT 10 AS NUM
UNION ALL
SELECT 20 AS NUM
UNION ALL
SELECT 30 AS NUM
UNION ALL
SELECT 40 AS NUM
UNION ALL
SELECT 50 AS NUM
UNION ALL
SELECT 60 AS NUM
UNION ALL
SELECT 70 AS NUM
UNION ALL
SELECT 80 AS NUM
UNION ALL
SELECT 90 AS NUM
)B
WHERE A.NUM + B.NUM BETWEEN 1 AND (SELECT COUNT(1) FROM @T)
) D
WHERE (NUM -1) %@flag =0/*
SEQ
-------------------------------------------------------------
CZ20081~CZ20085
CZ20086~CZ200810(2 row(s) affected)*/
insert into @s
select 1,'AA' union all
select 2,'BB' union all
select 3,'CC' union all
select 4,'DD' union all
select 5,'EE' union all
select 6,'FF' union all
select 7,'GG' union all
select 8,'HH' union all
select 9,'AA' union all
select 10,'BB' union all
select 11,'CC' union all
select 12,'DD' union all
select 13,'EE' union all
select 14,'FF' union all
select 15,'GG' union all
select 16,'HH'
declare @t table(a varchar(50))
declare @str varchar(50),@i int,@n int,@c int
set @str='CZ2008'
set @i=5select @str + cast((m.编号-1)*@i + 1 as varchar) col1, @str + cast(m.编号 * @i as varchar) col2 from @s m,
(select count(*)/@i cnt1, count(*)%@i cnt2 from @s) n where m.编号 <= n.cnt1
union all
select @str + cast(n.cnt1 * @i + 1 as varchar) col1, @str + cast(n.cnt1 * @i + n.cnt2 as varchar) col2 from @s m,
(select count(*)/@i cnt1, count(*)%@i cnt2 from @s) n where m.编号 = n.cnt1/*
col1 col2
---------- ---------
CZ20081 CZ20085
CZ20086 CZ200810
CZ200811 CZ200815
CZ200816 CZ200816(所影响的行数为 4 行)
*/
insert into @s
select 1,'AA' union all
select 2,'BB' union all
select 3,'CC' union all
select 4,'DD' union all
select 5,'EE' union all
select 6,'FF' union all
select 7,'GG' union all
select 8,'HH'
declare @str varchar(10) ,@int int
set @str='CZ2008' set @int=5
select distinct rowid-rowid%@int+1,@str+rtrim(convert(char(10),rowid-rowid%@int+1)) +'~'+
@str+rtrim(convert(char(10),rowid-rowid%@int+@int)) from
(select row_number() over(order by 编号)rowid from @s )b
where rowid-rowid%@int+1<=rowid
order by rowid-rowid%@int+1
10楼的SQL:当@i=奇数的时候正常,当当@i=偶数的时候不正常(最后一行显示是多余的)
--改正上面的错误:
--修正下上面的错误:
--> 测试数据: @s
declare @s table (编号 int,姓名 varchar(2))
insert into @s
select 1,'AA' union all
select 2,'BB' union all
select 3,'CC' union all
select 4,'DD' union all
select 5,'EE' union all
select 6,'FF' union all
select 7,'GG' union all
select 8,'HH' union all
select 9,'HH' union all
select 10,'HH' union all
select 11,'HH' union all
select 12,'HH' declare @t table(a varchar(50))
declare @str varchar(50),@i int,@n int,@c int
set @str='CZ2008'
set @i=6
set @c=0
select @n=count(编号) from @s
while(@i*@c<@n)
begin
insert into @t select @str+ltrim(@i*@c+1)+'~'+@str+ltrim( case when (@c+1)*@i>=@n then @n else (@c+1)*@i end)
set @c=@c+1
end
select * from @t
insert into @s
select 1,'AA' union all
select 2,'BB' union all
select 3,'CC' union all
select 4,'DD' union all
select 5,'EE' union all
select 6,'FF' union all
select 7,'GG' union all
select 8,'HH' declare @str varchar(50),@n int
set @str='CZ2008'
set @n=5--> 如果这么简单,一个汇总就出来了,没必要那么复杂:select @str+ltrim(min(编号))+'~'+@str+ltrim(max(编号)) as result from @s group by (编号-1)/@n/*
result
-----------------------------------------------------------------------------------------------------------------------------
CZ20081~CZ20085
CZ20086~CZ20088
*/
insert into @s
select 1,'AA' union all
select 2,'BB' union all
select 3,'CC' union all
select 4,'DD' union all
select 5,'EE' union all
select 6,'FF' union all
select 7,'GG' union all
select 8,'HH' union all
select 9,'AA' union all
select 10,'BB' union all
select 11,'CC' union all
select 12,'DD' union all
select 13,'EE' union all
select 14,'FF' union all
select 15,'GG' union all
select 16,'HH'
declare @t table(a varchar(50))
declare @str varchar(50),@i int,@n int,@c int
set @str='CZ2008'
set @i=5select * from
(
select @str + cast((m.编号-1)*@i + 1 as varchar) col1, @str + cast(m.编号 * @i as varchar) col2 from @s m,
(select count(*)/@i cnt1, count(*)%@i cnt2 from @s) n where m.编号 <= n.cnt1
union all
select case when n.cnt2 <> 0 then @str + cast(n.cnt1 * @i + 1 as varchar) else '' end col1,
case when n.cnt2 <> 0 then @str + cast(n.cnt1 * @i + n.cnt2 as varchar) else '' end col2
from @s m,(select count(*)/@i cnt1, count(*)%@i cnt2 from @s) n where m.编号 = n.cnt1
) t
where col1 <> ''/*
col1 col2
---------- ---------
CZ20081 CZ20085
CZ20086 CZ200810
CZ200811 CZ200815
CZ200816 CZ200816(所影响的行数为 4 行)
*/set @i=4
select * from
(
select @str + cast((m.编号-1)*@i + 1 as varchar) col1, @str + cast(m.编号 * @i as varchar) col2 from @s m,
(select count(*)/@i cnt1, count(*)%@i cnt2 from @s) n where m.编号 <= n.cnt1
union all
select case when n.cnt2 <> 0 then @str + cast(n.cnt1 * @i + 1 as varchar) else '' end col1,
case when n.cnt2 <> 0 then @str + cast(n.cnt1 * @i + n.cnt2 as varchar) else '' end col2
from @s m,(select count(*)/@i cnt1, count(*)%@i cnt2 from @s) n where m.编号 = n.cnt1
) t
where col1 <> ''/*
col1 col2
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
CZ20081 CZ20084
CZ20085 CZ20088
CZ20089 CZ200812
CZ200813 CZ200816(所影响的行数为 4 行)
*/
insert into @s
select 1,'AA' union all
select 2,'BB' union all
select 3,'CC' union all
select 4,'DD' union all
select 5,'EE' union all
select 6,'FF' union all
select 7,'GG' union all
select 8,'HH'declare @t table(a varchar(50))
declare @str varchar(50),@i int,@n int,@c int
set @str='CZ2008'
set @i=5select @str + cast(n.cnt1 as varchar) col1, @str + cast(@i as varchar) col2 from @s m,
(select count(*)/@i cnt1, count(*)%@i cnt2 from @s) n where m.编号 <= n.cnt1
union all
select @str + cast(n.cnt1 * @i + 1 as varchar) col1, @str + cast(n.cnt1 * @i + n.cnt2 as varchar) col2 from @s m,
(select count(*)/@i cnt1, count(*)%@i cnt2 from @s) n where m.编号 <= n.cnt1/*
col1 col2
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
CZ20081 CZ20085
CZ20086 CZ20088(所影响的行数为 2 行)
*/
insert into @s
select 1,'AA' union all
select 2,'BB' union all
select 3,'CC' union all
select 4,'DD' union all
select 5,'EE' union all
select 6,'FF' union all
select 7,'GG' union all
select 8,'HH' declare @str varchar(50),@n int
set @str='CZ2008'
set @n=5--> 如果这么简单,一个汇总就出来了,没必要那么复杂:select @str+ltrim(min(编号))+'~'+@str+ltrim(max(编号)) as result from @s group by (编号-1)/@n/*
result
-----------------------------------------------------------------------------------------------------------------------------
CZ20081~CZ20085
CZ20086~CZ20088
*/
insert into @s
select 1,'AA' union all
select 3,'BB' union all
select 4,'CC' union all
select 5,'DD' union all
select 6,'EE' union all
select 7,'FF' union all
select 8,'GG' union all
select 9,'HH'declare @str varchar(50),@n int
set @str='CZ2008'
set @n=5--> 因为编号往往不是连续的——那就让它连续——生成id:select @str+ltrim(min(id))+'~'+@str+ltrim(max(id)) as result from (select id=(select count(1) from @s where 编号<=s.编号) from @s s) t group by (id-1)/@n/*
result
---------------
CZ20081~CZ20085
CZ20086~CZ20088
*/