SELECT Xname, Sid, Ss FROM Stu ORDER BY Xname, Ss Desc
select b.sid,b.ssfrom xy a inner join stu b on a.xname=b.xname order by ss desc
select a.Sid,a.Xnaem,a.Ss,b.Sid,b.Xname,b.Ss from Xy a,Stu b where a.Xname=b.Xname order by a.Sid,b.Sid
没测试!select * from Stu as s where (select count(*) from Stu where Xname=s.Xname and Ss>=s.Ss)<=(select Rs from Xy where Xname=s.Xname) order by Xname,Ss
如果说逻辑难题, 必须应用not exists(特别是两三个嵌套)的SQL查询算是。
我起哄一下:----------------table1 create table Xy(Xid int not null identity(1,1), Xname varchar(20), Rs int) ----------------table2 create table Stu(Sid int not null identity(1,1), Xname varchar(20), Ss int) ------------插入数据 insert Xy(Xname,Rs) select '数学系',2 insert Xy(Xname,Rs) select '中文系',5 insert Xy(Xname,Rs) select '化学系',4 insert Xy(Xname,Rs) select '物理系',3 insert Xy(Xname,Rs) select '历史系',1 insert Xy(Xname,Rs) select '外语系',7 --------------- insert Stu(Xname,Ss) select '数学系',83 insert Stu(Xname,Ss) select '数学系',12 insert Stu(Xname,Ss) select '数学系',32 insert Stu(Xname,Ss) select '数学系',43 insert Stu(Xname,Ss) select '数学系',77 insert Stu(Xname,Ss) select '数学系',54 insert Stu(Xname,Ss) select '数学系',91 insert Stu(Xname,Ss) select '数学系',83 insert Stu(Xname,Ss) select '物理系',44 insert Stu(Xname,Ss) select '物理系',54 insert Stu(Xname,Ss) select '物理系',66 go select * from Stu as s where (select count(*) from Stu where Xname=s.Xname and Ss>=s.Ss)<=(select Rs from Xy where Xname=s.Xname) order by Xname,Ss至少给出了点东西。如果“当面讨教”这是傻子的行为,我想今后就都不用跟贴了。
> create table jhua ( dwei varchar(50) , zye varchar(50) , jhrs int ) > create table stu ( sid int , sdwei varchar(50), szye varchar(50) , fshu int) > > Go > > insert into jhua select '理学院','数学系','2' > insert into jhua select '理学院','物理系','3' > insert into jhua select '管理学院','经管系','5' > insert into jhua select '管理学院','英语系','4' > > insert into stu select '1','理学院','数学系','120' > insert into stu select '2','理学院','数学系','110' > insert into stu select '3','理学院','数学系','100' > insert into stu select '4','理学院','物理系','120' > insert into stu select '5','理学院','物理系','110' > insert into stu select '6','理学院','物理系','100' > insert into stu select '7','理学院','物理系','90' > insert into stu select '8','管理学院','经管系','120' > insert into stu select '9','管理学院','经管系','110' > insert into stu select '10','管理学院','经管系','100' > > --select * from jhua > --select * from stu > /* > --结果 > '1','理学院','数学系','120' > '2','理学院','数学系','110' > '4','理学院','物理系','120' > '5','理学院','物理系','110' > '6','理学院','物理系','100' > '8','管理学院','经管系','120' > '9','管理学院','经管系','110' > '10','管理学院','经管系','100' > '10','管理学院','经管系','100' > null,'管理学院','英语系',null > */ > > select > s1.sid, > max(dwei), > max(zye), > max(s1.fshu) > from stu s1 > join stu s2 on s1.sdwei=s2.sdwei and s1.szye=s2.szye > right join jhua on s1.sdwei=dwei and s1.szye=zye > group by s1.sid,s1.sdwei,s1.szye,jhrs > having sum(case when s1.fshu<s2.fshu then 1 else 0 end)<jhrs > > drop table jhua > drop table stu > ______________________________________> > ===================================================================---------使用游标 b c e 教学单位 专业 计划人数 单位1 计算机 4 单位2 计算机 2 单位3 法学 1 入学考试成绩表aaa b c d 学号 教学单位 专业 总分数 1 单位1 计算机 256 2 单位1 计算机 254 3 单位1 计算机 111 4 单位1 计算机 253 4 单位1 计算机 222 如下表 bb(b,c,e) aa(a,b,c,d)DECLARE test_cursor CURSORFOR SELECT b,c,e FROM bbDECLARE @b varchar(40),@c varchar(20),@e intdeclare @sql varchar(200)Create table #temp(id1 varchar(20),id2 varchar(20),id3 float) create table #temp1(id1 float) OPEN test_cursorFETCH NEXT FROM test_cursor INTO @b,@c,@eWHILE (@@fetch_status <> -1) BEGIN declare @num varchar(10) IF (@@fetch_status <> -2) BEGIN delete from #temp1 --有数据. if(select count(*) from aa where b=@b and c=@c)>=@e begin set @sql='insert into #temp1 select top '+cast(@e as varchar)+' d from aa where b= '''+@b+''' and c= '''+@c+ ''' order by d desc' exec(@sql) select top 1 @num=id1 from #temp1 order by id1 asc insert into #temp values(@b,@c,@num) end else begin if(select count(*) from aa where b=@b and c=@c)>0 begin set @num=(select count(*) from aa where b=@b and c=@c)
set @sql='insert into #temp1 select top '+cast(@num as varchar)+ 'd from aa where b = '''+@b+''' and c= '''+@c+''' order by d desc'
exec(@sql)
select top 1 @num=id1 from #temp1 order by id1 asc
insert into #temp values(@b,@c,@num)
end else begin insert into #temp values(@b,@c,0) end end END FETCH NEXT FROM test_cursor INTO @b,@c,@e ENDselect * from #temp drop table #temp1 drop table #temp CLOSE test_cursor DEALLOCATE test_cursor GO 我这个是求的分数线。张的是求的具体人数,但可以把张的改一下.再在外边嵌入一套查询就行了。select dwei'院校',zye as '系别',min(isnull(pp,0)) as '分数线' from ( select s1.sid, max(dwei)dwei, max(zye)zye, max(s1.fshu)pp from stu s1 join stu s2 on s1.sdwei=s2.sdwei and s1.szye=s2.szye right join jhua on s1.sdwei=dwei and s1.szye=zye group by s1.sid,s1.sdwei,s1.szye,jhrs having sum(case when s1.fshu<s2.fshu then 1 else 0 end)<jhrs) c group by dwei,zye
drop table jhua drop table stu -----------是同事给出的题,大家一起参考学习。 -----------还有对于很多人分数相同的情况按照一个人来计算。感谢大家的参与。
FROM Stu
ORDER BY Xname, Ss Desc
order by ss desc
a.Xname=b.Xname order by a.Sid,b.Sid
create table Xy(Xid int not null identity(1,1),
Xname varchar(20),
Rs int)
----------------table2
create table Stu(Sid int not null identity(1,1),
Xname varchar(20),
Ss int)
------------插入数据
insert Xy(Xname,Rs) select '数学系',2
insert Xy(Xname,Rs) select '中文系',5
insert Xy(Xname,Rs) select '化学系',4
insert Xy(Xname,Rs) select '物理系',3
insert Xy(Xname,Rs) select '历史系',1
insert Xy(Xname,Rs) select '外语系',7
---------------
insert Stu(Xname,Ss) select '数学系',83
insert Stu(Xname,Ss) select '数学系',12
insert Stu(Xname,Ss) select '数学系',32
insert Stu(Xname,Ss) select '数学系',43
insert Stu(Xname,Ss) select '数学系',77
insert Stu(Xname,Ss) select '数学系',54
insert Stu(Xname,Ss) select '数学系',91
insert Stu(Xname,Ss) select '数学系',83
insert Stu(Xname,Ss) select '物理系',44
insert Stu(Xname,Ss) select '物理系',54
insert Stu(Xname,Ss) select '物理系',66
go
select * from Stu as s where (select count(*) from Stu where Xname=s.Xname and Ss>=s.Ss)<=(select Rs from Xy where Xname=s.Xname) order by Xname,Ss至少给出了点东西。如果“当面讨教”这是傻子的行为,我想今后就都不用跟贴了。
> create table stu ( sid int , sdwei varchar(50), szye varchar(50) ,
fshu int)
>
> Go
>
> insert into jhua select '理学院','数学系','2'
> insert into jhua select '理学院','物理系','3'
> insert into jhua select '管理学院','经管系','5'
> insert into jhua select '管理学院','英语系','4'
>
> insert into stu select '1','理学院','数学系','120'
> insert into stu select '2','理学院','数学系','110'
> insert into stu select '3','理学院','数学系','100'
> insert into stu select '4','理学院','物理系','120'
> insert into stu select '5','理学院','物理系','110'
> insert into stu select '6','理学院','物理系','100'
> insert into stu select '7','理学院','物理系','90'
> insert into stu select '8','管理学院','经管系','120'
> insert into stu select '9','管理学院','经管系','110'
> insert into stu select '10','管理学院','经管系','100'
>
> --select * from jhua
> --select * from stu
> /*
> --结果
> '1','理学院','数学系','120'
> '2','理学院','数学系','110'
> '4','理学院','物理系','120'
> '5','理学院','物理系','110'
> '6','理学院','物理系','100'
> '8','管理学院','经管系','120'
> '9','管理学院','经管系','110'
> '10','管理学院','经管系','100'
> '10','管理学院','经管系','100'
> null,'管理学院','英语系',null
> */
>
> select
> s1.sid,
> max(dwei),
> max(zye),
> max(s1.fshu)
> from stu s1
> join stu s2 on s1.sdwei=s2.sdwei and s1.szye=s2.szye
> right join jhua on s1.sdwei=dwei and s1.szye=zye
> group by s1.sid,s1.sdwei,s1.szye,jhrs
> having sum(case when s1.fshu<s2.fshu then 1 else 0 end)<jhrs
>
> drop table jhua
> drop table stu
> ______________________________________>
> ===================================================================---------使用游标
b c e
教学单位 专业 计划人数
单位1 计算机 4
单位2 计算机 2
单位3 法学 1
入学考试成绩表aaa b c d
学号 教学单位 专业 总分数
1 单位1 计算机 256
2 单位1 计算机 254
3 单位1 计算机 111
4 单位1 计算机 253
4 单位1 计算机 222
如下表
bb(b,c,e)
aa(a,b,c,d)DECLARE test_cursor CURSORFOR SELECT b,c,e FROM bbDECLARE @b varchar(40),@c varchar(20),@e intdeclare @sql varchar(200)Create table #temp(id1 varchar(20),id2 varchar(20),id3 float)
create table #temp1(id1 float)
OPEN test_cursorFETCH NEXT FROM test_cursor INTO @b,@c,@eWHILE (@@fetch_status <> -1)
BEGIN
declare @num varchar(10)
IF (@@fetch_status <> -2)
BEGIN
delete from #temp1
--有数据.
if(select count(*) from aa where b=@b and c=@c)>=@e
begin
set @sql='insert into #temp1 select top '+cast(@e as
varchar)+' d from aa where b= '''+@b+''' and c= '''+@c+ ''' order by d desc'
exec(@sql) select top 1 @num=id1 from #temp1 order by id1 asc insert into #temp values(@b,@c,@num) end
else
begin
if(select count(*) from aa where b=@b and c=@c)>0
begin
set @num=(select count(*) from aa where b=@b and c=@c)
set @sql='insert into #temp1 select top '+cast(@num as
varchar)+ 'd from aa where b = '''+@b+''' and c= '''+@c+''' order by d
desc'
exec(@sql)
select top 1 @num=id1 from #temp1 order by id1 asc
insert into #temp values(@b,@c,@num)
end
else
begin
insert into #temp values(@b,@c,0)
end end
END
FETCH NEXT FROM test_cursor INTO @b,@c,@e
ENDselect * from #temp
drop table #temp1
drop table #temp
CLOSE test_cursor
DEALLOCATE test_cursor
GO
我这个是求的分数线。张的是求的具体人数,但可以把张的改一下.再在外边嵌入一套查询就行了。select dwei'院校',zye as '系别',min(isnull(pp,0)) as '分数线' from (
select
s1.sid,
max(dwei)dwei,
max(zye)zye,
max(s1.fshu)pp
from stu s1
join stu s2 on s1.sdwei=s2.sdwei and s1.szye=s2.szye
right join jhua on s1.sdwei=dwei and s1.szye=zye
group by s1.sid,s1.sdwei,s1.szye,jhrs having sum(case when s1.fshu<s2.fshu then 1 else 0 end)<jhrs) c group
by dwei,zye
drop table jhua
drop table stu
-----------是同事给出的题,大家一起参考学习。
-----------还有对于很多人分数相同的情况按照一个人来计算。感谢大家的参与。