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
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