mysql学习-查询语句进阶_mysql查询进价超过2元的name列语句-优快云博客

本文链接：https://blog.youkuaiyun.com/whatname123/article/details/108360096

本文深入讲解了SQL子查询的各种应用场景，包括一行一列的简单条件判断、枚举查询、一行多列的比较操作以及多行多列的复杂查询。通过实际例子演示了如何使用子查询提高SQL查询效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

查询语句

#查询的进阶
#子查询 一行一列
SELECT NAME,Population FROM city WHERE Population>(SELECT Population FROM city WHERE NAME='Herat') ORDER BY Population ASC;
#子查询得到一行一列的结果 才可以做外部查询的条件判断
#子查询做枚举查询的条件
SELECT CountryCode,NAME,Population FROM city WHERE Population IN(SELECT Population FROM city WHERE CountryCode='NLD') ORDER BY Population ASC;
#子查询 一行多列
#all 高于所有,要高于数值最高的    any 高于部分,只要高于数值最低的就算 
SELECT CountryCode,NAME,Population FROM city 
WHERE Population >ALL(SELECT Population FROM city WHERE CountryCode='NLD') 
ORDER BY Population ASC;
SELECT CountryCode,NAME,Population FROM city 
WHERE Population >ANY(SELECT Population FROM city WHERE CountryCode='NLD') 
ORDER BY Population ASC;
#子查询 多行多列
SELECT NAME,Population FROM
(SELECT NAME,Population FROM city ORDER BY Population ASC) AS temp
LIMIT 0,5;
#聚合函数嵌套子查询 要用as取名提到临时表格
SELECT MAX(ttt) FROM (SELECT Population AS ttt FROM city WHERE CountryCode='NLD') AS aaa;
#合并查询 要求列数相同 列数据类型可以不同
#union 去除重复的数据 union all 不去除
(SELECT NAME,Population FROM city LIMIT 0,5) UNION (SELECT NAME,Population FROM country LIMIT 0,5);