mysql 根据str字段给定(1,2,3,4,6,7)个分组，统计它们分别在str里包含的次数

如果有表结构:

 CREATE TABLE `test` (
  `id` int(11) DEFAULT NULL,
  `str` varchar(20) DEFAULT NULL
)

那可以这样

 SELECT t.num,IFNULL(SUM(LOCATE(t.num,test.str)>0),0) FROM (
    SELECT 1 AS num UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 
    ) t 
    LEFT JOIN  test  ON LOCATE(t.num,test.str)>0
GROUP BY t.num

SELECT substring_index(substring_index(t.context,',', b.help_topic_id + 1), ',', -1) FROM test.test t join mysql.help_topic b ON b.help_topic_id <  (LENGTH(t.context) - LENGTH(REPLACE(t.context, ',', '')) + 1);

大体思路：判断每个字段是否包含字段，有就加1.用几个if语句就可以。

 SELECT  num.num AS '数字', COUNT(test.id)
FROM (SELECT 1 AS num UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7) AS num
LEFT JOIN test 
ON FIND_IN_SET(num.num, test.str)
GROUP BY num.num
HAVING COUNT(test.id) > 0;