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

Question

Answer 1

如果有表结构:

 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