有一表:
编号 考号 姓名
1 cc01 AA
1 cc02 BB
1 cc03 CC
1 cc04 DD
1 cc05 EE
...
我的目的就是根据每个考室的人数(用户输入)自动生成一个表,如按一个考室3个考生的效果如下:
考室 考号区间
1 cc01-cc03
2 cc04-cc05
--------------------
create table tb(编号 int,考号 char(4),姓名 varchar(10)) insert tb select 1 , 'cc01' , 'AA' insert tb select 1 , 'cc02' , 'BB' insert tb select 1 , 'cc03' , 'CC' insert tb select 1 , 'cc04' , 'DD' insert tb select 1 , 'cc05' , 'EE' go select ceiling(cnt/3.0) row,* into t from ( select *,cnt=(select count(*) from tb where 考号<=a.考号) from tb a ) tt go create function dbo.f_str(@row int) returns varchar(100) as begin declare @re varchar(100) set @re='' select top 1 @re=考号 from t where row=@row order by 考号 select top 1 @re=@re+'-'+考号 from t where row=@row order by 考号 desc return @re end go select row [室号],dbo.f_str(row) [考号区间] from t group by row drop table tb,t drop function f_str /* 室号 考号区间 -------------------- -------------- 1 cc01-cc03 2 cc04-cc05 (2 行受影响) */ http://topic.csdn.net/u/20080305/18/34a9f481-1dac-43bf-9bde-d21d2ba75880.html?seed=1637030877
原文链接：https://blog.csdn.net/htl258/article/details/4517389?ops_request_misc=&request_id=a4e44fc64a9d401fbe67769e67b97f80&biz_id=&utm_medium=distribute.pc_search_result.none-task-blog-2~blog~koosearch~default-15-4517389-null-null.268%5Ev1%5Econtrol&utm_term=%E6%91%A9%E6%89%98%E8%BD%A6%E8%80%83%E5%9C%BA
