一个表TB 有name,dept,age三列,dept列包涵计算机,信息,通信,age为年龄
我想查询的是其他系中比'信息'所有学生年龄都大的学生姓名,年龄并按降序排列
这是我写的,但有错
select AAA.name as sname,AAA.age as age
from TB AAA
where AAA.age=(select AAA.age from AAA where AAA.dept not in('计算机')) > MAX(select BBB.age from TB BBB where dept='信息')
order by AAA.age desc
go
我想查询的是其他系中比'信息'所有学生年龄都大的学生姓名,年龄并按降序排列
这是我写的,但有错
select AAA.name as sname,AAA.age as age
from TB AAA
where AAA.age=(select AAA.age from AAA where AAA.dept not in('计算机')) > MAX(select BBB.age from TB BBB where dept='信息')
order by AAA.age desc
go
where dept!='信息'
and not exists(select 1 from tb where dept='信息' and age>a.age)
and age>(select max(age) from tb where dept='信息') order by age desc
declare @TB table (name int,dept varchar(6),age int)
insert into @TB
select 1,'计算机',17 union all
select 2,'信息',19 union all
select 3,'计算机',20 union all
select 4,'信息',16 union all
select 9,'通信',15 union all
select 5,'通信',21 union all
select 6,'通信',22 union all
select 7,'通信',12SELECT name,age FROM @TB WHERE age>(
select MAX(age) from @TB WHERE dept='信息') and dept<>'信息'
/*
name age
----------- -----------
3 20
5 21
6 22
*/
insert into @TB
select 1,'计算机',17 union all
select 2,'信息',19 union all
select 3,'计算机',20 union all
select 4,'信息',16 union all
select 9,'通信',15 union all
select 5,'通信',21 union all
select 6,'通信',22 union all
select 7,'通信',12
--SQL:
select b.* from
(select age=MAX(age) from @TB where dept = '信息') a
cross apply
(select * from @TB where dept <> '信息' and age > a.age) b
/*
name dept age
3 计算机 20
5 通信 21
6 通信 22
*/
INSERT @TB
SELECT '苍井空',20,'计算机' UNION ALL
SELECT '小泽',24,'信息' UNION ALL
SELECT '饭岛爱',28,'通信' UNION ALL
SELECT '吉泽明步',26,'信息' UNION ALL
SELECT '松岛枫',19,'计算机' UNION ALL
SELECT '小泉彩',29,'通信'SELECT [name] ,
age
FROM @TB a
WHERE dept <> '信息'
AND NOT EXISTS ( SELECT 1
FROM @TB b
WHERE a.age < b.age
AND b.dept = '信息' )
name age
-------------------- -----------
饭岛爱 28
小泉彩 29(2 行受影响)
*
from
tb t
where
dept<>'信息'
and
not exists(select 1 from tb where dept='信息' and age>a.age)---可以看看执行计划
NOT EXISTS比较高 MAX也比较高