作者pickman888 (joker)
看板Database
標題Re: [MySQL] Select the row which one of the ...
時間Mon Jan 22 16:33:53 2018
※ 引述《cutekid (可愛小孩子)》之銘言：
: with tb1 as (
: select Id,count(*) as cnt
: from example
: group by Id
: )
: select t1.No,t1.Name
: from example t1
: inner join tb1 on t1.No = t2.Id
: order by t2.cnt desc
: ※ 引述《rrr0832 (rrr0832)》之銘言：
: : If there is a table name "example" like below:
: : http://i.redwh.al/Ef.png
: : How do I select the name which "No" has the most occurrence in other rows' "Id
: : "?
: : Here's my try:
: : http://i.redwh.al/Ld.png
: : The result should be like this:
: : http://i.redwh.al/T7.png
mysql 8 才有 CTE的樣子
之前的版本應該用下面的寫法就可以了
SELECT @order:=@order+1 AS `order` ,NO ,NAME , CONCAT('(which no has occured ' , `dec` , ' times in Id') description
FROM ( SELECT NO , NAME , COUNT(*) `dec` FROM EXAMPLE GROUP BY NO , NAME ) a , (SELECT @order:=0) b
ORDER BY `dec` DESC ;
--
※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 114.24.155.79
※ 文章網址: https://webptt.com/m.aspx?n=bbs/Database/M.1516610036.A.D5E.html