删除重复数据、行转列、列转行
来源:互联网 发布:菜鸟网络到底做什么 编辑:程序博客网 时间:2024/06/07 03:34
查询是否有重复数据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_idWHERE (b.user_id,b.kills) IN (SELECT user_id,MAX(kills) FROM user_kills GROUP BY user_id);
- 删除重复数据、行转列、列转行
- 删除数据库列中重复数据
- 数据库删除指定列重复数据
- excel 删除重复行数据,列数据
- 列转行、行转列
- 行转列 -列转行
- SQL行转列 列转行
- SQL行转列,列转行
- 行转列 列转行
- 行转列.列转行 oracle
- oracle 行转列,列转行
- 【ORACLE】行转列,列转行
- SQL 列转行/行转列
- oracle 行转列 列转行
- oracle 行转列,列转行
- 行转列 VS 列转行
- 列转行--行转列
- oracle 行转列 列转行
- 如何用Python写一个小游戏(1)
- HDU 3594 Cactus 【强连通】
- 单链表定义-(线性表的链表存储结构)
- 【iOS_GitHub】评分条/星级条(类似于淘宝/京东/1号店/亚马逊等商城类的评分条,支持0.5分哦)
- Node JS体系架构 - 单线程事件循环
- 删除重复数据、行转列、列转行
- maven依赖关系中Scope的作用
- 批处理命令学习笔记——操作注册表
- JAVA设计模式(适配器模式)
- RNN代码解读之char-RNN with TensorFlow(util.py)
- sql学习笔记
- 编写qml到安卓手机的一些小心得
- QZ项目开发(一)项目开发实用工具总结
- Swift闭包表达式