特殊用途的SQL语句

1.SQL语句之添加序号

语句是select Row_Number()over(order by userid ) xh,name from gbgl_ryxx_jbxx where dxbz='0' order by userid

注意点  Row_Number()over(order by userid ) xh,    函数内部的字段必须是排序的字段

 

2.SQL语句之多条数据拼接某个字段

 SQL语句 select xbmc,string_agg(jb.name,',') pinjie from gbgl_ryxx_jbxx jb where userid<100  group by xbmc;

 注意点： 子串拼接函数是分组对应，是个多的关系。  两个表一对多或者多对多，一个表就是分组

3.SQL语句之截取长度和补充长度

SQL语句 length(字段)=6  substr(sydw.bmbh, 0, 7)=bm.bmbh   

截取字段获得六位长度。注意点，下标加一

 补充长度则用字符串拼接  select stuff('00000',len('00000')-len('123')+1,len('123'),'123')

应用场景：业务分级，目录结构等。例如用某一字段标示部门编号，2个长度标示一级部门，四个长度标示二级部门。补0之后可以按照大小排序，表示父子部门

第二种实现：建立表的自关联，设置一个字段，指向父级部门的部门编号

4.SQL语句之连接符

--解决因字符串拼接造成的SQL注入
'a'||'b' ='ab'  ||相当于java的+
like '%${bmbh}%' = like '%'||#{bmbh}||'%'  
#在编译过程中会加上''，字符串拼接和替换的区别

 5.SQL语句之count()函数使用

利用count对类型进行统计。

 6.SQL查询之封装结果集表

 注意结果集表字段的数据。

 7.SQL查询之多条件判断

语句格式

select userid,name,(case  when xbbm='0' then '女' 
                                                    when xbbm='1' then '没有名字还要什么性别'
end) as haidi from gbgl_ryxx_jbxx where userid<10;

select userid,name,(case  when xbbm='0' then '女' else '男'
end) as haidi from gbgl_ryxx_jbxx where userid<10;

注意：end结束，多个条件筛选的子集加起来刚好是整个集合

 8.SQL语句修改之末尾加字段

语句格式

update user set name=name||'狗尾续貂' where xbbm=0

 等号可以用其它字段来代替整段内容，pgsql不可以用这种方式

9.多表关联更新

 多表关联更新有两种方式。

其一是

update a,b set a.name=b.name where a.id=b.id；

两张表关联更新，如果id对应的name相等，则不更新。如果id重复，则以表里数据前面一条的name为准。如果没有对应的id，则不更新

其二是（建议第二种）  如果关联的字段为全部为空，建议第二种，速度更快。 

update a set a.name=(select name from b where a.id=b.id)

如果有id对应的name，则不更新。如果id重复，则会报错。如果没有对应的id，则会将name置空。

---

10.多表字段字典转换匹配

想要获得女歌手，男演员的名字。

select  a.name  from 

　　　　sex_name a,role_name b where

　　　　a.name=b.name and

　　　　(a.sex= '女'  and b.role='歌手') or (a.sex= '男'  and b.role='演员');

 还有一种实现可以用子查询，但不如这种。字典项比较少可以这么选择。字典项比较多的话建议加一个字典项转换的对应表做转换。