数据库结构如下user_id username f1
1 vipstar NULL
2 admin NULL
3 sun 1
4 testu 2
5 test2 2
6 test3 1
7 csdnu 1欲用一句sql语句实现排序,排序的条件即f1出现的频率高低,其中f1代表的是推荐人的user_id;也就是看哪个user_id推荐的用户最多
1 vipstar NULL
2 admin NULL
3 sun 1
4 testu 2
5 test2 2
6 test3 1
7 csdnu 1欲用一句sql语句实现排序,排序的条件即f1出现的频率高低,其中f1代表的是推荐人的user_id;也就是看哪个user_id推荐的用户最多
我想要的是,按 f1 (=user_id) 出现频率的高低排序,
比如 f1 = 1 出现了3次,即user_id=1的排在
f1=2 出现2次的 user_id=2 的前面
SELECT a.*, b.username AS parentname, count(a.f1) AS num
FROM table AS a
LEFT JOIN table AS b ON a.f1=b.user_id
WHERE 1
GROUP BY a.f1
ORDER BY num DESC
FROM `test`
LEFT JOIN (
SELECT `f1`, count( f1 ) AS f1num
FROM `test`
WHERE 1
GROUP BY `f1`
) AS tmp ON tmp.f1 = test.f1
WHERE 1
ORDER BY tmp.f1num DESC , test.id ASC
考虑null
SELECT test.`id` , test.`name` , test.`f1` , tmp.f1num
FROM `test`
LEFT JOIN (SELECT `f1` , count( IF( IFNULL( NULL , f1 ) , f1, 0 ) ) AS f1num
FROM `test`
WHERE 1
GROUP BY `f1`//这里我自己也有点不明,单独运行这句,f1为null的记录f1num查出来会是正确的数字,但完整的left join以后却又莫名其妙的变成null了
) AS tmp ON tmp.f1 = test.f1
WHERE 1
ORDER BY tmp.f1num DESC , test.id ASC
SQL 查询: SELECT test.`id` , test.`name` , test.`f1` , tmp.f1num
FROM `ecm_member`
LEFT JOIN (
SELECT `f1` , count( IF( IFNULL( NULL , f1 ) , f1, 0 ) ) AS f1num
FROM `ecm_member`
WHERE 1
GROUP BY `f1`
) AS tmp ON tmp.f1 = test.f1
WHERE 1
ORDER BY tmp.f1num DESC , test.id ASC
LIMIT 0 , 30 MySQL 返回: #1054 - Unknown column 'test.id' in 'field list'
不知为什么是空的呢,提示您运行的 SQL 语句已经成功运行了。 (查询花费 0.0001 秒)SQL 查询:SELECT count( * ) AS num, f1
FROM ecm_member
WHERE f1 <> NULL
GROUP BY f1
ORDER BY num;
我实际表名为ecm_member