mysql查询平均温度_Mysql查询应用

以下内容均整理自互联网，若有侵权可联系本人删除

1.分数排名

对Scores表(左二列)返回分数排名(右二列)，名词之间不可有间隔

Id

Score

Score

Rank

1

3.50

4.00

1

2

3.65

4.00

1

3

4.00

3.85

2

4

3.85

3.65

3

5

4.00

3.65

3

6

3.65

3.50

4

select Score,dense_rank() over(order by Score desc) as "Rank" from Scores;

函数

意义

rank()

排序返回间断型排名

dense_rank()

排序返回连续型排名

row_number

返回行号

2.连续出现的数字

对Logs表(左二列)返回至少连续出现3次的数字(右一列)

Id

Num

ConsecutiveNums

1

1

1

2

1

3

1

4

2

5

1

6

2

7

2

select distinct Num as ConsecutiveNums

from(

select num,

@times:=if(@prenum=num,@times:=@times+1,1) as times,

@prenum:=num

from

Logs,(

select @prenum:=null,

@times:=0

) as t

) as a

where a.times>2;

定义临时变量prenum表示前一数字，times表示连续出现次数

若前一数字来自于与本次数字相同则times加一，最后筛选times>2的结果；

3.超过经理收入的员工

对Employee表(左四列)返回员工工资大于其经理工资的员工姓名(右一列)

Id

Name

Salary

ManagerId

Employee

1

Joe

70000

3

Joe

2

Henry

80000

4

3

Sam

60000

Null

4

Max

90000

Null

select a.Name Employee from Employee a join Employee b where a.MangerId=b.Id and a.Salary > b.Salary;

内连接，将Employee表中员工工资与其经理工资进行关联

4.查找重复的电子邮箱

对Person表(左二列)返回所有重复的电子邮箱(右一列)

Id

Email

Email

1

a@b.com

a@b.com

2

c@d.com

3

a@b.com

select Email from Person group by Email having count(Email)>1;

5.从不订购的客户

对Customers表(左二列)和Orders表(中二列)返回从不订购任何东西的客户(右一列)

Id

Name

Id

CustomerId

Customers

1

Joe

1

3

Henry

2

Henry

2

1

Max

3

Sam

4

Max

select Name Customers from Customers left join Orders on Customers.Id=Orders.CustomerId where Orders.Id is null;

6.部门工资最高/前三高的员工

对Employee表(左四列)和Department表(中二列)返回各部门工资最高的员工信息(右三列)

Id

Name

Salary

Department

Id

Name

Department

Employee

Salary

1

Joe

70000

1

1

IT

IT

Max

90000

2

Henry

80000

2

2

Sales

Sales

Henry

80000

3

Sam

60000

2

4

Max

90000

1

select S.Department,S.Employee,S.Salary from(

select D.Name Department,E.Name Employee,E.Salary,

dense_rank() over(partition by E.DepartmentId order by E.Salary desc) rk

from Employee E inner join Department D on E.DepartmentId=D.Id

) as S where S.rk=1;

对Employee表(左四列)和Department表(中二列)返回各部门工资前三高的员工信息(右三列)

Id

Name

Salary

Department

Id

Name

Department

Employee

Salary

1

Joe

85000

1

1

IT

IT

Max

90000

2

Henry

80000

2

2

Sales

IT

Randy

85000

3

Sam

60000

2

IT

Joe

85000

4

Max

90000

1

IT

Will

70000

5

Janet

69000

1

Sales

Henry

80000

6

Randy

85000

1

Sales

Sam

60000

7

Will

70000

1

依旧使用以上程序，将条件语句改为S.rk<4即可

7.删除重复的电子邮箱

对Person表(左二列)删除重复邮箱并返回重复邮箱中Id最小的数据(右二列)

Id

Email

Id

Email

1

john@example.com

1

john@example.com

2

bob@example.com

2

bob@example.com

3

john@example.com

delete p1 from person p1,person p2 where p1.Email=p2.Email and p1.Id>p2.Id;

8.上升的温度

对Weather表(左三列)返回比上一天温度更高的Id(右一列)

Id(INT)

RecordeDate(DATE)

Temperature(INT)

Id

1

2015-01-01

10

2

2

2015-01-02

25

4

3

2015-01-03

20

4

2015-01-04

30

select w1.Id from Weather w1 inner join Weather w2 on datediff(w1.RecordDate,w2.RecordDate)=1

where w1.Temperature>w2.Temperature;

9.大的国家

对World表(左五列)返回面积超过300万或人口超过2500万的国家(右三列)

name

continent

area

population

gdp

name

population

area

Afghanistan

Asia

652230

25500100

20343000

Afghanistan

25500100

652230

Albania

Europe

28748

2831741

12960000

Algeria

37100000

2381741

Algeria

Africa

2381741

37100000

188681000

Andorra

Europe

468

78115

3712000

Angola

Africa

1246700

20609294

100990000

select name,population,area from World where area>3000000

union

select name,population,area from World where population>25000000;

使用union取并集较优于or的多条件筛选(？)

10.受欢迎的课

对courses表(左二列)返回超过或等于5名学生的课(右一列)

student

class

class

A

Math

Math

B

English

C

Math

D

Biology

E

Math

F

Computer

G

Math

H

Math

I

Math

select class from courses group by class having count(distinct student)>4;