爱站程序员基地查询语句
#查询的进阶#子查询 一行一列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 cityWHERE Population >ALL(SELECT Population FROM city WHERE CountryCode=\'NLD\')ORDER BY Population ASC;SELECT CountryCode,NAME,Population FROM cityWHERE 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 tempLIMIT 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);
