大家来玩SQL语句

[8LY8Apollo]

表1，学院信息表(学院编号，
学院系别，
招生人数)
表2，考生信息表（考生编号，
考生所报系别，
考生分数）
-----------------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 select '数学系',2
insert Xy select '中文系',5
insert Xy select '化学系',4
insert Xy select '物理系',3
insert Xy select '历史系',1
insert Xy select '外语系',7
---------------
insert Stu'数学系'，83
insert Stu'数学系'，12
insert Stu'数学系'，32
insert Stu'数学系'，43
insert Stu'数学系'，77
insert Stu'数学系'，54
insert Stu'数学系'，91
insert Stu'数学系'，83
insert Stu'物理系'，44
insert Stu'物理系'，54
insert Stu'物理系'，66
…………………………
求出每个系的录取人资料，包括人员编号和分数（录取分从高到低）
--------------------------
很有意思的一道题，谢谢参与。

[wzh1215]

select Xname,Sid,Ss
from Stu,Xy
where Ss>=60 and Stu.Xname=Xy.Xname
order by Xname,Ss

[zarge]

SELECT Xname, Sid, Ss
FROM Stu
ORDER BY Xname, Ss Desc

[zhouzdsoft]

select b.sid,b.ss

from xy a inner join stu b on a.xname=b.xname
order by ss desc

[w_rose]

如果说逻辑难题， 必须应用not exists（特别是两三个嵌套）的SQL查询算是。

[zgq19801123]

up

[happy_0325]

经测试，上面的语句都没达到搂主的要求。
搂主什么时候亮答案？

[maoyesky]

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

[8LY8Apollo]

不好意思，是院系录取的学生。没有那么简单，呵呵。

[w_rose]

没测试！

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

[w_rose]

我的回答中的问题是：对于恰好在录取学生中最低分但是又有多人分数相同的情况，可能没有很好处理。

[w_rose]

我起哄一下：

----------------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

至少给出了点东西。如果“当面讨教”这是傻子的行为，我想今后就都不用跟贴了。

[8LY8Apollo]

> 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


入学考试成绩表aa

a 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 CURSOR

FOR SELECT b,c,e FROM bb

DECLARE @b varchar(40),@c varchar(20),@e int

declare @sql varchar(200)

Create table #temp(id1 varchar(20),id2 varchar(20),id3 float)


create table #temp1(id1 float)


OPEN test_cursor

FETCH NEXT FROM test_cursor INTO @b,@c,@e

WHILE (@@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
END

select * 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
-----------是同事给出的题，大家一起参考学习。
-----------还有对于很多人分数相同的情况按照一个人来计算。

感谢大家的参与。