SQL难题
if   object_id( 'bb ')   is   not   null
drop   table   bb
go
CREATE   TABLE   [bb]   (
[prsa]   [int]   NULL   ,
[prend]   [int]   NULL   ,
[type]   [char]   (2)   NULL  
)   ON   [PRIMARY]
go
insert   bb   select   12,213, 'D '
union   all   select   214,300, 'D+ '
union   all   select   301,413, 'C '
union   all   Select   414,500, 'C+ '
union   all   select   501,613, 'B '
union   all   select   614,700, 'B+ '
union   all   select   701,800, 'A '
go
select   *   from   bb
GO
if   object_id( 'aa ')   is   not   null
drop   table   aa
go
CREATE   TABLE   [aa]   (
[name]   [nvarchar]   (20)   NULL   ,
[pr]   [int]   NULL  
)   ON   [PRIMARY]
GO
insert   aa   select   N '小1 ',521
union   all   select   N '王2 ',321
union   all   select   N '李3 ',152
union   all   select   N '李4 ',132
go
select   *   from   aa
--===========评出aa表中学生的等级(aa的表数据不固定)============================
--===========评出bb表中各等级学生数(即打A的有多少学生,打B的有多少学生)============================
--=====================评出bb表中各等级学生有哪些===================

------解决方案--------------------
--评出bb表中各等级学生有哪些
create function fun(@prsa int, @prend int)
returns varchar(1000)
as
begin
declare @re varchar(1000)
set @re= ' '
select @re=@re+ ', '+name from aa where pr between @prsa and @prend

return(stuff(@re, 1, 1, ' '))
end
go

select
bb.type,
[count]=count(pr),
[name]=dbo.fun(prsa, prend)
from bb
left join aa on aa.pr between bb.prsa and bb.prend
group by bb.type, bb.prsa, bb.prend

--result
type count name
---- ----------- ------------------
A 0 NULL
B 1 小1
B+ 0 NULL
C 1 王2
C+ 0 NULL
D 2 李3,李4
D+ 0 NULL

(7 row(s) affected)
------解决方案--------------------
select name,等级=max(case when pr between prsa and prend then type else null end) from aa a, bb b
group by name
--2:
select 等级=type,个数=sum(case when pr between prsa and prend then 1 else 0 end) from bb b,aa a
group by type
--3:
select * from
(
select 等级=type,个数=sum(case when pr between prsa and prend then 1 else 0 end) from bb b,aa a
group by type
) ta left join
(
select name,等级=max(case when pr between prsa and prend then type else null end) from aa a, bb b
group by name
) tb
on ta.等级=tb.等级

------解决方案--------------------


--===========评出aa表中学生的等级(aa的表数据不固定)============================
select aa.*, bb.type from aa
join bb on aa.pr between bb.prsa and bb.prend

--===========评出bb表中各等级学生数(即打A的有多少学生,打B的有多少学生)============================
select type,isnull(sum(cn),0)[count] from bb
left join (select 1 cn,* from aa)aa on aa.pr between bb.prsa and bb.prend
group by type

--=====================评出bb表中各等级学生有哪些===================
select bb