一些关于Oracle的查询操作技巧

来源：互联网发布：养平原巨蜥知乎编辑：程序博客网时间：2024/05/17 02:25

1.查询某一张表中某一个字段的最后2位是XX

select * from [table] where substr([字段], -2)='XX'

2.查询某一张表中某一个字段从第0位开始取2位的查询结果

select substr([字段],0, 2) from [table]

3.更新某一张表中某一个字段中的X1替换为X2

update [table] set [字段]=replace([字段],'X1','X2')

4.根据年月日计算年龄的查询语句

floor(to_number(sysdate - birthday) / 365) as age

5.根据年龄段分组查询

select age, count(1) as peoplecount
from (select case
when to_char(sysdate, 'yyyy') - to_char(birthday, 'yyyy') >= '0' and
to_char(sysdate, 'yyyy') - to_char(birthday, 'yyyy') < '20' then
'20岁以下'

when to_char(sysdate, 'yyyy') - to_char(birthday, 'yyyy') >= '20' and
to_char(sysdate, 'yyyy') - to_char(birthday, 'yyyy') <= '30' then
'20-30'

when to_char(sysdate, 'yyyy') - to_char(birthday, 'yyyy') >= '30' and
to_char(sysdate, 'yyyy') - to_char(birthday, 'yyyy') <= '40' then
'30-40'

when to_char(sysdate, 'yyyy') - to_char(birthday, 'yyyy') >= '40' and
to_char(sysdate, 'yyyy') - to_char(birthday, 'yyyy') <= '50' then
'40-50'

when to_char(sysdate, 'yyyy') - to_char(birthday, 'yyyy') >= '50' and
to_char(sysdate, 'yyyy') - to_char(birthday, 'yyyy') <= '60' then
'50-60'

when to_char(sysdate, 'yyyy') - to_char(birthday, 'yyyy') > '60' then
'60岁以上'
else
'其他'
end as age
from [table]
where birthday is not null)
group by age
order by peoplecount

0 0