select (select count(*) from it_plan where vid=a.vid and answerad like concat(a.answerad,'%')) , answerad from it_plan as a where a.vid =4 group by answerad;
INSERT INTO Temp_Num(xh) -- 写入数字辅助表 SELECT @i := @i+1 FROM information_schema.COLLATIONS SELECT t.n,SUM(num) AS num FROM ( SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(LEFT(s.n,CHAR_LENGTH(s.n)-1),',',l.xh),',',-1) AS n, s.numFROM ( SELECT '骨干网络,' n,15 num UNION ALL SELECT '骨干网络,接入网络,', 3 UNION ALL SELECT '骨干网络,接入网络,用户端,',28 )sJOIN Temp_Num l ON((l.xh <= ((LENGTH(s.n)-1 - LENGTH(REPLACE(s.n,',',''))) + 1))) )t GROUP BY t.n ORDER BY SUM(num) desc
select sum(case when answer_AD like '%骨干网络%' then 1 else 0) s1 , sum(case when answer_AD like '%接入网络%' then 1 else 0) s2, sum(case when answer_AD like '%配置故障%' then 1 else 0) s3 from it_plain where vid=4;
from it_plan as a
where a.vid =4
group by answerad;
先创建一个辅助表,类似:
CREATE TABLE Temp_Num ( xh INT PRIMARY KEY ); -- 创建数字辅助表
SET @i = 0;
INSERT INTO Temp_Num(xh) -- 写入数字辅助表
SELECT @i := @i+1
FROM information_schema.COLLATIONS
SELECT t.n,SUM(num) AS num
FROM
(
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(LEFT(s.n,CHAR_LENGTH(s.n)-1),',',l.xh),',',-1) AS n,
s.numFROM
(
SELECT '骨干网络,' n,15 num UNION ALL
SELECT '骨干网络,接入网络,', 3 UNION ALL
SELECT '骨干网络,接入网络,用户端,',28
)sJOIN Temp_Num l ON((l.xh <= ((LENGTH(s.n)-1 - LENGTH(REPLACE(s.n,',',''))) + 1)))
)t
GROUP BY t.n
ORDER BY SUM(num) desc
sum(case when answer_AD like '%接入网络%' then 1 else 0) s2,
sum(case when answer_AD like '%配置故障%' then 1 else 0) s3 from it_plain where vid=4;