| 這個是 小弟在 tim club 討論時 碰到一個 蠻有趣的問題 雖然小弟在那裡回答了 但希望更多人知道 這個解決問題的技巧 不要什麼都用 sub query 會寫出大怪物的... 嘿嘿..cccQ1 : FORM JOKY這是個蠻蠢的問題..我用MySQL 3.23.58 rpm版本分別有三個table , schema 如下︰create table member (member_id int not null auto_increment,name varchar(255),primary key (member_id));-- data dumpinsert into member values (1,'Joky');insert into member values (2,'Stanley');insert into member values (3,'David');insert into member values (4,'Mary');create table morder (order_id int not null auto_increment,member_id int not null,primary key (order_id));-- data dumpinsert into morder values (1,1);insert into morder values (2,1);insert into morder values (3,2);insert into morder values (4,2);insert into morder values (5,1);insert into morder values (6,1);insert into morder values (7,3);create table mbonus (bonus_id int not null auto_increment,member_id int not null,bonus int,primary key(bonus_id));-- data dumpinsert into mbonus values (1,1,100);insert into mbonus values (2,1,200);insert into mbonus values (3,2,50);insert into mbonus values (4,2,30);insert into mbonus values (5,1,90);insert into mbonus values (6,1,70);insert into mbonus values (7,3,160);好, 問題來了, 而且很蠢的感覺* 語法1select member.* , count(morder.order_id) as totalorder from member LEFT JOIN morder ON member.member_id = morder.member_id group by member.member_id order by member_id desc;得到【程式碼】 +-----------+---------+------------+| member_id | name | totalorder |+-----------+---------+------------+| 4 | Mary | 0 || 3 | David | 1 || 2 | Stanley | 2 || 1 | Joky | 4 |+-----------+---------+------------+* 語法2select member.* , sum(mbonus.bonus) as totalbonus from member LEFT JOIN mbonus ON member.member_id = mbonus.member_id group by member.member_id order by member_id desc;得到【程式碼】 +-----------+---------+------------+| member_id | name | totalbonus |+-----------+---------+------------+| 4 | Mary | 0 || 3 | David | 160 || 2 | Stanley | 80 || 1 | Joky | 460 |+-----------+---------+------------+語法3 - 現在改成這種寫法 (2個一齊來 left join)select member.* , sum(mbonus.bonus) as totalbonus,count(morder.order_id) as totalorder from member LEFT JOIN mbonus ON member.member_id = mbonus.member_id LEFT JOIN morder ON member.member_id = morder.member_id group by member.member_id order by member_id desc;卻會得到【程式碼】 +-----------+---------+------------+------------+| member_id | name | totalbonus | totalorder |+-----------+---------+------------+------------+| 4 | Mary | 0 | 0 || 3 | David | 160 | 1 || 2 | Stanley | 160 | 4 || 1 | Joky | 1840 | 16 |+-----------+---------+------------+------------+語法3 這句該如何修正, 才能得到正確的答案呢極蠢的是, 每一個值都是照著 0,2的0次, 2的1次, 2的2次成長.. A1 : FORM JOKY自問自解好了..... 今天有幸跟一位很厲害的高手 henry 討論, 他提出了解決方法呢 500)this.width=500'>【程式碼】 select ms.member_id,ms.name,ms.mb,mm.mo from (select member.*,sum(mbonus.bonus) as mb from member left join mbonus using (member_id) group by member.member_id,member.name) as ms natural join (select member.*,count(morder.order_id) as mo from member left join morder using (member_id) group by member.member_id,member.name) as mm;這方法是用 二個 select 的結果去當成二個table , 再把二個 table 去做 natural selectps:什麼是 natural select 呢？加上這個關鍵字之後，兩個表格在進行 JOIN 時，不必言明彼此的結合關係，兩者之間同名的欄位會被自動結合在一起在此感謝 henry 的幫助.. 雖然他有勒索我雞排. 嗚嗚........A2 : FORM FIEND 這是因為 二句 left join 造成 morder.order_id 有了 (bonusr 的筆數 * morder) 的效果 造成 id 去做 count 時 會 有 m*n 的效果 所以 多半都要做 (m*n)/m mysql 提供了 distinct 給 count用就是為了處理這種狀況~問一些資深點的工程師都知道為什麼 - 還能解說原因 用 sub query 太誇張了 distinct 就能解決了~ 看情形如果說不知道自己下的 sql 邏輯 你寫的程式一定都很可怕 ccc 開玩笑啦 ^^!! 賣生氣不過事實確是如此 從這裡就可以看出程式設計師根本不知道 這二句 join mysql 做了那些事 當初 系統規劃師做的工作也是做白工了~如果我是老闆 就算php語法再強 - 觀念不好的人寫的程式還是會讓我很害怕 ^^!! 語重了 參考就好~ 答案是 :mysql> SELECT member . * , sum( mbonus.bonus ) AS totalbonus, count( DISTINCT morder.order_id ) AS totalorder-> FROM member-> LEFT OUTER JOIN mbonus ON member.member_id = mbonus.member_id-> LEFT OUTER JOIN morder ON member.member_id = morder.member_id-> GROUP BY member.member_id-> ;[]+-----------+---------+------------+------------+| member_id | name | totalbonus | totalorder |+-----------+---------+------------+------------+| 1 | Joky | 1840 | 4 || 2 | Stanley | 160 | 2 || 3 | David | 160 | 1 || 4 | Mary | NULL | 0 |+-----------+---------+------------+------------+4 rows in set (0.00 sec)很想哭 吧 一句參數就結束了~ ^^!! 還請一客雞排 我也要多看點官方手冊吧 ^^ 每支參數和function 都有它的存在價值多多品咮啦http://www.twbb.org/ebook/mysql4 ... ence.html#FunctionsQ2 : FROM : 妥當大家好!!小弟最近也遇到相同的問題!!我用的方式也是和FIEND 前輩一樣!!不過求出的值一樣有誤!!以此主題為例:實際結果應為:【程式碼】 +-----------+---------+------------+------------+| member_id | name | totalbonus | totalorder |+-----------+---------+------------+------------+| 1 | Joky | 460 | 4 || 2 | Stanley | 80 | 2 || 3 | David | 160 | 1 || 4 | Mary | 0 | 0 |+-----------+---------+------------+------------+而非【程式碼】 +-----------+---------+------------+------------+| member_id | name | totalbonus | totalorder |+-----------+---------+------------+------------+| 1 | Joky | 1840 | 4 || 2 | Stanley | 160 | 2 || 3 | David | 160 | 1 || 4 | Mary | 0 | 0 |+-----------+---------+------------+------------+雖然在COUNT()中加入了DISTINCT可去除重複的資料,但是SUM()卻沒辦法,因此totalbonus所得的值會再乘以totalorder的值A2 : FROM : FIEND 好久沒來了 不知現在回來不來的及 ccc妥當 兄 你還真古意咧 小學生都會的算數題目 - 哈哈哈 : 你就 用(sum(id)/(count(id) / count( DISTINCT id))) as num 就得到你要地值了咩.........ccc 文章來源 : http://timteam.org/?TIM=FORUM&am ... ment=14153#TIM14153 |
