SQL开发技巧:开发技巧

select 
    sum(case when childr=‘餐饮‘ then money end ) as ‘餐饮‘ , 
    sum(case when childr=‘通讯‘ then money end ) as ‘通讯‘ ,
    sum(case when childr=‘娱乐‘ then money end ) as ‘娱乐‘ 
from 
    (select childr, convert(sum(money), decimal(10,2)) as money from wx_spend  group by childr) tmp;

CREATE TABLE tmp_sequence (id INT PRIMARY KEY AUTO_INCREMENT);
INSERT INTO tmp_sequence VALUES(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),();

SELECT 
    name, REPLACE(SUBSTRING(SUBSTRING_INDEX(courseinfo, ‘,‘, a.id), CHAR_LENGTH(SUBSTRING_INDEX(courseinfo, ‘,‘, a.id-1)) + 1), ‘,‘ ,‘‘) AS courseinfo
FROM 
    tmp_sequence a 
CROSS JOIN 
    (SELECT name, concat(courseinfo,",") AS courseinfo, LENGTH(courseinfo) - LENGTH(REPLACE(courseinfo, ‘,‘, ‘‘)) + 1 AS size FROM W_SalesCoursePackageInfo LIMIT 1,1) b
ON a.id <= b.size