※ 引述《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:[email protected]+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 ;