sql开发技巧总结-2
---恢复内容开始---
1.如何进行行列转换
需求:
列转换成行
select a.`user_name`,sum(b.kills) from user1 a join user_kills b
on a.id = b.user_id group by a.user_name;
-行转换成列
select sum(case when user_name='wukong' then kills end) as 'wukong',
sum(case when user_name='zhubajie' then kills end) as 'zhubajie',
sum(case when user_name='shaseng' then kills end) as 'shaseng'
from user1 a join user_kills b on a.id=b.user_id;
-列转换成行
alter table user1 add column mobile varchar(100);
select user_name,concat(mobile,',') as mobile,LENGTH(mobile)-LENGTH(REPLACE(mobile,',',''))+1 size from contact b; //计算长度
总的:
--union all 列转行操作
select user_name,'arms' as equipment,arms from user1 a join user1_equipment
b on a.id=b.user_id
union all
select user_name,'clothing' as equipment,clothing from user1 a join user1_equipment
b on a.id=b.user_id
union all
select user_name,'shoe' as equipment,shoe from user1 a join user1_equipment
b on a.id=b.user_id order by a.user_name;
--序列表 列转行操作
(coalesce不为空)
select user_name
,coalesce(case when c.id = 1 then arms end
,case when c.id = 2 then clothing end
,case when c.id = 3 then shoe end) as eq_name
from user1 a
join user1_equipment b on a.id = b.user_id
cross join tb_sequence c where c.id<=3 order by user_name;
select user_name,
case
when c.id = 1 then 'arms'
when c.id = 2 then 'clothig'
when c.id = 3 then 'shoe'
end as equipment
,coalesce(case when c.id = 1 then arms end
,case when c.id = 2 then clothing end
,case when c.id = 3 then shoe end) as eq_name
from user1 a
join user1_equipment b on a.id = b.user_id
cross join tb_sequence c where c.id<=3 order by user_name;
2.如何生成唯一序列号
生成序列号的方法
--(优先选择系统提供的序列号生成方式)
--特殊情况下可以使用SQL方式生成序列号
mysql AUTO_INCREMENT
sql_server IDENTITY/SEQUENCE
Oracle SEQUENCE
需求:生成订单序列号 并且订单号格式如下
YYYYMMDDNNNNNNN 如201505120000003
3.如何删除重复数据
产生数据重复的原因:
人为:重复录入数据 重复提交
系统:由于系统升级或者设计使原来可以重复的数据变为不可用
如何查询数据是否重复
group by having
select user_name,COUNT(*)
FROM user1_test
GROUP BY user_name HAVING COUNT(*)>1
如何删除重复数据 对于相同数据保留ID最大的
更复杂情况
函数:
1、从左开始截取字符串
left(str, length)
说明:left(被截取字段,截取长度)
例:select left(content,200) as abstract from my_content_t
2、从右开始截取字符串
right(str, length)
说明:right(被截取字段,截取长度)
例:select right(content,200) as abstract from my_content_t
3、截取字符串
substring(str, pos)
substring(str, pos, length)
说明:substring(被截取字段,从第几位开始截取)
substring(被截取字段,从第几位开始截取,截取长度)
例:select substring(content,5) as abstract from my_content_t
select substring(content,5,200) as abstract from my_content_t
(注:如果位数是负数 如-5 则是从后倒数位数,到字符串结束或截取的长度)
4、按关键字截取字符串
substring_index(str,delim,count)
说明:substring_index(被截取字段,关键字,关键字出现的次数)
例:select substring_index("blog.jb51.net","。",2) as abstract from my_content_t
结果:blog.jb51
(注:如果关键字出现的次数是负数 如-2 则是从后倒数,到字符串结束)
SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)
不带有len 参数的格式从字符串str返回一个子字符串,起始于位置 pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串,起始于位置 pos。 使用 FROM的格式为标准 SQL 语法。也可能对pos使用一个负值。假若这样,则子字符串的位置起始于字符串结尾的pos 字符,而不是字符串的开头位置。在以下格式的函数中可以对pos 使用一个负值。
最新文章
- 【POJ3691】DNA repair(AC自动机,DP)
- java中运算符的优先级
- 苹果IOS开发者账号总结--发布应用APP时team name是否可以随意写?
- MEF
- LeetCode:Unique Paths I II
- UVALive 6884 GREAT + SWERC = PORTO dfs模拟
- 开源牛人 zcbenz
- wxpython ItemContainer
- 彻底弄懂LSH之simHash算法
- CentOS5.6下安装Oracle10G软件 【保留报错经验】
- mmmmmmmm
- iOS开发——判断手机格式
- 双向lstm-crf源码的问题和细微修改
- IO 异常:The Network Adapter could not establish the connection 怎么解决
- 使用pie.htc时Border-radius的兼容
- [apache2.4]configure: error: APR not found. Please read the documentation.
- 那些年我们一起用过的Hybrid App
- 第一周java学习总结
- 安装与配置Flutter开发环境
- Linux 配置本地源 (Ubuntu / CentOS)
热门文章
- C# 保存文件如有重名在原名后加(*)
- 转:InnoDB Crash Recovery 流程源码实现分析
- java.lang.Runtime.exec() Payload Workarounds
- Python类(四)-多态
- HOOK技术演示
- 新增线下、APP、公众号多处入口,小程序会再火起来么?
- 【新手向】Centos系统文件权限的系统阐述与演示
- spring MVC 异常处理整理
- The R Project for Statistical Computing
- LNMP 1.2 Nginx编译安装