1.单表中名字去重
第一种思路型写法
select *
from gbase.gis_bridge a
where bridge_id not in
(select bridge_id
from gbase.gis_bridge a
where a.bridge_name in
(select bridge_name
from gbase.gis_bridge a
group by bridge_name
having count(*) > 1)
and bridge_id not in (select min(bridge_id)
from gbase.gis_bridge
group by bridge_name
having count(*) > 1));
后来用了更为简单的方法
第二种简洁写法
select * from gbase.gis_bridge a
where a.bridge_id in(
select min(bridge_id) from gbase.gis_bridge a
group by bridge_name)
2.查询并截取特殊字符的字段
截取出名称中从0开始到第一个扩号之前的数据
select substr(section_name,0,instr(section_name,'(',1)-1) from gbase.gis_cable_section
3.将竖向记录两两合并为横向记录
create table ywgl.wq_test(
action_id number primary key,
bill_id number,
starttime varchar2(255),
action varchar2(255)
)
insert into ywgl.wq_test values(1,1,'20170321','s');
insert into ywgl.wq_test values(2,1,'20170322','e');
insert into ywgl.wq_test values(3,1,'20170323','s');
insert into ywgl.wq_test values(4,1,'20170324','e');
insert into ywgl.wq_test values(5,1,'20170324','s');
select t1.starttime,t2.starttime from
(select starttime,rownum rn from ywgl.wq_test where action='s') t1,
(select starttime,rownum rn from ywgl.wq_test where action='e') t2
where t1.rn=t2.rn(+)
4.Oracle分页查询语句
select *
from (select s.*, rownum rn
from (select * from ywgl.ywgl_action_record_gcph order by action_id) s
where rownum <= 4)
where rn >= 1
第二种
SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (select * from ywgl.ywgl_action_record_gcph order by action_id) A
)
WHERE RN BETWEEN 1 AND 4
5.Oracle获取不补0的月份
第一种：date型
-- end_date首先是date型
to_char(end_data, 'fmmm') billMonth
第二种: varchar型
-- 先将varchar转为date型 再获取
to_char(to_date(end_date, 'yyyy-mm-dd hh24:mi'), 'fmmm')
6.Date比较多种方式
第一种：通过varchar比较，适合数据量小的情况（因为大的情况用索引不方便）
where to_char(end_data,'yyyy-MM-dd')='2018-05-07'
第二种：直接date比较（end_date是date类型）
where end_date=date'2018-05-07'
7.字符串的截取
select bill_info_1,
substr(bill_info_1,
instr(bill_info_1, ':', 1, 2) + 1,
instr(bill_info_1, '<br>', 1, 2) -
(instr(bill_info_1, ':', 1, 2) + 1)) as "工程联系人",
substr(bill_info_1,
instr(bill_info_1, ':', 1, 3) + 1,
instr(bill_info_1, '<br>', 1, 3) -
(instr(bill_info_1, ':', 1, 3) + 1)) as "联系号码",
substr(bill_info_1,
instr(bill_info_1, ':', 1) + 1,
instr(bill_info_1, '<br>', 1) -
(instr(bill_info_1, ':', 1) + 1)) as "施工单位",
substr(bill_info_1, instr(bill_info_1, ':', 1, 4) + 1) as "客保编号" from bill_info where kind_1_id=3
实现效果如下：
7.1字符串截取
8.字符串的截取函数
第七节可以发现如果有规律的数据，如果写一大串来截取，还不如写个函数来实现呢？实现的效果就是给定一个字符串，给定一个特殊字符，最后给定想要获取哪个位置的数据即可。（此函数是由同事毕兄所写，感谢毕兄帮我解决了这个问题）
create or replace function func_ywgl_substr_column (v_content in varchar2,v_identifier in varchar2,v_location in number) return varchar2 is
v_result varchar2(4000);
v_identifier_length number;
v_instr number;
v_instr_1 number;
begin
v_identifier_length :=length(v_identifier);
v_instr :=instr(v_content,v_identifier,1,v_location);
if v_location =1 then
null;
else
v_instr_1 :=instr(v_content,v_identifier,1,v_location-1);
end if;
--if v_instr !=0 then
if v_location=1 then
v_result := substr(v_content ,1,instr(v_content,v_identifier,1,1)-1);
elsif v_instr =0 and v_instr_1<>0 then
v_result :=substr(v_content,instr(v_content,v_identifier,1,v_location-1)+v_identifier_length);
else
v_result := substr(v_content,v_identifier_length+instr(v_content,v_identifier,1,v_location-1),instr(v_content,v_identifier,1,v_location)-v_identifier_length-(instr(v_content,v_identifier,1,v_location-1)));
end if;
dbms_output.put_line(v_result);
return(v_result);
end;
下面就来验证下结果吧：
select bill_info_4,
func_ywgl_substr_column(bill_info_4, '$', 1) a,
func_ywgl_substr_column(bill_info_4, '$', 2) b,
func_ywgl_substr_column(bill_info_4, '$', 3) c,
func_ywgl_substr_column(bill_info_4, '$', 4) d,
func_ywgl_substr_column(bill_info_4, '$', 5) e,
func_ywgl_substr_column(bill_info_4, '$', 6) f,
func_ywgl_substr_column(bill_info_4, '$', 7) g,
func_ywgl_substr_column(bill_info_4, '$', 8) h
from ywgl.ywgl_bill_info
where ex_bill_id = 13508440
and kind_1_id = 6
运行结果如下：
图8.1 截取函数运行结果图
