SQL开发技巧:开发技巧

 

一、行列转换

　　基本思路：

　　　　1. 将数据转转为标书数据，如图所示

　　　　　　

　　　　2. 使用笛卡尔积做行列转换

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;

一、单列转多行转换

原数据样式

　　

　　　　　　处理后数据样式

　　　　　　　　

　　　　SQL语句

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

 三、处理重复数据

　　1.查询重复数据

SELECT username , COUNC(*) FROM userInfo GROUP BY username HAVING count(*) > 1

　　2.删除重复的数据，保留ID最大

　　　　

DELETE a FROM userInfo_192 a JOIN ( SELECT username, COUNT(*), MAX(id) as id FROM userInfo_1 GROUP BY username HAVING COUNT(*) > 1) b ON a.username = b.username WHERE a.id < b.id