--1.准备数据
--1.1创建表结构,按照每所学校1000人,共30所学校,满分760分计算
if (object_id('tempdb..#baseT') > 0)
drop table #baseT
create table #baseT
(
name varchar(20),
code varchar(20),
studentId uniqueidentifier,
totalScore decimal(4,1)
)
if (object_id('tempdb..#schoolT') > 0)
drop table #schoolT
create table #schoolT
(
id int identity(1,1),
name varchar(20),
code varchar(20)
)
go
--1.2生成数据
insert into #schoolT
select '清华大学','bj0001'
union all
select '北京大学','bj0002'
union all
select '人民大学','bj0003'
declare @index int = 1,@iIndex int = 0, @sCount int
declare
@fullScore decimal(4,1) = 760.0,
@tScore decimal(4,1)
declare @name varchar(20),@code varchar(20)
while @index < 4
begin
select @name = name ,@code = code from #schoolT where id=@index
set @iIndex = 0;
set @sCount = 5000 * @index
while(@iIndex < @sCount)
begin
set @tScore = RAND() * @fullScore
insert into #baseT
select @name,@code,NEWID(),@tScore
set @iIndex += 1
end
set @index += 1
end
select * from #baseT
--2.运算结果
declare @fScore decimal(4,1) = 760.0 --满分同上面创建时一样
declare @colCount int = CEILING(@fScore/50.0)
declare
@sql varchar(max) = '',
@fStr varchar(max) = '',
@sStr varchar(max) = '',
@colName varchar(20)
declare @i int = 0,@start varchar(10),@end varchar(10)
--2.1分段生成动态部分
while(@i<@colCount)
begin
set @start = ltrim(50 * @i)
set @end = LTRIM(50 * (@i+1))
if(@i = @colCount-1)
begin
set @colName = '[' + LTRIM(@start) +'分以上]'
end
else
begin
set @colName = '[' + LTRIM(@start) + '-' + LTRIM(@end-1) + '分]'
end
set @fStr = ',COUNT(nullif(case when totalScore >=' + @start + ' and totalScore < ' + @end + ' then 0 else 1 end,1)) as ' + @colName + @fStr
set @sStr = ',ltrim(' + @colName + ') + ''|'' + ltrim(convert(decimal(4,1),' + @colName + '*100.0/人数)) + ''%'' + ''|'' + ltrim(convert(decimal(4,1),' + @colName + '*100.0/@allCount)) + ''%'' as ' + @colName + @sStr
set @i += 1
end
--2.2构造整个SQL语句并执行结果
set @sql = '
declare @allCount int
select @allCount = COUNT(*) from #baseT
;with tempR as
(
select name as 学校名称 ,code as 学校代码, COUNT(*) as 人数' + @fStr + '
from #baseT
group by name,code
)
--select * from tempR
select 学校名称,学校代码,人数' + @sStr + '
from tempR'
exec(@sql)
--1.1创建表结构,按照每所学校1000人,共30所学校,满分760分计算
if (object_id('tempdb..#baseT') > 0)
drop table #baseT
create table #baseT
(
name varchar(20),
code varchar(20),
studentId uniqueidentifier,
totalScore decimal(4,1)
)
if (object_id('tempdb..#schoolT') > 0)
drop table #schoolT
create table #schoolT
(
id int identity(1,1),
name varchar(20),
code varchar(20)
)
go
--1.2生成数据
insert into #schoolT
select '清华大学','bj0001'
union all
select '北京大学','bj0002'
union all
select '人民大学','bj0003'
declare @index int = 1,@iIndex int = 0, @sCount int
declare
@fullScore decimal(4,1) = 760.0,
@tScore decimal(4,1)
declare @name varchar(20),@code varchar(20)
while @index < 4
begin
select @name = name ,@code = code from #schoolT where id=@index
set @iIndex = 0;
set @sCount = 5000 * @index
while(@iIndex < @sCount)
begin
set @tScore = RAND() * @fullScore
insert into #baseT
select @name,@code,NEWID(),@tScore
set @iIndex += 1
end
set @index += 1
end
select * from #baseT
--2.运算结果
declare @fScore decimal(4,1) = 760.0 --满分同上面创建时一样
declare @colCount int = CEILING(@fScore/50.0)
declare
@sql varchar(max) = '',
@fStr varchar(max) = '',
@sStr varchar(max) = '',
@colName varchar(20)
declare @i int = 0,@start varchar(10),@end varchar(10)
--2.1分段生成动态部分
while(@i<@colCount)
begin
set @start = ltrim(50 * @i)
set @end = LTRIM(50 * (@i+1))
if(@i = @colCount-1)
begin
set @colName = '[' + LTRIM(@start) +'分以上]'
end
else
begin
set @colName = '[' + LTRIM(@start) + '-' + LTRIM(@end-1) + '分]'
end
set @fStr = ',COUNT(nullif(case when totalScore >=' + @start + ' and totalScore < ' + @end + ' then 0 else 1 end,1)) as ' + @colName + @fStr
set @sStr = ',ltrim(' + @colName + ') + ''|'' + ltrim(convert(decimal(4,1),' + @colName + '*100.0/人数)) + ''%'' + ''|'' + ltrim(convert(decimal(4,1),' + @colName + '*100.0/@allCount)) + ''%'' as ' + @colName + @sStr
set @i += 1
end
--2.2构造整个SQL语句并执行结果
set @sql = '
declare @allCount int
select @allCount = COUNT(*) from #baseT
;with tempR as
(
select name as 学校名称 ,code as 学校代码, COUNT(*) as 人数' + @fStr + '
from #baseT
group by name,code
)
--select * from tempR
select 学校名称,学校代码,人数' + @sStr + '
from tempR'
exec(@sql)