MySQL Cookbook 学习笔记-03
生活随笔
收集整理的這篇文章主要介紹了
MySQL Cookbook 学习笔记-03
小編覺得挺不錯的,現(xiàn)在分享給大家,幫大家做個參考.
1、INNER JOIN關(guān)聯(lián)查詢
2、outer join（LEFT JOIN 與 RIGHT JOIN）
3、自連接
4、主從表查詢
5、在分組內(nèi)查找某列最大或最小的一行
6、計算小組積分榜
7、計算連續(xù)行的差
8、計算“累計和”與運行時平均值
9、使用 JOIN 控制查詢結(jié)果的順序
10、通過 UNION 合并查詢結(jié)果集
———————————————————————————————————————————
1、INNER JOIN關(guān)聯(lián)查詢
（Recipe 12.1. Finding Rows in One Table That Match Rows in Another）
CREATE TABLE artist (a_id INT UNSIGNED NOT NULL AUTO_INCREMENT, # artist IDname VARCHAR(30) NOT NULL, # artist namePRIMARY KEY (a_id),UNIQUE (name) );CREATE TABLE painting (a_id INT UNSIGNED NOT NULL, # artist IDp_id INT UNSIGNED NOT NULL AUTO_INCREMENT, # painting IDtitle VARCHAR(100) NOT NULL, # title of paintingstate VARCHAR(2) NOT NULL, # state where purchasedprice INT UNSIGNED, # purchase price (dollars)INDEX (a_id),PRIMARY KEY (p_id) );表的數(shù)據(jù)：
mysql> SELECT * FROM artist ORDER BY a_id; +------+----------+ | a_id | name | +------+----------+ | 1 | Da Vinci | | 2 | Monet | | 3 | Van Gogh | | 4 | Picasso | | 5 | Renoir | +------+----------+ mysql> SELECT * FROM painting ORDER BY a_id, p_id; +------+------+-------------------+-------+-------+ | a_id | p_id | title | state | price | +------+------+-------------------+-------+-------+ | 1 | 1 | The Last Supper | IN | 34 | | 1 | 2 | The Mona Lisa | MI | 87 | | 3 | 3 | Starry Night | KY | 48 | | 3 | 4 | The Potato Eaters | KY | 67 | | 3 | 5 | The Rocks | IA | 33 | | 5 | 6 | Les Deux Soeurs | NE | 64 | +------+------+-------------------+-------+-------+ 方案一：通過 where 語句關(guān)聯(lián)查詢 mysql> SELECT * FROM artist, painting-> WHERE artist.a_id = painting.a_id; 方案二：通過 INNER JOIN .... ON 關(guān)聯(lián)查詢（等價方案一） mysql> SELECT * FROM artist INNER JOIN painting-> ON artist.a_id = painting.a_id; +------+----------+------+------+-------------------+-------+-------+ | a_id | name | a_id | p_id | title | state | price | +------+----------+------+------+-------------------+-------+-------+ | 1 | Da Vinci | 1 | 1 | The Last Supper | IN | 34 | | 1 | Da Vinci | 1 | 2 | The Mona Lisa | MI | 87 | | 3 | Van Gogh | 3 | 3 | Starry Night | KY | 48 | | 3 | Van Gogh | 3 | 4 | The Potato Eaters | KY | 67 | | 3 | Van Gogh | 3 | 5 | The Rocks | IA | 33 | | 5 | Renoir | 5 | 6 | Les Deux Soeurs | NE | 64 | +------+----------+------+------+-------------------+-------+-------+ 方案三：通過 INNER JOIN ... USING 關(guān)聯(lián)查詢（關(guān)聯(lián)兩表的列必須相同） mysql> SELECT * FROM artist INNER JOIN painting-> USING(a_id); +------+----------+------+-------------------+-------+-------+ | a_id | name | p_id | title | state | price | +------+----------+------+-------------------+-------+-------+ | 1 | Da Vinci | 1 | The Last Supper | IN | 34 | | 1 | Da Vinci | 2 | The Mona Lisa | MI | 87 | | 3 | Van Gogh | 3 | Starry Night | KY | 48 | | 3 | Van Gogh | 4 | The Potato Eaters | KY | 67 | | 3 | Van Gogh | 5 | The Rocks | IA | 33 | | 5 | Renoir | 6 | Les Deux Soeurs | NE | 64 | +------+----------+------+-------------------+-------+-------+ 推薦用法：JOIN 語句來關(guān)聯(lián)表，where 子句作為篩選條件 mysql> SELECT artist.name, painting.title, painting.state, painting.price-> FROM artist INNER JOIN painting-> ON artist.a_id = painting.a_id-> WHERE painting.state = 'KY'; +----------+-------------------+-------+-------+ | name | title | state | price | +----------+-------------------+-------+-------+ | Van Gogh | Starry Night | KY | 48 | | Van Gogh | The Potato Eaters | KY | 67 | +----------+-------------------+-------+-------+ 三表關(guān)聯(lián)示例： mysql> SELECT artist.name, painting.title, states.name, painting.price-> FROM artist INNER JOIN painting INNER JOIN states-> ON artist.a_id = painting.a_id AND painting.state = states.abbrev; +----------+-------------------+----------+-------+ | name | title | name | price | +----------+-------------------+----------+-------+ | Da Vinci | The Last Supper | Indiana | 34 | | Da Vinci | The Mona Lisa | Michigan | 87 | | Van Gogh | Starry Night | Kentucky | 48 | | Van Gogh | The Potato Eaters | Kentucky | 67 | | Van Gogh | The Rocks | Iowa | 33 | | Renoir | Les Deux Soeurs | Nebraska | 64 | +----------+-------------------+----------+-------+ 關(guān)聯(lián)查詢和聚合函數(shù)——示例 mysql> SELECT artist.name,-> COUNT(*) AS 'number of paintings',-> SUM(painting.price) AS 'total price',-> AVG(painting.price) AS 'average price'-> FROM artist INNER JOIN painting ON artist.a_id = painting.a_id-> GROUP BY artist.name; +----------+---------------------+-------------+---------------+ | name | number of paintings | total price | average price | +----------+---------------------+-------------+---------------+ | Da Vinci | 2 | 121 | 60.5000 | | Renoir | 1 | 64 | 64.0000 | | Van Gogh | 3 | 148 | 49.3333 | +----------+---------------------+-------------+---------------+ 特別注意：為了提高聯(lián)合查詢的效率，一般將聯(lián)合的列（如：a_id）設(shè)定為索引！TABLE artist，a_id 為主鍵（強制索引）
TABLE painting，a_id 直接設(shè)定為索引
2、outer join（LEFT JOIN 與 RIGHT JOIN）
查詢在表painting 中沒有作品的作者：
mysql> select artist.a_id, artist.name from artist-> left join painting on artist.a_id = painting.a_id-> where painting.a_id is null; +------+---------+ | a_id | name | +------+---------+ | 2 | Monet | | 4 | Picasso | +------+---------+其它方法（非 JOIN） mysql> select artist.a_id, artist.name from artist-> where artist.a_id not in (-> select painting.a_id from painting-> ); +------+---------+ | a_id | name | +------+---------+ | 2 | Monet | | 4 | Picasso | +------+---------+LEFT JOIN 和 RIGHT JOIN 的相互轉(zhuǎn)換： mysql> SELECT artist.name,-> IF(COUNT(painting.a_id)>0,'yes','no') AS 'in collection'-> FROM artist LEFT JOIN painting ON artist.a_id = painting.a_id-> GROUP BY artist.name; +----------+---------------+ | name | in collection | +----------+---------------+ | Da Vinci | yes | | Monet | no | | Picasso | no | | Renoir | yes | | Van Gogh | yes | +----------+---------------+ mysql> SELECT artist.name,-> IF(COUNT(painting.a_id)>0,'yes','no') AS 'in collection'-> FROM painting RIGHT JOIN artist ON artist.a_id = painting.a_id-> GROUP BY artist.name; +----------+---------------+ | name | in collection | +----------+---------------+ | Da Vinci | yes | | Monet | no | | Picasso | no | | Renoir | yes | | Van Gogh | yes | +----------+---------------+3、自連接
任務(wù)：查找作品為“The Potato Eaters”的作者的所有作品。
mysql> select p2.title-> from painting as p1 inner join painting as p2-> on p1.a_id = p2.a_id-> where p1.title = 'The Potato Eaters'; +-------------------+ | title | +-------------------+ | Starry Night | | The Potato Eaters | | The Rocks | +-------------------+替換方法（嵌套查詢） mysql> select title from painting-> where painting.a_id = (-> select a_id from painting where title = 'The Potato Eaters'-> ); +-------------------+ | title | +-------------------+ | Starry Night | | The Potato Eaters | | The Rocks | +-------------------+4、主從表查詢
a、只查詢從表中有數(shù)據(jù)的“主-從”關(guān)聯(lián)查詢，用 INNER ?JOIN
b、查詢主表中所有數(shù)據(jù)，從表可以沒有，用 LEFT ?JOIN
a——示例
mysql> select artist.name, painting.title-> from artist INNER JOIN painting ON artist.a_id = painting.a_id-> order by name, title; +----------+-------------------+ | name | title | +----------+-------------------+ | Da Vinci | The Last Supper | | Da Vinci | The Mona Lisa | | Renoir | Les Deux Soeurs | | Van Gogh | Starry Night | | Van Gogh | The Potato Eaters | | Van Gogh | The Rocks | +----------+-------------------+帶聚集函數(shù)——示例
mysql> select artist.name as painter, count(painting.a_id) as count-> from artist INNER JOIN painting ON artist.a_id = painting.a_id-> group by artist.name; +----------+-------+ | painter | count | +----------+-------+ | Da Vinci | 2 | | Renoir | 1 | | Van Gogh | 3 | +----------+-------+ b——示例 mysql> select artist.name, painting.title-> from artist LEFT JOIN painting ON artist.a_id = painting.a_id-> order by name, title; +----------+-------------------+ | name | title | +----------+-------------------+ | Da Vinci | The Last Supper | | Da Vinci | The Mona Lisa | | Monet | NULL | | Picasso | NULL | | Renoir | Les Deux Soeurs | | Van Gogh | Starry Night | | Van Gogh | The Potato Eaters | | Van Gogh | The Rocks | +----------+-------------------+帶聚集函數(shù)——示例 mysql> select a.a_id, a.name, p.a_id, count(p.a_id) as count-> from artist as a LEFT JOIN painting as p ON a.a_id = p.a_id-> group by a.name; +------+----------+------+-------+ | a_id | name | a_id | count | +------+----------+------+-------+ | 1 | Da Vinci | 1 | 2 | | 2 | Monet | NULL | 0 | | 4 | Picasso | NULL | 0 | | 5 | Renoir | 5 | 1 | | 3 | Van Gogh | 3 | 3 | +------+----------+------+-------+ 聚集函數(shù)會忽略 NULL 值，count( ) 會將 NULL 值操作返回0！例如：
mysql> select * from taxpayer; +---------+--------+ | name | id | +---------+--------+ | bernina | 198-48 | | bertha | NULL | | ben | NULL | | NULL | 475-83 | | baidu | 111+55 | +---------+--------+mysql> select count(id) from taxpayer-> where name='ben'; +-----------+ | count(id) | +-----------+ | 0 | +-----------+其他聚集函數(shù)（如，SUM( )，AVG( )）就必須做特殊處理： mysql> select a.name as painter,-> count(p.a_id) as 'number of paintings',-> sum(p.price) as 'total price',-> avg(p.price) as 'average price'-> from artist as a LEFT JOIN painting as p ON a.a_id = p.a_id-> group by a.name; +----------+---------------------+-------------+---------------+ | painter | number of paintings | total price | average price | +----------+---------------------+-------------+---------------+ | Da Vinci | 2 | 121 | 60.5000 | | Monet | 0 | NULL | NULL | | Picasso | 0 | NULL | NULL | | Renoir | 1 | 64 | 64.0000 | | Van Gogh | 3 | 148 | 49.3333 | +----------+---------------------+-------------+---------------+這里的 SUM( ) 和 AVG( )函數(shù)返回的都為 NULL ，顯然不合理，修改如下： mysql> select a.name as painter,-> count(p.a_id) as 'number of paintings',-> IFNULL(sum(p.price),0) as 'total price',-> IFNULL(avg(p.price),0) as 'average price'-> from artist as a LEFT JOIN painting as p ON a.a_id = p.a_id-> group by a.name; +----------+---------------------+-------------+---------------+ | painter | number of paintings | total price | average price | +----------+---------------------+-------------+---------------+ | Da Vinci | 2 | 121 | 60.5000 | | Monet | 0 | 0 | 0.0000 | | Picasso | 0 | 0 | 0.0000 | | Renoir | 1 | 64 | 64.0000 | | Van Gogh | 3 | 148 | 49.3333 | +----------+---------------------+-------------+---------------+5、在分組內(nèi)查找某列最大或最小的一行
（Recipe 12.6. Finding Rows Containing Per-Group Minimum or Maximum Values）
查找全表某列最大或最小的行，如下方法合適：
mysql> select a.name, p.title, p.price-> from artist as a INNER JOIN painting as p-> ON p.a_id = a.a_id-> where p.price = (select max(price) from painting); +----------+---------------+-------+ | name | title | price | +----------+---------------+-------+ | Da Vinci | The Mona Lisa | 87 | +----------+---------------+-------+查找 分組內(nèi)某列最大或最小的行， 錯誤方法（ 我的）：mysql> select a.name, p.title, max(p.price)-> from artist as a INNER JOIN painting as p-> ON a.a_id = p.a_id-> group by a.name; +----------+-----------------+--------------+ | name | title | max(p.price) | +----------+-----------------+--------------+ | Da Vinci | The Last Supper | 87 | | Renoir | Les Deux Soeurs | 64 | | Van Gogh | Starry Night | 67 | +----------+-----------------+--------------+分析原因，看兩表連接查詢數(shù)據(jù)： mysql> select a.name, p.title, p.price-> from artist as a INNER JOIN painting as p-> ON a.a_id = p.a_id; +----------+-------------------+-------+ | name | title | price | +----------+-------------------+-------+ | Da Vinci | The Last Supper | 34 | | Da Vinci | The Mona Lisa | 87 | | Renoir | Les Deux Soeurs | 64 | | Van Gogh | Starry Night | 48 | | Van Gogh | The Potato Eaters | 67 | | Van Gogh | The Rocks | 33 | +----------+-------------------+-------+總結(jié)：雖然 我的方法中“name”列和“max(price)”列都是正確的，但是“title”列卻是任意的！
正確方法：
a、單獨創(chuàng)建臨時表，與臨時表關(guān)聯(lián)查詢
b、同過 FROM 子句連接查詢
c、通過自連接連接查詢（比較難懂）
a——示例
mysql> create table tmp-> select a_id, max(price) as max_price from painting group by a_id;//演示用-START mysql> select * from tmp; +------+-----------+ | a_id | max_price | +------+-----------+ | 1 | 87 | | 3 | 67 | | 5 | 64 | +------+-----------+ //演示用-ENDmysql> select a.name, p.title, p.price-> from artist as a INNER JOIN painting as p INNER JOIN tmp as t-> ON a.a_id = p.a_id-> AND p.a_id = t.a_id-> AND p.price = t.max_price; +----------+-------------------+-------+ | name | title | price | +----------+-------------------+-------+ | Da Vinci | The Mona Lisa | 87 | | Van Gogh | The Potato Eaters | 67 | | Renoir | Les Deux Soeurs | 64 | +----------+-------------------+-------+b——示例 mysql> select a.name, p.title, p.price-> from artist as a INNER JOIN painting as p-> INNER JOIN (select a_id, max(price) as max_price from painting group by a_id) as tmp-> ON a.a_id = p.a_id-> AND p.a_id = tmp.a_id-> AND p.price = tmp.max_price; +----------+-------------------+-------+ | name | title | price | +----------+-------------------+-------+ | Da Vinci | The Mona Lisa | 87 | | Van Gogh | The Potato Eaters | 67 | | Renoir | Les Deux Soeurs | 64 | +----------+-------------------+-------+c——示例 mysql> select a.name, p1.title, p1.price-> from painting as p1 LEFT JOIN painting as p2-> ON p1.a_id = p2.a_id-> AND p1.price < p2.price-> INNER JOIN artist as a-> ON p1.a_id = a.a_id-> WHERE p2.a_id is null; +----------+-------------------+-------+ | name | title | price | +----------+-------------------+-------+ | Da Vinci | The Mona Lisa | 87 | | Renoir | Les Deux Soeurs | 64 | | Van Gogh | The Potato Eaters | 67 | +----------+-------------------+-------+6、計算小組積分榜
（Recipe 12.7. Computing Team Standings）
如下數(shù)據(jù)：
mysql> SELECT team, wins, losses FROM standings1-> ORDER BY wins-losses DESC; +-------------+------+--------+ | team | wins | losses | +-------------+------+--------+ | Winnipeg | 37 | 20 | | Crookston | 31 | 25 | | Fargo | 30 | 26 | | Grand Forks | 28 | 26 | | Devils Lake | 19 | 31 | | Cavalier | 15 | 32 | +-------------+------+--------+計算獲勝的比例公式： wins / (wins + losses)為了預(yù)防還沒有一場比賽的情況，公式增強改為：IF( wins=0, 0, wins/(wins+losses) )
Game Behind 公式：( (winsA - lossesA) - (winsB - lossesB) ) / 2
給定一個小組（其實就是“Winnipeg”小組）作為比較的基準：
mysql> SET @wl_diff = (SELECT MAX(wins-losses) FROM standings1);查詢計算結(jié)果為： mysql> SELECT team, wins AS W, losses AS L,-> wins/(wins+losses) AS PCT,-> (@wl_diff - (wins-losses)) / 2 AS GB-> FROM standings1-> ORDER BY wins-losses DESC, PCT DESC; +-------------+------+------+--------+---------+ | team | W | L | PCT | GB | +-------------+------+------+--------+---------+ | Winnipeg | 37 | 20 | 0.6491 | 0.0000 | | Crookston | 31 | 25 | 0.5536 | 5.5000 | | Fargo | 30 | 26 | 0.5357 | 6.5000 | | Grand Forks | 28 | 26 | 0.5185 | 7.5000 | | Devils Lake | 19 | 31 | 0.3800 | 14.5000 | | Cavalier | 15 | 32 | 0.3191 | 17.0000 | +-------------+------+------+--------+---------+改進： mysql> SELECT team, wins AS W, losses AS L,-> TRUNCATE(wins/(wins+losses),3) AS PCT,-> IF(@wl_diff = wins-losses,-> '-',TRUNCATE((@wl_diff - (wins-losses))/2,1)) AS GB-> FROM standings1-> ORDER BY wins-losses DESC, PCT DESC; +-------------+------+------+-------+------+ | team | W | L | PCT | GB | +-------------+------+------+-------+------+ | Winnipeg | 37 | 20 | 0.649 | - | | Crookston | 31 | 25 | 0.553 | 5.5 | | Fargo | 30 | 26 | 0.535 | 6.5 | | Grand Forks | 28 | 26 | 0.518 | 7.5 | | Devils Lake | 19 | 31 | 0.380 | 14.5 | | Cavalier | 15 | 32 | 0.319 | 17.0 | +-------------+------+------+-------+------+7、計算連續(xù)行的差
（Recipe 12.9. Calculating Successive-Row Differences）
mysql> SELECT seq, city, miles FROM trip_log ORDER BY seq; +-----+------------------+-------+ | seq | city | miles | +-----+------------------+-------+ | 1 | San Antonio, TX | 0 | | 2 | Dallas, TX | 263 | | 3 | Benton, AR | 566 | | 4 | Memphis, TN | 745 | | 5 | Portageville, MO | 878 | | 6 | Champaign, IL | 1164 | | 7 | Madison, WI | 1412 | +-----+------------------+-------+ 計算連續(xù)兩城市的距離（ dist）： mysql> SELECT t1.seq AS seq1, t2.seq AS seq2,-> t1.city AS city1, t2.city AS city2,-> t1.miles AS miles1, t2.miles AS miles2,-> t2.miles-t1.miles AS dist-> FROM trip_log AS t1 INNER JOIN trip_log AS t2-> ON t1.seq+1 = t2.seq-> ORDER BY t1.seq; +------+------+------------------+------------------+--------+--------+------+ | seq1 | seq2 | city1 | city2 | miles1 | miles2 | dist | +------+------+------------------+------------------+--------+--------+------+ | 1 | 2 | San Antonio, TX | Dallas, TX | 0 | 263 | 263 | | 2 | 3 | Dallas, TX | Benton, AR | 263 | 566 | 303 | | 3 | 4 | Benton, AR | Memphis, TN | 566 | 745 | 179 | | 4 | 5 | Memphis, TN | Portageville, MO | 745 | 878 | 133 | | 5 | 6 | Portageville, MO | Champaign, IL | 878 | 1164 | 286 | | 6 | 7 | Champaign, IL | Madison, WI | 1164 | 1412 | 248 | +------+------+------------------+------------------+--------+--------+------+8、計算“累計和”與運行時平均值
（Recipe 12.10. Finding Cumulative Sums and Running Averages）
mysql> SELECT date, precip FROM rainfall ORDER BY date; +------------+--------+ | date | precip | +------------+--------+ | 2006-06-01 | 1.50 | | 2006-06-02 | 0.00 | | 2006-06-03 | 0.50 | | 2006-06-04 | 0.00 | | 2006-06-05 | 1.00 | +------------+--------+ 計算每天的累計降雨量（ 自連接）： mysql> SELECT t1.date, t1.precip AS 'daily precip',-> SUM(t2.precip) AS 'cum. precip'-> FROM rainfall AS t1 INNER JOIN rainfall AS t2-> ON t1.date >= t2.date-> GROUP BY t1.date; +------------+--------------+-------------+ | date | daily precip | cum. precip | +------------+--------------+-------------+ | 2006-06-01 | 1.50 | 1.50 | | 2006-06-02 | 0.00 | 1.50 | | 2006-06-03 | 0.50 | 2.00 | | 2006-06-04 | 0.00 | 2.00 | | 2006-06-05 | 1.00 | 3.00 | +------------+--------------+-------------+ 一般化： mysql> SELECT t1.date, t1.precip AS 'daily precip',-> SUM(t2.precip) AS 'cum. precip',-> COUNT(t2.precip) AS 'days elapsed',-> AVG(t2.precip) AS 'avg. precip'-> FROM rainfall AS t1 INNER JOIN rainfall AS t2-> ON t1.date >= t2.date-> GROUP BY t1.date; +------------+--------------+-------------+--------------+-------------+ | date | daily precip | cum. precip | days elapsed | avg. precip | +------------+--------------+-------------+--------------+-------------+ | 2006-06-01 | 1.50 | 1.50 | 1 | 1.500000 | | 2006-06-02 | 0.00 | 1.50 | 2 | 0.750000 | | 2006-06-03 | 0.50 | 2.00 | 3 | 0.666667 | | 2006-06-04 | 0.00 | 2.00 | 4 | 0.500000 | | 2006-06-05 | 1.00 | 3.00 | 5 | 0.600000 | +------------+--------------+-------------+--------------+-------------+ 如果日期不連續(xù)呢（如，刪除“precip”的值為 0 ，的行） mysql> DELETE FROM rainfall WHERE precip = 0; mysql> SELECT date, precip FROM rainfall ORDER BY date; +------------+--------+ | date | precip | +------------+--------+ | 2006-06-01 | 1.50 | | 2006-06-03 | 0.50 | | 2006-06-05 | 1.00 | +------------+--------+ 計算累計天數(shù) =? DATEDIFF( MAX(t2.date), MIN(t2.date) ) + 1計算平均降雨量 = 總的降雨量 / 累計天數(shù)
mysql> SELECT t1.date, t1.precip AS 'daily precip',-> SUM(t2.precip) AS 'cum. precip',-> DATEDIFF(MAX(t2.date),MIN(t2.date)) + 1 AS 'days elapsed',-> SUM(t2.precip) / (DATEDIFF(MAX(t2.date),MIN(t2.date)) + 1)-> AS 'avg. precip'-> FROM rainfall AS t1 INNER JOIN rainfall AS t2-> ON t1.date >= t2.date-> GROUP BY t1.date; +------------+--------------+-------------+--------------+-------------+ | date | daily precip | cum. precip | days elapsed | avg. precip | +------------+--------------+-------------+--------------+-------------+ | 2006-06-01 | 1.50 | 1.50 | 1 | 1.500000 | | 2006-06-03 | 0.50 | 2.00 | 3 | 0.666667 | | 2006-06-05 | 1.00 | 3.00 | 5 | 0.600000 | +------------+--------------+-------------+--------------+-------------+ 另一例（對時間的特殊處理）
mysql> SELECT stage, km, t FROM marathon ORDER BY stage; +-------+----+----------+ | stage | km | t | +-------+----+----------+ | 1 | 5 | 00:15:00 | | 2 | 7 | 00:19:30 | | 3 | 9 | 00:29:20 | | 4 | 5 | 00:17:50 | +-------+----+----------+ mysql> SELECT t1.stage, t1.km, t1.t,-> SUM(t2.km) AS 'cum. km',-> SEC_TO_TIME(SUM(TIME_TO_SEC(t2.t))) AS 'cum. t',-> SUM(t2.km)/(SUM(TIME_TO_SEC(t2.t))/(60*60)) AS 'avg. km/hour'-> FROM marathon AS t1 INNER JOIN marathon AS t2-> ON t1.stage >= t2.stage-> GROUP BY t1.stage; +-------+----+----------+---------+----------+--------------+ | stage | km | t | cum. km | cum. t | avg. km/hour | +-------+----+----------+---------+----------+--------------+ | 1 | 5 | 00:15:00 | 5 | 00:15:00 | 20.0000 | | 2 | 7 | 00:19:30 | 12 | 00:34:30 | 20.8696 | | 3 | 9 | 00:29:20 | 21 | 01:03:50 | 19.7389 | | 4 | 5 | 00:17:50 | 26 | 01:21:40 | 19.1020 | +-------+----+----------+---------+----------+--------------+
9、使用 JOIN 控制查詢結(jié)果的順序
（Recipe 12.11. Using a Join to Control Query Output Order）
a、創(chuàng)建一個輔助表，然后連接查詢
b、使用 FROM 子句連接查詢
數(shù)據(jù)如下：
mysql> SELECT * FROM driver_log ORDER BY rec_id; +--------+-------+------------+-------+ | rec_id | name | trav_date | miles | +--------+-------+------------+-------+ | 1 | Ben | 2006-08-30 | 152 | | 2 | Suzi | 2006-08-29 | 391 | | 3 | Henry | 2006-08-29 | 300 | | 4 | Henry | 2006-08-27 | 96 | | 5 | Ben | 2006-08-29 | 131 | | 6 | Henry | 2006-08-26 | 115 | | 7 | Suzi | 2006-09-02 | 502 | | 8 | Henry | 2006-09-01 | 197 | | 9 | Ben | 2006-09-02 | 79 | | 10 | Henry | 2006-08-30 | 203 | +--------+-------+------------+-------+ 任務(wù)：統(tǒng)計出每個人的總里程，并按總里程從大到小排序a——示例
mysql> CREATE TABLE tmp-> SELECT name, SUM(miles) AS driver_miles FROM driver_log GROUP BY name;//演示——START mysql> SELECT * FROM tmp ORDER BY driver_miles DESC; +-------+--------------+ | name | driver_miles | +-------+--------------+ | Henry | 911 | | Suzi | 893 | | Ben | 362 | +-------+--------------+ //演示——ENDmysql> SELECT tmp.driver_miles, driver_log.*-> FROM driver_log INNER JOIN tmp-> ON driver_log.name = tmp.name-> ORDER BY tmp.driver_miles DESC, driver_log.trav_date; +--------------+--------+-------+------------+-------+ | driver_miles | rec_id | name | trav_date | miles | +--------------+--------+-------+------------+-------+ | 911 | 6 | Henry | 2006-08-26 | 115 | | 911 | 4 | Henry | 2006-08-27 | 96 | | 911 | 3 | Henry | 2006-08-29 | 300 | | 911 | 10 | Henry | 2006-08-30 | 203 | | 911 | 8 | Henry | 2006-09-01 | 197 | | 893 | 2 | Suzi | 2006-08-29 | 391 | | 893 | 7 | Suzi | 2006-09-02 | 502 | | 362 | 5 | Ben | 2006-08-29 | 131 | | 362 | 1 | Ben | 2006-08-30 | 152 | | 362 | 9 | Ben | 2006-09-02 | 79 | +--------------+--------+-------+------------+-------+b——示例 mysql> SELECT tmp.driver_miles, driver_log.*-> FROM driver_log INNER JOIN-> (SELECT name, SUM(miles) AS driver_miles FROM driver_log GROUP BY name) AS tmp-> ON driver_log.name = tmp.name-> ORDER BY tmp.driver_miles DESC, driver_log.trav_date; +--------------+--------+-------+------------+-------+ | driver_miles | rec_id | name | trav_date | miles | +--------------+--------+-------+------------+-------+ | 911 | 6 | Henry | 2006-08-26 | 115 | | 911 | 4 | Henry | 2006-08-27 | 96 | | 911 | 3 | Henry | 2006-08-29 | 300 | | 911 | 10 | Henry | 2006-08-30 | 203 | | 911 | 8 | Henry | 2006-09-01 | 197 | | 893 | 2 | Suzi | 2006-08-29 | 391 | | 893 | 7 | Suzi | 2006-09-02 | 502 | | 362 | 5 | Ben | 2006-08-29 | 131 | | 362 | 1 | Ben | 2006-08-30 | 152 | | 362 | 9 | Ben | 2006-09-02 | 79 | +--------------+--------+-------+------------+-------+10、通過 UNION 合并查詢結(jié)果集
（Recipe 12.12. Combining Several Result Sets in a Single Query）
UNION，刪除重復(fù)的行
UNION ALL，不刪除重復(fù)的行
演示數(shù)據(jù)：
mysql> SELECT * FROM prospect; +---------+-------+------------------------+ | fname | lname | addr | +---------+-------+------------------------+ | Peter | Jones | 482 Rush St., Apt. 402 | | Bernice | Smith | 916 Maple Dr. | +---------+-------+------------------------+ mysql> SELECT * FROM customer; +-----------+------------+---------------------+ | last_name | first_name | address | +-----------+------------+---------------------+ | Peterson | Grace | 16055 Seminole Ave. | | Smith | Bernice | 916 Maple Dr. | | Brown | Walter | 8602 1st St. | +-----------+------------+---------------------+ mysql> SELECT * FROM vendor; +-------------------+---------------------+ | company | street | +-------------------+---------------------+ | ReddyParts, Inc. | 38 Industrial Blvd. | | Parts-to-go, Ltd. | 213B Commerce Park. | +-------------------+---------------------+ UNION——演示 mysql> SELECT fname, lname, addr FROM prospect-> UNION-> SELECT first_name, last_name, address FROM customer-> UNION-> SELECT company, '', street FROM vendor; +-------------------+----------+------------------------+ | fname | lname | addr | +-------------------+----------+------------------------+ | Peter | Jones | 482 Rush St., Apt. 402 | | Bernice | Smith | 916 Maple Dr. | | Grace | Peterson | 16055 Seminole Ave. | | Walter | Brown | 8602 1st St. | | ReddyParts, Inc. | | 38 Industrial Blvd. | | Parts-to-go, Ltd. | | 213B Commerce Park. | +-------------------+----------+------------------------+ UNION ALL——演示 mysql> SELECT fname, lname, addr FROM prospect-> UNION ALL-> SELECT first_name, last_name, address FROM customer-> UNION ALL-> SELECT company, '', street FROM vendor; +-------------------+----------+------------------------+ | fname | lname | addr | +-------------------+----------+------------------------+ | Peter | Jones | 482 Rush St., Apt. 402 | | Bernice | Smith | 916 Maple Dr. | | Grace | Peterson | 16055 Seminole Ave. | | Bernice | Smith | 916 Maple Dr. | | Walter | Brown | 8602 1st St. | | ReddyParts, Inc. | | 38 Industrial Blvd. | | Parts-to-go, Ltd. | | 213B Commerce Park. | +-------------------+----------+------------------------+ 列的合并處理： mysql> (SELECT CONCAT(lname,', ',fname) AS name, addr FROM prospect)-> UNION-> (SELECT CONCAT(last_name,', ',first_name), address FROM customer)-> UNION-> (SELECT company, street FROM vendor)-> ORDER BY name; +-------------------+------------------------+ | name | addr | +-------------------+------------------------+ | Brown, Walter | 8602 1st St. | | Jones, Peter | 482 Rush St., Apt. 402 | | Parts-to-go, Ltd. | 213B Commerce Park. | | Peterson, Grace | 16055 Seminole Ave. | | ReddyParts, Inc. | 38 Industrial Blvd. | | Smith, Bernice | 916 Maple Dr. | +-------------------+------------------------+總結(jié)
以上是生活随笔為你收集整理的MySQL Cookbook 学习笔记-03的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 用户权限管理——DB设计篇
- 下一篇： 数独游戏破解