我有两个表：code和code_like
代码表
code_id | code_name
---------------
1 | pagination
2 | function
3 | script
类代码表
like_id | code_id(id of code table)
1 | 3
2 | 1
3 | 3
4 | 2
5 | 1
6 | 3
我想按code_like表的行计数对code table进行排序
示例：
I want result like
(1) script (1st because 3 times in code_like table)
(2) pagination (2nd because 2 times in code_like table)
(3) function (3rd because 1 time in code_like table)
使用连接并按代码计数排序，如
select c.*
from code c
left join code_like l on c.id = l.code_id
group by c.id
order by count(*) desc
DEMO
这里需要COUNT、JOIN和GROUP BY
SELECT c.code_name, count(*) AS rowcount
FROM code c LEFT JOIN code_like l ON l.code_id = c.id
GROUP BY c.id
ORDER BY rowcount DESC
sqlfiddle
你可以试试这个：
SELECT *
, (SELECT COUNT(*) FROM code_like WHERE code_like.code_id = code.id ) AS like
FROM code
ORDER BY (SELECT COUNT(*) FROM code_like WHERE code_like.code_id = code.id ) DESC
或
SELECT * FROM ( SELECT *
, (SELECT COUNT(*) FROM code_like WHERE code_like.code_id = code.id ) AS like
FROM code
) AS code_was_like
ORDER BY like DESC
我希望这能有所帮助。
SELECT c . *
FROM code AS c
INNER JOIN code_like AS cl ON cl.code_id = c.id
GROUP BY c.id
ORDER BY COUNT( cl.code_id ) DESC