# [SQL]中级SQL(4)

## 数据集

Schma和大部分SQL语句来自Prof. Alfons Kemper, Ph.D.的课件和书。

## 中级SQL

• 找出在所有`Diszipplin`都比`Bolt`好的运动员：
``````select distinct z.name
from zehnkampfd as z
where not exists(
select *
from zehnkampfd z2
where z2.name = z.name and exists(
select *
from zehnkampfd z3
where z2.punkte <= z3.punkte and z2.disziplin = z3.disziplin and z3.name = 'Bolt'
)
)``````

``````select *
from zehnkampfd z2
where  exists(
select *
from zehnkampfd z3
where z2.punkte <= z3.punkte and z2.disziplin = z3.disziplin and z3.name = 'Bolt'
)``````
``````    name     | disziplin  | punkte
-------------+------------+--------
Bolt        | 100m       |     50
Bolt        | Weitsprung |     50
Eaton       | 100m       |     40
Behrenbruch | 100m       |     30
Behrenbruch | Weitsprung |     50
(5 rows)``````

``````with better_as_bolt as (
select z.name, z.disziplin
from zehnkampfd z, zehnkampfd b
where z.punkte > b.punkte and z.disziplin = b.disziplin and b.name = 'Bolt'
), num_dis as (
select count(distinct disziplin) as num
from zehnkampfd
)

select distinct name
from better_as_bolt
group by name
having count(*) = (select num from num_dis)``````

• 搜索`100m`的冠军(冠军定义为：没有人比这个运动员更好)：
``````-- with correlated sub-query
select gold.name
from zehnkampfd gold
where gold.disziplin = '100m' and not exists(
select *
from zehnkampfd other
where other.disziplin = gold.disziplin  and gold.punkte < other.punkte
)``````

``````-- with correlated sub-query
select gold.name
from zehnkampfd gold
where gold.disziplin = '100m' and gold.name not in (
-- 存在有运动员比当前运动员更好的的情况
select z1.name
from zehnkampfd z1, zehnkampfd z2
where z1.disziplin = z2.disziplin and z1.disziplin = '100m' and z1.punkte < z2.punkte
)``````

``````-- 与数字比较
select gold.name
from zehnkampfd gold
where gold.disziplin = '100m' and gold.punkte = (select max(punkte) from zehnkampfd where disziplin = '100m')``````
• 搜索`100m`的亚军(亚军定义为：只存在一个运动员比当前运动员更好)：
``````select silver.name
from zehnkampfd silver
where silver.disziplin = '100m' and 1 = (
select count(*)
from zehnkampfd gold -- 这个是冠军，比我们想选的运动员更好，我们只允许这样的人出现一次
where gold.disziplin = '100m' and gold.punkte > silver.punkte
)``````
``````select silver.name
from zehnkampfd silver
where silver.disziplin = '100m' and exists(
select *
from zehnkampfd gold -- 这个是冠军，比我们想选的运动员更好，我们只允许这样的人出现一次
where gold.disziplin = '100m' and gold.punkte > silver.punkte and not exists(
select *
from zehnkampfd nobody -- 不再运行非冠军，比我们想选的运动员更好
where nobody.disziplin = '100m' and gold.name != nobody.name and nobody.punkte > silver.punkte)
)``````
• 搜索`100m`的季军(季军定义为：只存在两个运动员比当前运动员更好)：
``````select bronze.name
from zehnkampfd bronze
where bronze.disziplin = '100m' and 2 = (
select count(*)
from zehnkampfd other
where other.disziplin = '100m' and other.punkte > bronze.punkte
)``````
``````select bronze.name
from zehnkampfd bronze
where bronze.disziplin = '100m' and exists(
select *
from zehnkampfd gold, zehnkampfd silver
where gold.disziplin = '100m' and gold.punkte > bronze.punkte and
silver.disziplin = '100m' and silver.punkte > bronze.punkte and
gold.name != silver.name and not exists(
select *
from zehnkampfd nobody -- 不再允许非冠军和亚军
where nobody.disziplin = '100m' and gold.name != nobody.name and silver.name != nobody.name and nobody.punkte > bronze.punkte)
)``````

1 声望
1 粉丝
0 条评论