if not object_id('Tempdb..#s') is null drop table #s Go Create table #s([bj] int,[xm] nvarchar(1),[zf] int) Insert #s select 1,N'a',602 union all select 2,N'b',580 union all select 3,N'c',588 union all select 1,N'd',700 union all select 2,N'e',312 union all select 3,N'f',555 union all select 1,N'g',400 Go declare @zgf int,@zdf int select @zgf=600,@zdf=550 ;with b as (select @zgf as grade1,grade2=800 union all select grade1-10,grade1 from b where grade1>550) ,c as (select 0 as bj union all select distinct bj from #s) ,d as (select * from (Select * from b union all select 0,@zdf) as a ,c) select a.bj,a.grade1 as fs,rs=COUNT(case when b.[zf]<grade2 then 1 end),lj=COUNT(b.bj) from d as a left join #s as b on b.[zf]>=grade1 and (a.bj=0 or a.bj=b.bj) group by a.bj,a.grade1 order by a.bj, a.grade1 desc/* bj fs rs lj 0 600 2 2 0 590 0 2 0 580 2 4 0 570 0 4 0 560 0 4 0 550 1 5 0 0 2 7 1 600 2 2 1 590 0 2 1 580 0 2 1 570 0 2 1 560 0 2 1 550 0 2 1 0 1 3 2 600 0 0 2 590 0 0 2 580 1 1 2 570 0 1 2 560 0 1 2 550 0 1 2 0 1 2 3 600 0 0 3 590 0 0 3 580 1 1 3 570 0 1 3 560 0 1 3 550 1 2 3 0 0 2 */
create table s(bj int,xm nvarchar(10),zf int) insert into s select 1,'a',602 insert into s select 2,'b',580 insert into s select 3,'c',588 insert into s select 1,'d',700 insert into s select 2,'e',312 insert into s select 3,'f',555 insert into s select 1,'g',400 go declare @zgf int,@zdf int set @zgf=600 set @zdf=550select a.bj,a.df,SUM(case when b.zf between a.df and a.gf then 1 else 0 end)rs,COUNT(b.zf)lj from( select a.bj,b.df,b.gf from ( select 0 as bj union select distinct bj from s )a,( select (case when number>0 then (number-1)*10+@zdf else 0 end)df, (case when (number-1)*10+@zdf>=@zgf then 10000 else number*10+@zdf-1 end)gf from master..spt_values where type='p' and (number-1)*10+@zdf<=@zgf )b)a left join s b on b.bj=(case when a.bj=0 then b.bj else a.bj end) and b.zf>=a.df-- between a.df and a.gf group by a.bj,a.df order by a.bj,a.df desc /* bj df rs lj ----------- ----------- ----------- ----------- 0 600 2 2 0 590 0 2 0 580 2 4 0 570 0 4 0 560 0 4 0 550 1 5 0 0 2 7 1 600 2 2 1 590 0 2 1 580 0 2 1 570 0 2 1 560 0 2 1 550 0 2 1 0 1 3 2 600 0 0 2 590 0 0 2 580 1 1 2 570 0 1 2 560 0 1 2 550 0 1 2 0 1 2 3 600 0 0 3 590 0 0 3 580 1 1 3 570 0 1 3 560 0 1 3 550 1 2 3 0 0 2 警告: 聚合或其他 SET 操作消除了 Null 值。(28 行受影响) */ go drop table s
if not object_id('Tempdb..#s') is null
drop table #s
Go
Create table #s([bj] int,[xm] nvarchar(1),[zf] int)
Insert #s
select 1,N'a',602 union all
select 2,N'b',580 union all
select 3,N'c',588 union all
select 1,N'd',700 union all
select 2,N'e',312 union all
select 3,N'f',555 union all
select 1,N'g',400
Go
declare @zgf int,@zdf int
select @zgf=600,@zdf=550
;with b
as
(select @zgf as grade1,grade2=800
union all
select grade1-10,grade1 from b where grade1>550)
,c as
(select 0 as bj union all select distinct bj from #s)
,d as
(select * from (Select * from b union all select 0,@zdf) as a ,c)
select
a.bj,a.grade1 as fs,rs=COUNT(case when b.[zf]<grade2 then 1 end),lj=COUNT(b.bj)
from
d as a
left join
#s as b on b.[zf]>=grade1 and (a.bj=0 or a.bj=b.bj)
group by a.bj,a.grade1
order by a.bj, a.grade1 desc/*
bj fs rs lj
0 600 2 2
0 590 0 2
0 580 2 4
0 570 0 4
0 560 0 4
0 550 1 5
0 0 2 7
1 600 2 2
1 590 0 2
1 580 0 2
1 570 0 2
1 560 0 2
1 550 0 2
1 0 1 3
2 600 0 0
2 590 0 0
2 580 1 1
2 570 0 1
2 560 0 1
2 550 0 1
2 0 1 2
3 600 0 0
3 590 0 0
3 580 1 1
3 570 0 1
3 560 0 1
3 550 1 2
3 0 0 2
*/
insert into s select 1,'a',602
insert into s select 2,'b',580
insert into s select 3,'c',588
insert into s select 1,'d',700
insert into s select 2,'e',312
insert into s select 3,'f',555
insert into s select 1,'g',400
go
declare @zgf int,@zdf int
set @zgf=600
set @zdf=550select a.bj,a.df,SUM(case when b.zf between a.df and a.gf then 1 else 0 end)rs,COUNT(b.zf)lj from(
select a.bj,b.df,b.gf from (
select 0 as bj
union
select distinct bj from s
)a,(
select
(case when number>0 then (number-1)*10+@zdf else 0 end)df,
(case when (number-1)*10+@zdf>=@zgf then 10000 else number*10+@zdf-1 end)gf
from master..spt_values
where type='p' and (number-1)*10+@zdf<=@zgf
)b)a left join s b on b.bj=(case when a.bj=0 then b.bj else a.bj end) and b.zf>=a.df-- between a.df and a.gf
group by a.bj,a.df
order by a.bj,a.df desc
/*
bj df rs lj
----------- ----------- ----------- -----------
0 600 2 2
0 590 0 2
0 580 2 4
0 570 0 4
0 560 0 4
0 550 1 5
0 0 2 7
1 600 2 2
1 590 0 2
1 580 0 2
1 570 0 2
1 560 0 2
1 550 0 2
1 0 1 3
2 600 0 0
2 590 0 0
2 580 1 1
2 570 0 1
2 560 0 1
2 550 0 1
2 0 1 2
3 600 0 0
3 590 0 0
3 580 1 1
3 570 0 1
3 560 0 1
3 550 1 2
3 0 0 2
警告: 聚合或其他 SET 操作消除了 Null 值。(28 行受影响)
*/
go
drop table s