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.type,aa.* from bb
left join aa on aa.pr between bb.prsa and bb.prend
不难~~基础!!
