case when then
等价于if xxx return xxx else xxx
就是按条件进行筛选
-- Identify the home team as Bayern Munich, Schalke 04, or neither
SELECT
CASE WHEN hometeam_id = 10189 THEN 'FC Schalke 04' #简单的来说就是按照条件进行查询
WHEN hometeam_id = 9823 THEN 'FC Bayern Munich'
ELSE 'Other' END AS home_team,
COUNT(id) AS total_matches
FROM matches_germany
-- Group by the CASE statement alias
GROUP BY home_team;
demo
SELECT
c.name AS country,
-- Count games from the 2012/2013 season
COUNT(CASE WHEN m.season = '2012/2013'
THEN m.id ELSE NULL END) AS matches_2012_2013
FROM country AS c
LEFT JOIN match AS m
ON c.id = m.country_id
-- Group by country name alias
GROUP BY country;
country matches_2012_2013
Portugal 240
France 380
Scotland 228
Netherlands 306
Spain 380
Belgium 240
Italy 380
Germany 306
England 380
Switzerland 180
Poland 240
Showing 11 out of 11 rows
SELECT
m.date,
--Select the team long name column and call it 'opponent'
t.team_long_name AS opponent,
-- Complete the CASE statement with an alias
CASE WHEN m.home_goal > m.away_goal THEN 'Home win!'
WHEN m.home_goal < m.away_goal THEN 'Home loss :('
ELSE 'Tie' END AS outcome
FROM matches_spain AS m
-- Left join teams_spain onto matches_spain
LEFT JOIN teams_spain AS t #两个列表根据id进行合并
ON m.awayteam_id = t.team_api_id;
date outcome
2012-01-21 Home loss :(
2012-01-22 Home win!
2012-01-22 Home loss :(
2012-01-23 Home win!
2012-01-21 Tie
2012-01-22 Tie
2012-01-21 Home loss :(
2012-01-21 Home win!
2012-01-22 Tie
2012-01-22 Home loss :(
2011-10-26 Tie
2011-10-27 Home win!
2011-10-26 Home loss :(
2011-10-26 Home win!
2011-10-27 Home win!
2011-10-26 Home win!
2011-10-25 Tie
2011-10-26 Home loss :(
2011-10-26 Home win!
2011-10-25 Home loss :(
2011-10-30 Home win!
2011-10-30 Home win!
2011-10-30 Tie
2011-10-29 Home loss :(
2011-10-30 Home win!
2011-10-29 Home win!
2011-10-31 Home loss :(
2011-10-29 Home win!
2011-10-30 Home win!
2011-10-29 Home win!
2011-11-06 Home win!
2011-11-06 Tie
2011-11-06 Home win!
2011-11-06 Tie
2011-11-05 Tie
2011-11-05 Home loss :(
2011-11-05 Tie
2011-11-06 Tie
2011-11-06 Home win!
2011-11-06 Tie
2011-11-20 Tie
2011-12-07 Tie
2011-11-20 Home win!
2011-11-19 Home win!
2011-11-20 Home loss :(
2011-11-19 Home loss :(
2011-11-21 Home loss :(
2011-11-19 Home win!
2011-11-20 Tie
2011-11-20 Home win!
2011-11-27 Home loss :(
2011-11-27 Home win!
2011-11-27 Home win!
2011-11-27 Home loss :(
2011-11-27 Home loss :(
2011-11-26 Home win!
2011-11-28 Home win!
2011-11-27 Home loss :(
2011-11-26 Home loss :(
2011-11-26 Home win!
2011-12-04 Home win!
2011-12-03 Home win!
2011-12-05 Home win!
2011-12-03 Home win!
2011-12-03 Home win!
2011-12-04 Home win!
2011-12-04 Tie
2011-12-03 Home loss :(
2011-12-04 Home win!
2011-12-04 Home win!
2011-12-11 Tie
2011-12-11 Home loss :(
2011-12-11 Home loss :(
2011-12-11 Tie
2011-12-11 Home win!
2011-12-11 Home win!
2011-12-10 Home win!
2011-12-10 Home win!
2011-12-11 Tie
2011-12-10 Home loss :(
2011-12-17 Home loss :(
2011-12-18 Tie
2011-12-18 Home win!
2011-12-18 Home loss :(
2011-12-17 Home loss :(
2011-12-18 Home win!
2011-12-17 Home win!
2011-11-29 Home win!
2011-12-18 Home win!
2011-12-17 Home loss :(
2012-01-08 Tie
2012-01-07 Tie
2012-01-07 Home win!
2012-01-08 Home win!
2012-01-08 Tie
2012-01-08 Home win!
2012-01-07 Tie
2012-01-08 Tie
2012-01-07 Tie
2012-01-07 Home win!
Showing 100 out of 380 rows
demo2
SELECT
date,
-- Identify the home team as Barcelona or Real Madrid
CASE WHEN hometeam_id = 8634 THEN 'FC Barcelona'
ELSE 'Real Madrid CF' END AS home,
-- Identify the away team as Barcelona or Real Madrid
CASE WHEN awayteam_id = 8634 THEN 'FC Barcelona'
ELSE 'Real Madrid CF' END AS away
FROM matches_spain
WHERE (awayteam_id = 8634 OR hometeam_id = 8634)
AND (awayteam_id = 8633 OR hometeam_id = 8633); -- where 后面一般填写各种条件，简单的条件
date home away
2011-12-10 Real Madrid CF FC Barcelona
2012-04-21 FC Barcelona Real Madrid CF
demo3
SELECT
date,
CASE WHEN hometeam_id = 8634 THEN 'FC Barcelona'
ELSE 'Real Madrid CF' END as home,
CASE WHEN awayteam_id = 8634 THEN 'FC Barcelona'
ELSE 'Real Madrid CF' END as away,
-- Identify all possible match outcomes
CASE WHEN home_goal > away_goal AND hometeam_id = 8634 THEN 'Barcelona win!'
WHEN home_goal > away_goal AND hometeam_id = 8633 THEN 'Real Madrid win!' -- 很多的筛选的条件
WHEN home_goal < away_goal AND awayteam_id = 8634 THEN 'Barcelona win!'
WHEN home_goal < away_goal AND awayteam_id = 8633 THEN 'Real Madrid win!'
ELSE 'Tie!' END AS outcome
FROM matches_spain
WHERE (awayteam_id = 8634 OR hometeam_id = 8634)
AND (awayteam_id = 8633 OR hometeam_id = 8633);
date home away outcome
2011-12-10 Real Madrid CF FC Barcelona Barcelona win!
2012-04-21 FC Barcelona Real Madrid CF Real Madrid win!
Filtering your CASE statement
过滤数据
-- Select team_long_name and team_api_id from team
SELECT
team_long_name,
team_api_id
FROM teams_italy
-- Filter by team long name
WHERE team_long_name = 'Bologna';
team_long_name team_api_id
Bologna 9857
demo1
-- Select the season and date columns
SELECT
season,
date,
-- Identify when Bologna won a match
CASE WHEN hometeam_id = 9857 AND home_goal > away_goal THEN 'Bologna Win'
WHEN awayteam_id = 9857 AND away_goal > home_goal THEN 'Bologna Win'
END AS outcome
FROM matches_italy;
demo2
-- Select the season, date, home_goal, and away_goal columns
SELECT
season,
date,
home_goal,
away_goal
FROM matches_italy
WHERE
-- Exclude games not won by Bologna
CASE WHEN hometeam_id = 9857 AND home_goal > away_goal THEN 'Bologna Win'
WHEN awayteam_id = 9857 AND away_goal > home_goal THEN 'Bologna Win'
END IS NOT NULL;
season date home_goal away_goal
2011/2012 2011-10-30 3 1
2011/2012 2011-12-04 1 0
2011/2012 2012-01-08 2 0
2011/2012 2012-02-21 2 0
2011/2012 2012-02-17 0 3
2011/2012 2012-04-12 1 0
2011/2012 2012-04-29 3 2
2011/2012 2012-05-02 0 1
2011/2012 2012-05-06 2 0
2011/2012 2011-10-16 0 2
2011/2012 2011-10-26 0 1
2012/2013 2012-11-18 3 0
2012/2013 2012-12-02 2 1
2012/2013 2012-12-16 2 3
2012/2013 2013-01-12 4 0
2012/2013 2013-02-03 2 3
2012/2013 2013-02-26 2 1
2012/2013 2013-03-03 3 0
2012/2013 2013-03-10 0 1
2012/2013 2012-09-16 2 3
2012/2013 2013-05-12 0 2
2012/2013 2012-09-30 4 0
2013/2014 2013-10-30 0 3
2013/2014 2013-12-22 1 0
2013/2014 2014-02-09 1 2
2013/2014 2014-03-23 1 0
2013/2014 2013-10-27 1 0
case when + 聚合函数
SELECT
c.name AS country,
-- Count matches in each of the 3 seasons
COUNT(CASE WHEN m.season = '2012/2013' THEN m.id END) AS matches_2012_2013,
COUNT(CASE WHEN m.season = '2013/2014' THEN m.id END) AS matches_2013_2014,
COUNT(CASE WHEN m.season = '2014/2015' THEN m.id END) AS matches_2014_2015
FROM country AS c
LEFT JOIN match AS m
ON c.id = m.country_id
-- Group by country name alias
GROUP BY country;
country matches_2012_2013
Portugal 240
France 380
Scotland 228
Netherlands 306
Spain 380
Belgium 240
Italy 380
Germany 306
England 380
Switzerland 180
Poland 240
Showing 11 out of 11 rows
AS 后面往往都是新增的列，或者说按照条件筛选出来的列
SELECT
c.name AS country,
-- Count matches in each of the 3 seasons
COUNT(CASE WHEN m.season = '2012/2013' THEN m.id END) AS matches_2012_2013,
COUNT(CASE WHEN m.season = '2013/2014' THEN m.id END) AS matches_2013_2014,
COUNT(CASE WHEN m.season = '2014/2015' THEN m.id END) AS matches_2014_2015
FROM country AS c
LEFT JOIN match AS m
ON c.id = m.country_id
-- Group by country name alias
GROUP BY country;
country matches_2012_2013 matches_2013_2014 matches_2014_2015
Portugal 240 240 306
France 380 380 380
Scotland 228 228 228
Netherlands 306 306 306
Spain 380 380 380
Belgium 240 12 240
Italy 380 380 379
Germany 306 306 306
England 380 380 380
Switzerland 180 180 180
Poland 240 240 240
Showing 11 out of 11 rows
demo2
case when + sum
SELECT
c.name AS country,
-- Sum the total records in each season where the home team won
SUM(CASE WHEN m.season = '2012/2013' AND m.home_goal > m.away_goal
THEN 1 ELSE 0 END) AS matches_2012_2013,
SUM(CASE WHEN m.season = '2013/2014' AND m.home_goal > m.away_goal
THEN 1 ELSE 0 END) AS matches_2013_2014,
SUM(CASE WHEN m.season = '2014/2015' AND m.home_goal > m.away_goal
THEN 1 ELSE 0 END) AS matches_2014_2015
FROM country AS c
LEFT JOIN match AS m
ON c.id = m.country_id
-- Group by country name alias
GROUP BY country;
country matches_2012_2013 matches_2013_2014 matches_2014_2015
Portugal 103 108 137
France 170 168 181
Scotland 89 102 102
Netherlands 137 144 138
Spain 189 179 171
Belgium 102 6 106
Italy 177 181 152
Germany 130 145 145
England 166 179 172
Switzerland 84 82 76
Poland 97 110 114
Showing 11 out of 11 rows
突然考虑可以做分箱和映射,就是映射和赋值
demo3
SELECT
c.name AS country,
-- Sum the home wins, away wins, and ties in each country
COUNT(CASE WHEN m.home_goal > m.away_goal THEN m.id
END) AS home_wins,
COUNT(CASE WHEN m.home_goal < m.away_goal THEN m.id
END) AS away_wins,
COUNT(CASE WHEN m.home_goal = m.away_goal THEN m.id
END) AS ties
FROM country AS c
LEFT JOIN matches AS m
ON c.id = m.country_id
GROUP BY country;
country home_wins away_wins ties
Portugal 245 156 145
France 349 215 196
Scotland 204 158 94
Netherlands 282 173 157
Spain 350 233 177
Belgium 112 78 62
Italy 333 216 210
Germany 290 176 146
England 351 238 171
Switzerland 158 113 89
Poland 224 117 139
Showing 11 out of 11 rows
demo4
SELECT
c.name AS country,
-- Round the percentage of tied games to 2 decimal points
ROUND(AVG(CASE WHEN m.season='2013/2014' AND m.home_goal = m.away_goal THEN 1
WHEN m.season='2013/2014' AND m.home_goal != m.away_goal THEN 0
END),2) AS pct_ties_2013_2014,
ROUND(AVG(CASE WHEN m.season='2014/2015' AND m.home_goal = m.away_goal THEN 1
WHEN m.season='2014/2015' AND m.home_goal != m.away_goal THEN 0
END),2) AS pct_ties_2014_2015
FROM country AS c
LEFT JOIN matches AS m
ON c.id = m.country_id
GROUP BY country;
country pct_ties_2013_2014 pct_ties_2014_2015
Portugal 0.25 0.28
France 0.28 0.23
Scotland 0.22 0.19
Netherlands 0.27 0.24
Spain 0.23 0.24
Belgium 0.17 0.25
Italy 0.24 0.32
Germany 0.21 0.27
England 0.21 0.24
Switzerland 0.23 0.27
Poland 0.30 0.28
Showing 11 out of 11 rows
WHERE are the Subqueries?
子查询,或者说嵌套查询，这个非常的重要
通常在选择过滤信息之前对数据进行转化会需要用到子查询
where中的子查询
SELECT
-- Select the average of home + away goals, multiplied by 3
3 * AVG(home_goal + away_goal)
FROM matches_2013_2014;
顺序是先走where的子查询再走外层
SELECT
-- Select the date, home goals, and away goals scored
date,
home_goal,
away_goal
FROM matches_2013_2014
-- Filter for matches where total goals exceeds 3x the average
WHERE (home_goal + away_goal) >
(SELECT 3 * AVG(home_goal + away_goal)
FROM matches_2013_2014);
date home_goal away_goal
2013-12-14 6 3
2014-03-22 3 6
2013-10-30 7 3
demo2
SELECT
-- Select the team long and short names
team_long_name,
team_short_name
FROM team
-- Exclude all values from the subquery
WHERE team_api_id NOT IN
(SELECT DISTINCT hometeam_id FROM match);
demo3
SELECT
-- Select the team long and short names
team_long_name,
team_short_name
FROM team
-- Filter for teams with 8 or more home goals
WHERE team_api_id in
(SELECT hometeam_ID
FROM match
WHERE home_goal >= 8);
比较重要的是得知道怎么进行过滤
Subqueries in FROM
from之后的子查询
嵌套在from中的子查询
这个地方的子查询就相当于一个表，临时表，即表T。
也就是说，外面的select，其查询的内容就是括号内的子查询返回的数据集
SELECT
-- Select country name and the count match IDs
c.name AS country_name,
COUNT(sub.id) AS matches
FROM country AS c
-- Inner join the subquery onto country
-- Select the country id and match id columns
INNER JOIN (SELECT country_id, id
FROM match
-- Filter the subquery by matches with 10+ goals
WHERE (home_goal + away_goal) >= 10) AS sub
ON c.id = sub.country_id
GROUP BY country_name;
country_name matches
Netherlands 1
Spain 4
Germany 1
England 3
demo2
SELECT
-- Select country, date, home, and away goals from the subquery
country,
date,
home_goal,
away_goal
FROM
-- Select country name, date, and total goals in the subquery
(SELECT c.name AS country,
m.date,
m.home_goal,
m.away_goal,
(m.home_goal + m.away_goal) AS total_goals
FROM match AS m
LEFT JOIN country AS c
ON m.country_id = c.id) AS subquery
-- Filter by total goals scored in the main query
WHERE total_goals >= 10;
子查询
SELECT
l.name AS league,
-- Select and round the league's total goals
ROUND(AVG(m.home_goal + m.away_goal),2) AS avg_goals,
-- Select and round the average total goals
(SELECT ROUND(AVG(home_goal + away_goal),2)
FROM match
WHERE season = '2013/2014') AS overall_avg
FROM league AS l
LEFT JOIN match AS m
ON l.country_id = m.country_id
-- Filter for the 2013/2014 season
WHERE m.season = '2013/2014'
GROUP BY l.name;
demo2
SELECT
-- Select the league name and average goals scored
l.name AS league,
ROUND(AVG(m.home_goal + m.away_goal),2) AS avg_goals,
-- Subtract the overall average from the league average
ROUND(AVG(m.home_goal + m.away_goal) -
(SELECT AVG(home_goal + away_goal)
FROM match
WHERE season = '2013/2014'),2) AS diff
FROM league AS l
LEFT JOIN match AS m
ON l.country_id = m.country_id
-- Only include 2013/2014 results
WHERE m.season = '2013/2014'
GROUP BY l.name;
league avg_goals diff
Switzerland Super League 2.89 0.12
Poland Ekstraklasa 2.64 -0.13
Netherlands Eredivisie 3.20 0.43
Scotland Premier League 2.75 -0.02
France Ligue 1 2.46 -0.31
Spain LIGA BBVA 2.75 -0.02
Germany 1. Bundesliga 3.16 0.39
Italy Serie A 2.72 -0.04
Portugal Liga ZON Sagres 2.37 -0.40
England Premier League 2.77 0.00
Belgium Jupiler League
\SELECT
-- Select the stage and average goals from the subquery
s.stage,
ROUND(s.avg_goals,2) AS avg_goals
FROM
-- Select the stage and average goals in 2012/2013
(SELECT
stage,
AVG(home_goal + away_goal) AS avg_goals
FROM match
WHERE season = '2012/2013'
GROUP BY stage) AS s
WHERE
-- Filter the main query using the subquery
s.avg_goals > (SELECT AVG(home_goal + away_goal)
FROM match WHERE season = '2012/2013');
demo3
SELECT
-- Select the stage and average goals from s
s.stage,
ROUND(s.avg_goals,2) AS avg_goal,
-- Select the overall average for 2012/2013
(SELECT AVG(home_goal + away_goal) FROM match WHERE season = '2012/2013') AS overall_avg
FROM
-- Select the stage and average goals in 2012/2013 from match
(SELECT
stage,
AVG(home_goal + away_goal) AS avg_goals
FROM match
WHERE season = '2012/2013'
GROUP BY stage) AS s
WHERE
-- Filter the main query using the subquery
s.avg_goals > (SELECT AVG(home_goal + away_goal)
FROM match WHERE season = '2012/2013');
stage avg_goal overall_avg
4 2.80 2.7726993865030675
10 2.96 2.7726993865030675
38 3.17 2.7726993865030675
6 2.78 2.7726993865030675
12 3.23 2.7726993865030675
36 2.90 2.7726993865030675
31 3.06 2.7726993865030675
30 2.87 2.7726993865030675
21 2.90 2.7726993865030675
3 2.83 2.7726993865030675
17 2.85 2.7726993865030675
20 2.96 2.7726993865030675
33 3.10 2.7726993865030675
27 2.80 2.7726993865030675
23 3.01 2.7726993865030675
8 3.09 2.7726993865030675
11 2.92 2.7726993865030675