选择每个班级中身高最高的两个人id name height class
1 gate 180 1
2 bob 168 1
3 sky 180 2
4 yadi 170 3
5 ppg 159 3
6 obama 180 2
7 bush 170 2
正解:select *
from ta a
where id in(select top 2 id from ta where class = a.class order by height desc) 我的解答 :select max2(name) from human group by class2.oracle 付费怎么回事 ?
3.选择每个班级中身高最高最低的这两个人 ?
1 gate 180 1
2 bob 168 1
3 sky 180 2
4 yadi 170 3
5 ppg 159 3
6 obama 180 2
7 bush 170 2
正解:select *
from ta a
where id in(select top 2 id from ta where class = a.class order by height desc) 我的解答 :select max2(name) from human group by class2.oracle 付费怎么回事 ?
3.选择每个班级中身高最高最低的这两个人 ?
去oracle 版块问下吧
(
id int identity(1,1) primary key,
name nvarchar(50),
height int,
class int
)
insert into #TT select 'gate',180,1
insert into #TT select 'bob',168,1
insert into #TT select 'sky',180,2
insert into #TT select 'yadi',170,3
insert into #TT select 'ppg',159,3
insert into #TT select 'obama',180,2
insert into #TT select 'bush',170,2select * from #TT T
where id in(select top 2 id from #TT where class=T.class order by height desc)id name height class
----------- -------------------------------------------------- ----------- -----------
1 gate 180 1
2 bob 168 1
3 sky 180 2
4 yadi 170 3
5 ppg 159 3
6 obama 180 2
create table #TT
(
id int identity(1,1) primary key,
name nvarchar(50),
height int,
class int
)
insert into #TT select 'gate',180,1
insert into #TT select 'bob',168,1
insert into #TT select 'sky',180,2
insert into #TT select 'yadi',170,3
insert into #TT select 'ppg',159,3
insert into #TT select 'obama',180,2
insert into #TT select 'bush',170,2select * from #TT T
where id in(select top 2 id from #TT where class=T.class order by height ASC)id name height class
----------- -------------------------------------------------- ----------- -----------
1 gate 180 1
2 bob 168 1
4 yadi 170 3
5 ppg 159 3
6 obama 180 2
7 bush 170 2
where id in(select top 2 id from #TT where class=T.class order by height ASC)-->之前是DESC
(
id int identity(1,1) primary key,
name nvarchar(50),
height int,
class int
)
insert into #TT select 'gate',180,1
insert into #TT select 'bob',168,1
insert into #TT select 'sky',180,2
insert into #TT select 'obama',173,2
insert into #TT select 'bush',170,2
insert into #TT select 'yadi',170,3
insert into #TT select 'ppg',159,3
insert into #TT select 'ppssg',160,3
select *
from #TT k
where not exists(select * from #TT where class=k.class and k.height<height)
or not exists(select * from #TT where class=k.class and k.height>height)
id name height class
----------- -------------------------------------------------- ----------- -----------
1 gate 180 1
2 bob 168 1
3 sky 180 2
5 bush 170 2
6 yadi 170 3
7 ppg 159 3
from ta a
where
id in(select top 2 id from ta where class = a.class order by height desc)
OR
id in(select top 2 id from ta where class = a.class order by height ASC)
呵呵
*
from
tb t
where
not exists(select * from tb where class=t.class and t.height<height)
or
not exists(select * from tb where class=t.class and t.height>height)