数据检索-子查询(subselect)
chinayaosir
在不同的数据库,子查询实现是有差异的
1.语法:
1.1一般子查询
select select_list
from table_source
where columnname condition subselect_list
1.2嵌套子查询
select select_list
from table_source
where columnname condition(
select columnname
from table_source
where columnname condition(
select columnname
from table_source
where columnname condition))
1.3having子查询
select select_list
from table_source
group by grouplist
having columnname condition subselect_list
2.子查询条件:
subselect_condition子查询条件如下:
比较测试(>,>=,<,<=,=,<>)
组成员测试(in,not in)
存在测试(exists,not exists)
限定测试(any,all)
3.子查询例子
3.1子查询条件=
SELECT title
from books
here author_id =(SELECT id
FROM authors
WHERE last_name='Geisel'
and first_name='Theodor Seuss');
3.2子查询条件in
Select Film_Title
From Thespian_Film_Table
Where Thespian_Last_Name = "Ford"
and Thespian_First_Name = "Harrison"
and Film_Title in
(Select Film_Title
From Film_Table
Where Film_Year equals "1984");
3.3子查询条件not in
Select Film_Title
From Thespian_Film_Table
Where Thespian_Last_Name = "Ford"
and Thespian_First_Name = "Harrison"
and Film_Title not in
(Select Film_Title
From Film_Table
Where Film_Year equals "1984");
3.4子查询条件exists
Select Thespian_Film_Table.Film_Title
From Thespian_Film_Table
Where Thespian_Last_Name = "Ford"
and Thespian_First_Name = "Harrison"
and exists
(Select *
From Director_Film_Table
Where Director_Last_Name = "Lucas"
and Director_First_Name = "George"
and Director_Film_Table.Film_Title
= Thespian_Film_Table.Film_Title);
3.5子查询条件any
select name
from salesreps
where (quota * 0.1) < any (select amount
from orders
where rep=num)
3.6子查询having
select name,avg(amount)
from salesreps,orders
where empl_num=rep
and mfr='ACI'
group by name
having avg(amount) > (select avg(amount) from orders)
数据检索-子查询(subselect)
最新推荐文章于 2023-08-02 17:25:00 发布