一、更新关联数据
表中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:]]*$');