討論一個MySQL的LEFT JOIN 的問題

edited 十月 2013 in PHP新手區
這是個蠻蠢的問題..我用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 dump
insert 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 dump
insert 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 dump
insert 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);

好, 問題來了, 而且很蠢的感覺\
* 語法1
select 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 |
+-----------+---------+------------+

* 語法2
select 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次成長..


原始討論: http://twpug.net/x/modules/newbb/viewtopic.php?topic_id=253

評論

  • edited 九月 2004
    剛剛玩了一下:

    select A.* , sum(B.bonus) as totalbonus,count(C.order_id) as totalorder
    from member as A
    LEFT JOIN mbonus as B, morder as C ON A.member_id = B.member_id = C.member_id
    group by A.member_id order by A.member_id desc


    結果變成:

    +-----------+---------+------------+------------+
    | member_id | name    | totalbonus | totalorder |
    +-----------+---------+------------+------------+
    |         4 | Mary    |        700 |          0 |
    |         3 | David   |       1180 |          4 |
    |         2 | Stanley |        940 |          8 |
    |         1 | Joky    |       2080 |         16 |
    +-----------+---------+------------+------------+
    

    數字好像更怪了... ├090┤

    恩...繼續去忙自己的了...我通常不會想要把這種描述寫成一行......
  • edited 十一月 2004
    自問自解好了.....
    今天有幸跟一位很厲害的高手 henry 討論, 他提出了解決方法呢 :)
    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 select

    ps:
    什麼是 natural select 呢?
    加上這個關鍵字之後,兩個表格在進行 JOIN 時,不必言明彼此的結合關係,兩者之間同名的欄位會被自動結合在一起\

    在此感謝 henry 的幫助.. 雖然他有勒索我雞排. 嗚嗚........
    8-)
  • edited 十一月 2004
    恩,找了一下資料:

    http://chensh.loxa.edu.tw/php/B_9.php

    看來我還要再努力囉

    ├130┤
Sign In or Register to comment.