mysql 复杂统计_MYSQL复杂查询

1. 课堂练习

视图

#按课程汇总，统计每门课程报名的人数

CREATE VIEW 按课程汇总(课程号,人数)

AS

SELECT 课程号,COUNT(学号)

FROM score

GROUP BY 课程号;

#在此基础上筛选出人数小于3的课程号

SELECT 课程号

FROM`按课程汇总`

WHERE 人数<3;

子查询

#查询哪些学生的成绩 比 课程号为0002的全部成绩里的任意一个高呢

SELECT 学号,成绩

FROM score

WHERE 成绩>ANY(SELECT 成绩

FROM score

WHERE 课程号='0002');

#查询哪些学生的成绩 比 课程号为0002的全部成绩里的都高

SELECT 学号,成绩

FROM score

WHERE 成绩>ALL(

SELECT 成绩

FROM score

WHERE 课程号='0002');

标量子查询

#大于平均值的学生的学号和成绩

SELECT 学号,成绩

FROM score

WHERE 成绩>(

SELECT AVG(成绩)

FROM score);

#查询在差生平均成绩(小于60)和优等生(大于80)平均成绩之间的学生

SELECT 学号,成绩

FROM score

WHERE 成绩 BETWEEN (SELECT AVG(成绩) FROM score WHERE 成绩<60)

AND

(SELECT AVG(成绩) FROM score WHERE 成绩>80);

关联子查询

#查找出每个课程中大于对应课程平均成绩的学生

SELECT 学号,课程号,成绩

FROM score AS s1

WHERE 成绩>(SELECT AVG(成绩)

FROM score AS s2

WHERE s1.`课程号`=s2.`课程号`

GROUP BY 课程号);

(group by 可以不写，关联子查询中子句中的关联条件有分组的作用)

#查找出每个课程号最大成绩的所有信息

SELECT*

FROM score AS a

WHERE 成绩 IN( SELECT MAX(成绩)

FROM score AS b

WHERE b.`课程号`=a.`课程号`);

2. SQLzoo

数据来源

SELECT within SELECT Tutorial/zh​sqlzoo.net

#列出每個國家的名字 name，當中人口 population 是高於俄羅斯'Russia'的人口。

SELECT name

FROM world

WHERE population >

(SELECT population

FROM world

WHERE name='Russia')

#列出歐州每國家的人均GDP，當中人均GDP要高於英國'United Kingdom'的數值。

select name

from world

where continent='Europe' and gdp/population>(

select gdp/population

from world

where name='United Kingdom');

#在阿根廷Argentina 及 澳大利亞 Australia所在的洲份中，列出當中的國家名字 name 及洲分 continent 。按國字名字順序排序

select name,continent

from world

where continent in (

select continent

from world

where name in ('Argentina','Australia'))

order by name;

#哪一個國家的人口比加拿大Canada的多，但比波蘭Poland的少?列出國家名字name和人口population 。

select population,name

from world

where population > (

select population

from world

where name='Canada') and population

select population

from world

where name='Poland');

#Germany德國(人口8000萬)，在Europe歐洲國家的人口最多。Austria奧地利(人口850萬)擁有德國總人口的11％。顯示歐洲的國家名稱name和每個國家的人口population。以德國的人口的百分比作人口顯示。

select name,concat(round(100*population/(

select population

from world

where name='Germany'),0),'%')

from world

where continent='Europe';

#哪些國家的GDP比Europe歐洲的全部國家都要高呢? [只需列出 name 。] (有些國家的記錄中，GDP是NULL，沒有填入資料的。)

select name

from world

where gdp>all(

select gdp

from world

where continent='Europe' and gdp is not null);

#在每一個州中找出最大面積的國家，列出洲份 continent, 國家名字 name 及面積 area。 (有些國家的記錄中，AREA是NULL，沒有填入資料的。)

SELECT continent, name, area

FROM world as x

WHERE area= (

SELECT max(area)

FROM world as y

where x.continent=y.continent);

#列出洲份名稱，和每個洲份中國家名字按子母順序是排首位的國家名。(即每洲只有列一國)

法1：select continent,name

from world as x

where name=(

select name

from world as y

where x.continent=y.continent

order by name asc

limit 1);

法2：select continent, name

from world as x

where name <= all(

select name

from world as y

where y.continent=x.continent

group by continent);

法3：select continent, name

from world as x

where name <= (

select min(name)

from world as y

where y.continent=x.continent );

以上3个方法都得出同样的正确答案，<= all(子查询)即小于等于所有子查询结果，也就是小于等于子查询结果中的最小值，按照字母排序，默认为升序即a-z，题目要求查询排在首位的name也就是第一个开头是a的name，则在后两个方法中省略了排序过程

#找出洲份，當中全部國家都有少於或等於 25000000 人口. 在這些洲份中，列出國家名字name，continent 洲份和population人口。

select name,continent,population

from world as x

where 25000000>=all (

select population

from world as y

where y.continent= x.continent

group by continent);

#有些國家的人口是同洲份的所有其他國的3倍或以上。列出 國家名字name 和 洲份 continent

法1：select name,continent

from world as x

where population>=all(

select 3*population

from world as y

where x.continent=y.continent and x.name<>y.name

group by continent);

法2：select name,continent

from world as x

where population/3>=all(

select population

from world as y

where x.continent=y.continent and x.name<>y.name

注意：population>=3*all(子查询)是错误的书写方式，根据题意，国家不能和自己比较，因此需要将自己排除在外，即x.name<>y.name