MYSQL 資料庫 PHP 查詢後結合 資料

請問如果原本是這樣

SELECT *

FROM motonumber

inner join orderitem on orderitem.o_i_int=motonumber.moto_ordernum

WHERE moto_makeok = '1' and m_out_num is not NULL

但是我想要在inner join 前先做一個count 把orderitem裡的某個欄位先COUNT數量,假設是教室點閱紀錄想先COUNT紀錄再JOIN到,教室名稱有辦法嗎

列出來的東西很像

教室 點閱數

教室名稱 5

教室2 4

教室3 1

教室4 3

1 Answer

Rating
  • 1 decade ago
    Best Answer

    恩....以我來說我應該會先把兩個TABLE的資料展開再去做count

    select * from classroomInfo

    classID  className

    -------------------------

    id01   教室1 

    id02   教室2

    id03   教室3

     

     

    select * from 點閱紀錄TABLE

    classID  點閱日期

    -------------------------

    id01   2/3 

    id01   3/4 

    id01   6/5 

    id02   11/5

    id03   4/6

    id03   5/5

     

     

    當你查詢

    select classroomInfo.classID, 點閱日期, className

    from 點閱紀錄TABLE

    join classroomInfo on 點閱紀錄TABLE.classID = classroomInfo.classID

    classID  點閱日期 className

    -----------------------------------------------------------

    id01   2/3    教室1

    id01   3/4    教室1 

    id01   6/5    教室1 

    id02   1/5    教室2

    id03   4/6    教室3

    id03   5/5    教室3

    所以當我們再將這個展開的值做group by的運算

    select className,count(點閱日期) as 點閱數

    from 點閱紀錄TABLE

    join classroomInfo on 點閱紀錄TABLE.classID = classroomInfo.classID

    group by classroomInfo.classID

    className  點閱數 

    -----------------------------------------------------------

    教室1    3

    教室2    1

    教室3    2

    Source(s):
Still have questions? Get your answers by asking now.