删除重复数据、行转列、列转行

查询是否有重复数据http://write.blog.csdn.net/postedit/53517081

select name,conunt(*) from test group by name having count(*)>1;

删除重复数据

delete a from test a join(select name,conunt(*),max(id) as id from test group by name having count(*)>1) b on a.name=b.name whhttp://write.blog.csdn.net/postedit/53517081ere a.id>b.id;

行转列

1、SELECT sum(case when user_name='孙悟空' then kills end) as '孙悟空', sum(case when user_name='猪http://write.blog.csdn.net/postedit/53517081八戒' then kills end) as '猪八戒', sum(case when user_name='沙僧' then kills end) as '沙僧' FROM user1 a join user_kills b on a.id=b.user_id;

2、SELECT * FROM(SELECT sum(kills) as '沙僧' FROM user1 a join user_kills b on a.id=b.user.id and a.user_name= '沙僧') a cross join (SELECT sum(kills) as '沙僧' FROM user1 a join user_kills b on a.id=b.user.id and a.user_name= '沙僧') b cross join (SELECT sum(kills) as '沙僧' FROM user1 a join user_kills b on a.id=b.user.id and a.user_name= '沙僧') c;

列转行

SELECT user_name,REPLACE( SUBSTRING(SUBSTRING_INDEX(mobile,',',a.id),CHAR_LENGTH(SUBSTRING_INDEX(mobile,',',a.id-1))+1),',','' ) AS mobile FROM  tb_sequence a CROSS JOIN ( SELECT user_name,CONCAT(mobile,',' ) AS mobile,LENGTH(mobile)-LENGTH(REPLACE(mobile,',',''))+1 size FROM user1 b) b ON a.id <=b.size;

多列查询过滤：

SELECT a.user_name,b.timestr,kills FROM user1 a JOIN user_kills b ON a.id=b.user_id WHERE (b.user_id,b.kills) IN (SELECT user_id,MAX(kills) FROM user_kills GROUP BY user_id);