create function getmaxid(@KID varchar(2)) returns int as ...create function getminid(@KID varchar(2)) returns int as ...create function 各科目 returns @re table(科目 varchar(2)) as begin insert @re select distinct 科目 from table return endselect 科目,dbo.getmaxid(科目) as maxid,dbo.getminid(科目) as minid from dbo.各科目不知行不行的
drop table test create table test (ID int IDENTITY(1,1), --自增长,主键 KID varchar(2),--课程编号,如"K1", "K2" SID varchar(2), --学生编号,如"S1", "S2" Value int,--学生成绩 TID varchar(2)--教师编号,如"T1", "T2" )给些数据来试下
insert into test(kid,sid,value) select '01','01',92 union all select '01','02',92 union all select '01','03',90 union all select '02','01',92 union all select '02','02',91 union all select '02','03',90 union all select '03','01',80 union all select '03','02',70 union all select '03','03',50 union all select '03','04',50 union all select '04','01',80
select SID ,Value from test where Value in(select Max(Value) from test) union select SID ,Value from test where Value in(select min(Value) from test) 第一行是最高分的学生和分数 第二行是最低分的学生和分数
select * from ( select kid, sid as sid_max,value as maxValue from test where id in ( select id from test where value in (select max(value) from test group by kid )) ) a inner join ( select kid, sid as sid_min,value as minValue from test where id in ( select id from test where value in (select min(value) from test group by kid )) ) b on a.kid = b.kid我只能做到这一步了 有一个问题,如果最高分有多个人的并且最低分只有一个的时候,那么会重复显示最低分 你试试就知道了,这个是连接本身的问题,我还没找到办法解决 我提出来大家讨论一下吧两个表a(id,int,desc varchar),b(id,int,desc varchar) 如果id=1时,a表中有4条记录而b表中只有1条记录的话 通过join连接a,b查询时,会发生b表desc字段重复显示,就像如下显示的那样 kid sid_max value_max kid sid_min value_min 1 2 96 1 3 82 1 4 96 1 3 82 2 6 96 2 7 69 kid=1时就发生我所说 的情况
drop table test create table test ( ID int IDENTITY(1,1), --自增长,主键 KID varchar(2),--课程编号,如"K1", "K2" SID varchar(2), --学生编号,如"S1", "S2" Value int,--学生成绩 TID varchar(2)--教师编号,如"T1", "T2" )insert test (kid,sid,value) select '10','22','100' union all select '10','23','100' union all select '10','24','13' union all select '11','23','45' union all select '11','24','65' union all select '11','25','45'select b.*,c.sid,null,null from( select distinct kid,max_v=(select max(value) from test where kid=a.kid) from test a ) b join test c on b.kid=c.kid and b.max_v=c.value union allselect b.kid,null,null,b.min_v,c.sid from( select distinct kid,min_v=(select min(value) from test where kid=a.kid) from test a ) b join test c on b.kid=c.kid and b.min_v=c.value看这样行不行
to:aht1979(蓝色雪狐) ( ) 谢谢,不过只差了一点,:)
--我做的 select a.kid ,a.maxvalue,a.sid as max_id,b.minvalue,b.sid as min_id from (select a.sid,a.kid,a.value as maxvalue from test a join (select kid,max(value) as maxvalue from test group by kid) b on a.kid=b.kid and a.value=b.maxvalue ) a full join (select a.sid,a.kid,a.value as minvalue from test a join (select kid,min(value) as minvalue from test group by kid) c on a.kid=c.kid and a.value=c.minvalue) b on a.kid=b.kid and (select sum(1) from (select a.sid,a.kid,a.value as maxvalue from test a join (select kid,max(value) as maxvalue from test group by kid) b on a.kid=b.kid and a.value=b.maxvalue ) d where d.kid=a.kid and d.sid<=a.sid) = (select sum(1) from (select a.sid,a.kid,a.value as minvalue from test a join (select kid,min(value) as minvalue from test group by kid) c on a.kid=c.kid and a.value=c.minvalue) e where e.kid=b.kid and e.sid<=b.sid) order by kid
select c.kid,c.Max_sid,c.maxVal, d.Min_sid,d.minVal from (select a.kid,[Max_sid]=b.sid,a.maxVal from (select kid,[maxVal]=max(value) from ttt group by kid) a left join ttt b on a.kid=b.kid and a.maxVal=b.value) cleft join(select a.kid,[Min_sid]=b.sid,a.minVal from (select kid,[minVal]=min(value) from ttt group by kid) a left join ttt b on a.kid=b.kid and a.minVal=b.value) don c.kid=d.kid
----这个可以达到效果select c.kid, c.Max_sid, c.maxVal, d.Min_sid, d.minVal into #tmpTab from (select a.kid,[Max_sid]=b.sid,a.maxVal from (select kid,[maxVal]=max(value) from ttt group by kid) a left join ttt b on a.kid=b.kid and a.maxVal=b.value) cleft join(select a.kid,[Min_sid]=b.sid,a.minVal from (select kid,[minVal]=min(value) from ttt group by kid) a left join ttt b on a.kid=b.kid and a.minVal=b.value) don c.kid=d.kidupdate #tmpTab set Max_sid=Null,maxVal=NUll from #tmpTab a where (select count(*) from #tmpTab b where b.kid=a.kid and b.max_sid=a.max_sid and b.min_sid<a.min_sid ) between 1 and 1update #tmpTab set Min_sid=Null,minVal=NUll from #tmpTab a where (select count(*) from #tmpTab b where b.kid=a.kid and b.min_sid=a.min_sid and b.max_sid<a.max_sid ) between 1 and 1 select * from #tmpTabdrop table #tmpTab
----这个不可以达到效果吗?select c.kid, c.Max_sid, c.maxVal, d.Min_sid, d.minVal into #tmpTab from (select a.kid,[Max_sid]=b.sid,a.maxVal from (select kid,[maxVal]=max(value) from ttt group by kid) a left join ttt b on a.kid=b.kid and a.maxVal=b.value) cleft join(select a.kid,[Min_sid]=b.sid,a.minVal from (select kid,[minVal]=min(value) from ttt group by kid) a left join ttt b on a.kid=b.kid and a.minVal=b.value) don c.kid=d.kid order by c.kid,c.Max_sid,d.Min_sidupdate #tmpTab set Max_sid=Null,maxVal=NUll from #tmpTab a where (select count(*) from #tmpTab b where b.kid=a.kid and b.max_sid=a.max_sid and b.min_sid<a.min_sid ) between 1 and 999999update #tmpTab set Min_sid=Null,minVal=NUll from #tmpTab a where (select count(*) from #tmpTab b where b.kid=a.kid and b.min_sid=a.min_sid and b.max_sid<a.max_sid ) between 1 and 999999 select * from #tmpTabdrop table #tmpTab
drop table test create table test ( ID int IDENTITY(1,1), --自增长,主键 KID varchar(2),--课程编号,如"K1", "K2" SID varchar(2), --学生编号,如"S1", "S2" Value int,--学生成绩 TID varchar(2)--教师编号,如"T1", "T2" )insert test (kid,sid,value) select '10','22','100' union all select '10','23','100' union all select '10','24','13' union all select '11','23','45' union all select '11','24','65' union all select '11','25','45'select from ( select KID,max(value) as maxvalue,min(value) as minvalte from test group by kid ) as g,test ta,testselect x.kid, x.value, x.sid, y.value, y.sid from ( select *,(select count(*) from test where kid=a.kid and sid<=a.sid and value=a.value) as num from test a where value=(select max(value) from test where kid=a.kid) ) as x full join ( select *,(select count(*) from test where kid=a.kid and sid<=a.sid and value=a.value) as num from test a where value=(select min(value) from test where kid=a.kid) ) as y on x.kid=y.kid and x.num=y.num
drop table test create table test ( ID int IDENTITY(1,1), --自增长,主键 KID varchar(2),--课程编号,如"K1", "K2" SID varchar(2), --学生编号,如"S1", "S2" Value int,--学生成绩 TID varchar(2)--教师编号,如"T1", "T2" )insert test (kid,sid,value) select '10','22','100' union all select '10','23','100' union all select '10','24','13' union all select '11','23','45' union all select '11','24','65' union all select '11','25','45' go select x.kid, x.value, x.sid, y.value, y.sid from ( select *,(select count(*) from test where kid=a.kid and sid<=a.sid and value=a.value) as num from test a where value=(select max(value) from test where kid=a.kid) ) as x full join ( select *,(select count(*) from test where kid=a.kid and sid<=a.sid and value=a.value) as num from test a where value=(select min(value) from test where kid=a.kid) ) as y on x.kid=y.kid and x.num=y.num
returns int
as
...create function getminid(@KID varchar(2))
returns int
as
...create function 各科目
returns @re table(科目 varchar(2))
as
begin
insert @re
select distinct 科目 from table
return
endselect 科目,dbo.getmaxid(科目) as maxid,dbo.getminid(科目) as minid from dbo.各科目不知行不行的
KID varchar(2),--课程编号,如"K1", "K2"
SID varchar(2), --学生编号,如"S1", "S2"
Value int,--学生成绩
TID varchar(2)--教师编号,如"T1", "T2"
)给些数据来试下
union all select '01','02',92
union all select '01','03',90
union all select '02','01',92
union all select '02','02',91
union all select '02','03',90
union all select '03','01',80
union all select '03','02',70
union all select '03','03',50
union all select '03','04',50
union all select '04','01',80
union
select SID ,Value from test where Value in(select min(Value) from test)
第一行是最高分的学生和分数
第二行是最低分的学生和分数
(
select kid, sid as sid_max,value as maxValue from test where id in (
select id from test where value in (select max(value) from test group by kid ))
) a
inner join (
select kid, sid as sid_min,value as minValue from test where id in (
select id from test where value in (select min(value) from test group by kid ))
) b on a.kid = b.kid我只能做到这一步了
有一个问题,如果最高分有多个人的并且最低分只有一个的时候,那么会重复显示最低分
你试试就知道了,这个是连接本身的问题,我还没找到办法解决
我提出来大家讨论一下吧两个表a(id,int,desc varchar),b(id,int,desc varchar)
如果id=1时,a表中有4条记录而b表中只有1条记录的话
通过join连接a,b查询时,会发生b表desc字段重复显示,就像如下显示的那样
kid sid_max value_max kid sid_min value_min
1 2 96 1 3 82
1 4 96 1 3 82
2 6 96 2 7 69
kid=1时就发生我所说 的情况
create table test (
ID int IDENTITY(1,1), --自增长,主键
KID varchar(2),--课程编号,如"K1", "K2"
SID varchar(2), --学生编号,如"S1", "S2"
Value int,--学生成绩
TID varchar(2)--教师编号,如"T1", "T2"
)insert test (kid,sid,value)
select '10','22','100'
union all select '10','23','100'
union all select '10','24','13'
union all select '11','23','45'
union all select '11','24','65'
union all select '11','25','45'select b.*,c.sid,null,null from(
select distinct kid,max_v=(select max(value) from test where kid=a.kid) from test a
) b join test c on b.kid=c.kid and b.max_v=c.value
union allselect b.kid,null,null,b.min_v,c.sid from(
select distinct kid,min_v=(select min(value) from test where kid=a.kid) from test a
) b join test c on b.kid=c.kid and b.min_v=c.value看这样行不行
谢谢,不过只差了一点,:)
select a.kid ,a.maxvalue,a.sid as max_id,b.minvalue,b.sid as min_id from
(select a.sid,a.kid,a.value as maxvalue from test a join
(select kid,max(value) as maxvalue from test group by kid) b
on a.kid=b.kid and a.value=b.maxvalue ) a full join
(select a.sid,a.kid,a.value as minvalue from test a join
(select kid,min(value) as minvalue from test group by kid) c
on a.kid=c.kid and a.value=c.minvalue) b
on a.kid=b.kid
and (select sum(1) from
(select a.sid,a.kid,a.value as maxvalue from test a join
(select kid,max(value) as maxvalue from test group by kid) b
on a.kid=b.kid and a.value=b.maxvalue ) d
where d.kid=a.kid and d.sid<=a.sid)
=
(select sum(1) from
(select a.sid,a.kid,a.value as minvalue from test a join
(select kid,min(value) as minvalue from test group by kid) c
on a.kid=c.kid and a.value=c.minvalue) e
where e.kid=b.kid and e.sid<=b.sid)
order by kid
其实相当于i++,
在这里可理解为count(*)
我想问问sum(1)是什么意思啊: 就是为了下面的效果(也相当于count(*))
KID SID_max max(Value) SID_min min(Value) sum(1) sum(1)
01 01 92 03 90 1 1
01 02 92 null null 2 1
d.Min_sid,d.minVal
from
(select a.kid,[Max_sid]=b.sid,a.maxVal from
(select kid,[maxVal]=max(value)
from ttt
group by kid) a
left join ttt b
on a.kid=b.kid and a.maxVal=b.value) cleft join(select a.kid,[Min_sid]=b.sid,a.minVal from
(select kid,[minVal]=min(value)
from ttt
group by kid) a
left join ttt b
on a.kid=b.kid and a.minVal=b.value) don c.kid=d.kid
kid Max_sid maxVal Min_sid minVal
---- ------- ----------- ------- -----------
01 01 92 03 90
01 02 92 03 90
02 01 92 03 90
03 01 80 03 50
03 01 80 04 50
04 01 80 01 80(所影响的行数为 6 行)
c.Max_sid,
c.maxVal,
d.Min_sid,
d.minVal
into #tmpTab
from
(select a.kid,[Max_sid]=b.sid,a.maxVal from
(select kid,[maxVal]=max(value)
from ttt
group by kid) a
left join ttt b
on a.kid=b.kid and a.maxVal=b.value) cleft join(select a.kid,[Min_sid]=b.sid,a.minVal from
(select kid,[minVal]=min(value)
from ttt
group by kid) a
left join ttt b
on a.kid=b.kid and a.minVal=b.value) don c.kid=d.kidupdate #tmpTab set Max_sid=Null,maxVal=NUll
from #tmpTab a
where (select count(*) from #tmpTab b
where b.kid=a.kid and b.max_sid=a.max_sid and b.min_sid<a.min_sid
) between 1 and 1update #tmpTab set Min_sid=Null,minVal=NUll
from #tmpTab a
where (select count(*) from #tmpTab b
where b.kid=a.kid and b.min_sid=a.min_sid and b.max_sid<a.max_sid
) between 1 and 1
select * from #tmpTabdrop table #tmpTab
kid Max_sid maxVal Min_sid minVal
---- ------- ----------- ------- -----------
01 01 92 03 90
01 02 92 NULL NULL
02 01 92 03 90
03 01 80 03 50
03 NULL NULL 04 50
04 01 80 01 80(所影响的行数为 6 行)
order by c.kid,c.Max_sid,d.Min_sid,
c.Max_sid,
c.maxVal,
d.Min_sid,
d.minVal
into #tmpTab
from
(select a.kid,[Max_sid]=b.sid,a.maxVal from
(select kid,[maxVal]=max(value)
from ttt
group by kid) a
left join ttt b
on a.kid=b.kid and a.maxVal=b.value) cleft join(select a.kid,[Min_sid]=b.sid,a.minVal from
(select kid,[minVal]=min(value)
from ttt
group by kid) a
left join ttt b
on a.kid=b.kid and a.minVal=b.value) don c.kid=d.kid
order by c.kid,c.Max_sid,d.Min_sidupdate #tmpTab set Max_sid=Null,maxVal=NUll
from #tmpTab a
where (select count(*) from #tmpTab b
where b.kid=a.kid and b.max_sid=a.max_sid and b.min_sid<a.min_sid
) between 1 and 999999update #tmpTab set Min_sid=Null,minVal=NUll
from #tmpTab a
where (select count(*) from #tmpTab b
where b.kid=a.kid and b.min_sid=a.min_sid and b.max_sid<a.max_sid
) between 1 and 999999
select * from #tmpTabdrop table #tmpTab
回复人: zheninchangjiang(我爱燕子) ( ) 信誉:100 2004-04-16 22:02:00 得分:0
对,一次查询
create table test (
ID int IDENTITY(1,1), --自增长,主键
KID varchar(2),--课程编号,如"K1", "K2"
SID varchar(2), --学生编号,如"S1", "S2"
Value int,--学生成绩
TID varchar(2)--教师编号,如"T1", "T2"
)insert test (kid,sid,value)
select '10','22','100'
union all select '10','23','100'
union all select '10','24','13'
union all select '11','23','45'
union all select '11','24','65'
union all select '11','25','45'select
from
(
select KID,max(value) as maxvalue,min(value) as minvalte
from test
group by kid
) as g,test ta,testselect
x.kid,
x.value,
x.sid,
y.value,
y.sid
from (
select *,(select count(*) from test where kid=a.kid and sid<=a.sid and value=a.value) as num from test a
where value=(select max(value) from test where kid=a.kid)
) as x
full join (
select *,(select count(*) from test where kid=a.kid and sid<=a.sid and value=a.value) as num from test a
where value=(select min(value) from test where kid=a.kid)
) as y
on x.kid=y.kid
and x.num=y.num
create table test (
ID int IDENTITY(1,1), --自增长,主键
KID varchar(2),--课程编号,如"K1", "K2"
SID varchar(2), --学生编号,如"S1", "S2"
Value int,--学生成绩
TID varchar(2)--教师编号,如"T1", "T2"
)insert test (kid,sid,value)
select '10','22','100'
union all select '10','23','100'
union all select '10','24','13'
union all select '11','23','45'
union all select '11','24','65'
union all select '11','25','45'
go
select
x.kid,
x.value,
x.sid,
y.value,
y.sid
from (
select *,(select count(*) from test where kid=a.kid and sid<=a.sid and value=a.value) as num from test a
where value=(select max(value) from test where kid=a.kid)
) as x
full join (
select *,(select count(*) from test where kid=a.kid and sid<=a.sid and value=a.value) as num from test a
where value=(select min(value) from test where kid=a.kid)
) as y
on x.kid=y.kid
and x.num=y.num
以后再给分吧