Oracle sql语句

一、更新关联数据

表中hallbin字段的很多值有误，为“空白”，应该为CK加上membercode字段的值。

示例修改语句如下：

update member a set a.hallbin = ('CK'||(
        select b.membercode from member b 
        where b.membercode=a.membercode and b.hallbin='空白'
 )) where a.id in (
 '8a6ee1cd3f8125b4013fbdb326040707',
 '8a6ee1cd3f8125b4013fbdb326fa0708',
 '8a6ee1cd3f8125b4013fbdb327e80709',
 '8a6ee1cd3f8125b4013fbdb328e1070a',
 '8a6ee1cd3f8125b4013fbdb329dc070b',
 '8a6ee1cd3f8125b4013fbdb32ac8070c',
 '8a6ee1cd3f8125b4013fbdb32bc4070d',
 '8a6ee1cd3f8125b4013fbdb32cb3070e');

 

二、查询重复数据

表中入库申请单对应的明细表中barcode字段含有重复数据，将这些重复了的数据找出来。

示例查询语句如下：

select distinct barcode from inputapplydetail
where inputapply_id = ? group by barcode having count(barcode)>1
order by barcode

 

三、按月统计数据

-- 统计： 供货平台，发货订单数量，发货终端数量 (按照 月份)
select p.providername 供货平台,count(o.id) 发货订单数量,sum(o.producttotalQuantity) 发货终端数量,to_char(o.createdate,'yyyy-mm') 月份
from orders o 
left join admin p on o.provider_id=p.id
where o.shippingstatus = 2
group by p.providername,to_char(o.createdate,'yyyy-mm')
order by p.providername,to_char(o.createdate,'yyyy-mm') desc

  

四、根据外键名称查询该外键所在的表以及外键列名称

--根据外键名称查询该外键所在的表以及外键列名称 FKED8DCCEFCA10C3E FK4393E73CA10C3E
select * from user_cons_columns cl where cl.constraint_name = 'FK4393E73CA10C3E'

 

 五、Oracle正则表达式匹配汉字

select sn.* from sm.staff_number sn where length(sn.staff_number)*2=lengthb(sn.staff_number) and regexp_like(sn.staff_number,'^[^[:punct:]]*$');