1、字符串截取substr
=left(a1,2)意思是取A1左面就是前面开始去2个字符
=right(a1,3)及时A1右面 就是走后面开始取3个字符
要是中间取
=mid(a1,2,4)
就是走A1的第2个开始取4个 字符
就是 2 3 4 5 位~
oracle中如何取得字符串的左边的几位?
substr(string,m,n)
m从第几位取,如为负数表示从右至左取
n共取几位
--查找所有数据
select distinct(ftmobile),foprcode from
weblocation.x_boss_history
where to_char(ftime,'yyyy-mm-dd') = '2012-03-05'
and foprcode = '110002'
and
ftmobile in
(
select substr(ferroetext,1,11) from weblocation.x_sys_errorlog
t where to_char(fctime,'yyyy-mm-dd') ='2012-03-05'
and ferroetext not in ('D302未开通业务')
group by ferroetext
)
select * from weblocation.x_boss_history
--查找重复数据
select * from
(
select distinct(ftmobile),foprcode from
weblocation.x_boss_history
where
--to_char(ftime,'yyyy-mm-dd') = '2012-03-05'
--and
foprcode = '110002'
and
ftmobile in
(
select substr(ferroetext,1,11) from weblocation.x_sys_errorlog
t where to_char(fctime,'yyyy-mm-dd') ='2012-03-05'
and ferroetext not in ('D302未开通业务')
--and ferroetext like '05752____%'
group by ferroetext
)
) b,
(
select distinct(ftmobile),foprcode from
weblocation.x_boss_history
where
--to_char(ftime,'yyyy-mm-dd') = '2012-03-05'
--and
foprcode = '120002'
and
ftmobile in
(
select substr(ferroetext,1,11) from weblocation.x_sys_errorlog
t where to_char(fctime,'yyyy-mm-dd') ='2012-03-05'
and ferroetext not in ('D302未开通业务')
--and ferroetext like '05752____%'
group by ferroetext
)
) a
where
a.ftmobile = b.ftmobile
2、如何在EXCEL的一列中查找重复的数据
每月为各部门人员汇总各种劳务费,EXCEL表中每人只能一行,这就需要在“姓名”列中查重,眼睛看累死了!
学习了excel函数:countif。表达式:COUNTIF(数据区域,条件),意义:对数据区域内符合条件单元格计数
具体应用:
在“姓名”(列A)后插入一列(列B),在B2单元格输入公式“=IF(COUNTIF($A$2:A2,A2)>1,"重复","")”,然后将鼠标放在单元格右下角,变成黑色十字后,向下拖动,引用公式。
当然,姓名排序之后再查重更清楚!
更多推荐
oracle countif函数,字符串截取substr、excel表中查找重复数据countif
发布评论