要实现动态查询,即分数段随成绩的变化可以改变。而不是使用死的,分数段固定的方法declare @MAX [int];---取最大成绩 如612
declare @MIN [int];---取最小成绩 如389
declare @MAXin [nvarchar](50); ---取最大成绩的区间如600
declare @minin [nvarchar](50);---取最小成绩的区间 如380
declare @minoff [nvarchar](50);---最小成绩的区间加9或者加@dis-1
declare @str [nvarchar](50); ---区间 表示为如 380-389,即@minin-@minoff
select @MAX= max(三门), @MIN= MIN(三门) from zongchenjibiao WHERE 语文缺='0' and 数学缺='0' and 英语缺='0'
set @maxin=@max/10*10 ----@maxin=@max/@dis*@dis
set @minin=@min/10*10 ----@minin=@min/@dis*@dis
set @minoff=@minin+9
set @str = @minin + '-' + @minoff
select @max,@min,@maxin,@minin,@minoff,@str
select
---用这样一个循环实现 下面的方法。
---case
---when 三门 between 390 and 399 then '390-399'
---when 三门 between 380 and 389 then '380-389' -----效果,静态实例
---else '不在范围' end as 分数段,
case
if @minin<@maxin
begin
when 三门 between @minin and @minoff then 'str'
set @minin=@minin+10
set @minoff=@minin+9
set @str = @minin + '-' + @minoff
end----这一段 怎么处理 else '不在范围' end as 分数段,
sum(case when zongchenjibiao.年级= '高一' and zongchenjibiao.班级='01' then 1 else 0 end) as [高一01],
sum(case when zongchenjibiao.年级= '高二' and zongchenjibiao.班级='02' then 1 else 0 end) as [高二02],
sum(case when zongchenjibiao.年级= '高二' and zongchenjibiao.班级='03' then 1 else 0 end) as [高二03] from dangqianbanji,zongchenjibiao
where dangqianbanji.班级=zongchenjibiao.班级 and 语文缺='0' and 数学缺='0' and 英语缺='0'
group by
---case
---when 三门 between 390 and 399 then '390-399'
---when 三门 between 380 and 389 then '380-389'
---else '不在范围' end as 分数段,
case
if @minin<@maxin
begin
when 三门 between @minin and @minoff then 'str'
set @minin=@minin+10
set @minoff=@minin+9
set @str = @minin + '-' + @minoff
end
else '不在范围' end
执行结果
分数段 高二01 高二02 高二 03
310-319 1 0 0
320-329 1 0 0
350-359 0 2 0
360-369 3 1 0
370-379 3 1 0
380-389 1 0 0
390-399 0 1 0
declare @MIN [int];---取最小成绩 如389
declare @MAXin [nvarchar](50); ---取最大成绩的区间如600
declare @minin [nvarchar](50);---取最小成绩的区间 如380
declare @minoff [nvarchar](50);---最小成绩的区间加9或者加@dis-1
declare @str [nvarchar](50); ---区间 表示为如 380-389,即@minin-@minoff
select @MAX= max(三门), @MIN= MIN(三门) from zongchenjibiao WHERE 语文缺='0' and 数学缺='0' and 英语缺='0'
set @maxin=@max/10*10 ----@maxin=@max/@dis*@dis
set @minin=@min/10*10 ----@minin=@min/@dis*@dis
set @minoff=@minin+9
set @str = @minin + '-' + @minoff
select @max,@min,@maxin,@minin,@minoff,@str
select
---用这样一个循环实现 下面的方法。
---case
---when 三门 between 390 and 399 then '390-399'
---when 三门 between 380 and 389 then '380-389' -----效果,静态实例
---else '不在范围' end as 分数段,
case
if @minin<@maxin
begin
when 三门 between @minin and @minoff then 'str'
set @minin=@minin+10
set @minoff=@minin+9
set @str = @minin + '-' + @minoff
end----这一段 怎么处理 else '不在范围' end as 分数段,
sum(case when zongchenjibiao.年级= '高一' and zongchenjibiao.班级='01' then 1 else 0 end) as [高一01],
sum(case when zongchenjibiao.年级= '高二' and zongchenjibiao.班级='02' then 1 else 0 end) as [高二02],
sum(case when zongchenjibiao.年级= '高二' and zongchenjibiao.班级='03' then 1 else 0 end) as [高二03] from dangqianbanji,zongchenjibiao
where dangqianbanji.班级=zongchenjibiao.班级 and 语文缺='0' and 数学缺='0' and 英语缺='0'
group by
---case
---when 三门 between 390 and 399 then '390-399'
---when 三门 between 380 and 389 then '380-389'
---else '不在范围' end as 分数段,
case
if @minin<@maxin
begin
when 三门 between @minin and @minoff then 'str'
set @minin=@minin+10
set @minoff=@minin+9
set @str = @minin + '-' + @minoff
end
else '不在范围' end
执行结果
分数段 高二01 高二02 高二 03
310-319 1 0 0
320-329 1 0 0
350-359 0 2 0
360-369 3 1 0
370-379 3 1 0
380-389 1 0 0
390-399 0 1 0
只能继续case when啊!
我改成这样了DECLARE @Max INT,@Min INT,@Str NVARCHAR(3000)
SELECT @Max=602,@Min=380
SELECT @Max=@Max/10*10,@Min=@Min/10*10,@str=N' Case '
WHILE @Min<=@Max
BEGIN
SELECT @str=@str+N' WHEN 三门 BETWEEN '+RTRIM(@MIN)+' AND '+rtrim(@MIN+9)+' THEN '''+RTRIM(@MIN)+'-'+rtrim(@MIN+9)+'''',@MIN=@MIN+10
ENDEXEC ( 'select [分数段]='+@Str+' else ''不在范围'' end,'+
'sum(case when zongchenjibiao.班级=01 then 1 else 0 end) as [高一01],
sum(case when zongchenjibiao.班级=02 then 1 else 0 end) as [高二02],
sum(case when zongchenjibiao.班级=03 then 1 else 0 end) as [高二03] from dangqianbanji,zongchenjibiao
where dangqianbanji.班级=zongchenjibiao.班级 and 语文缺=0 and 数学缺=0 and 英语缺=0
group by '+@str+' else ''不在范围'' end')即把“case when zongchenjibiao.年级= '高一' and zongchenjibiao.班级='01' then 1 else 0 end) as [高一01]” 变成这样
“case when zongchenjibiao.班级=01 then 1 else 0 end) as [高一01]” 如果这样“case when zongchenjibiao.年级= 高一 and zongchenjibiao.班级=01 then 1 else 0 end) as [高一01]” 提示错误 消息 207,级别 16,状态 1,第 1 行
列名 '高一' 无效。怎么加上年级呢或者把年级改成02就好了
SELECT @Max=602,@Min=380
SELECT @Max=@Max/10*10,@Min=@Min/10*10,@str=N' Case '
WHILE @Min<=@Max
BEGIN
SELECT @str=@str+N' WHEN 三门 BETWEEN '+RTRIM(@MIN)+' AND '+rtrim(@MIN+9)+' THEN '''+RTRIM(@MIN)+'-'+rtrim(@MIN+9)+'''',@MIN=@MIN+10
ENDEXEC ( 'select [分数段]='+@Str+' else ''不在范围'' end,'+
'sum(case when zongchenjibiao.年级= N''高一'' and zongchenjibiao.班级=''01'' then 1 else 0 end) as [高一01],
sum(case when zongchenjibiao.年级= N''高二'' and zongchenjibiao.班级=''02'' then 1 else 0 end) as [高二02],
sum(case when zongchenjibiao.年级= N''高二'' and zongchenjibiao.班级=''03'' then 1 else 0 end) as [高二03] from dangqianbanji,zongchenjibiao
where dangqianbanji.班级=zongchenjibiao.班级 and 语文缺=''0'' and 数学缺=''0'' and 英语缺=''0''
group by '+@str+' else ''不在范围'' end')字符串字符加上引上,上面漏打了引号
sum(case when zongchenjibiao.年级= N'高二' and zongchenjibiao.班级='02' then 1 else 0 end) as [高二02],
sum(case when zongchenjibiao.年级= N'高二' and zongchenjibiao.班级='03' then 1 else 0 end) as [高二03]
from dangqianbanji,zongchenjibiao
where dangqianbanji.班级=zongchenjibiao.班级 and 语文缺='0' and 数学缺='0' and 英语缺='0'
group by Case WHEN 三门 BETWEEN 380 AND 389 THEN '380-389' WHEN 三门 BETWEEN 390 AND 399 THEN '390-399' WHEN 三门 BETWEEN 400 AND 409 THEN '400-409' WHEN 三门 BETWEEN 410 AND 419 THEN '410-419' WHEN 三门 BETWEEN 420 AND 429 THEN '420-429' WHEN 三门 BETWEEN 430 AND 439 THEN '430-439' WHEN 三门 BETWEEN 440 AND 449 THEN '440-449' WHEN 三门 BETWEEN 450 AND 459 THEN '450-459' WHEN 三门 BETWEEN 460 AND 469 THEN '460-469' WHEN 三 else '不在范围' end