请教一下数据库查询大神,要把sign列里面的值做一下筛选,选出出现频率最高的3个,应该怎么写这个SQL查询?要的效果就像我用PHP处理后的图二这样子
SELECT
count(a.id) as num ,
substring_index( substring_index( a.sign, ',', b.help_topic_id + 1 ), ',',- 1 ) AS a_sign
FROM
`album` AS a
JOIN mysql.help_topic b ON b.help_topic_id < ( length( a.sign ) - length( REPLACE ( a.sign, ',', '' ) ) + 1 )
group by a_sign order by num desc limit 3
注意
1、mysql帐号要有help_topic表的操作权限
2、当两个词出现次数同样时,先出现的会排在前面
3、group by 后面的 a_sign要与第三行 as后面的a_sign保持同样的字段名 且与不一样原本的字段名不一样,,如果想要用同样的字段名,用下面的sql语句
SELECT
sign,
count( * ) as num
FROM
(
SELECT
substring_index( substring_index( a.sign, ',', b.help_topic_id + 1 ), ',',- 1 ) AS sign
FROM
`album` AS a
JOIN mysql.help_topic b ON b.help_topic_id < ( length( a.sign ) - length( REPLACE ( a.sign, ',', '' ) ) + 1 )
) AS a
GROUP BY
sign order by num desc limit 3