Yahoo Answers is shutting down on May 4th, 2021 (Eastern Time) and beginning April 20th, 2021 (Eastern Time) the Yahoo Answers website will be in read-only mode. There will be no changes to other Yahoo properties or services, or your Yahoo account. You can find more information about the Yahoo Answers shutdown and how to download your data on this help page.

php 群組後排序 order

table1

---------欄位甲----欄位乙

第1列---A----------K

第2列---A----------Y

第3列---B----------Y

第4列---C----------X

第5列---C----------Y

第6列---C----------Z

table2

---------欄位甲

第1列---X

第2列---X

第3列---Y

第4列---Z

第5列---Z

第6列---Z

第7列---K

第8列---K

如果傳遞一個搜尋值$search=C給table1搜尋, 請問如何用SQL語法比對table1及table2然後GROUP依table2的欄位甲重複出現次數從大到小排序結果成:

Z共3筆

X共2筆

Y共1筆

以下是我尚未合併的兩個SQL語法請參考,我想應該是要併在一起搜尋(子查詢??),請指教了,謝謝大家~!!

query=mysql_query(select * from table1 where 欄位甲='$search');

$result1=mysql_fetch_object(query);

query=mysql_query(select 欄位甲,count(*) AS count from table2 where 欄位甲='$result1->欄位甲' GROUP BY 欄位甲);

$result2=mysql_fetch_object(query);

Update:

感謝兩位回應,因為我是用Mysql因此請教Will回應的部分

from table1 a,table2 b----- " table1 a,table2 b"這語法是什麼意思?

)T order by cnt desc------- "T"又是什麼意思呢?

謝謝~!!

Update 2:

TO:路人甲

你可以弄成Mysql語法也讓大家參考看看嗎?

感謝~!!

3 Answers

Rating
  • 1 decade ago
    Favorite Answer

    SELECT t1.欄位乙 , count(t2.*) AS cnt

    FROM table1 t1,table2 t2

    WHERE t1.欄位乙 = t2.欄位甲

    AND t1.欄位甲 = 〝$search〞

    GROUP BY t1.欄位乙

    ORDER BY cnt DESC

    t1 和 t2 只是重新命名而已

    就不用每次都打 table1.欄位甲 那麼長

    table1 t1,table2 t2 是join的寫法

    簡單的說

    table1有5筆資料

    table2有3筆資料

    結果就會出現15筆資料 我們再經過一些條件的篩選

    過濾掉不要的資料

    p.s.引號記得改成正常的引號

    Source(s): 自己
  • 1 decade ago

    select * from(

    select b.欄位甲,count(*) as cnt from table1 a,table2 b where a.欄位乙=b.欄位甲 and a.欄位甲 = 'C' group by b.欄位甲

    )T order by cnt desc

    2008-05-01 11:34:09 補充:

    table1 a

    是表格別名

    後面的 a.欄位乙 即代表 table1.欄位乙

    T也是同樣的意思 把前面括號的select結果指定一個別名 T

    所以後面 order by cnt desc 也可以是 order by T.cnt desc

    Source(s): 我, 我
  • 1 decade ago

    for MSSQL T-SQL

    Select Table2.欄位甲,count(*)

    from Table1 ,Table2

    where Table1.欄位甲='C' and Table1.欄位乙=Table2.欄位甲

    Group By Table2.欄位甲

Still have questions? Get your answers by asking now.