阿表结构如下:jh varchar2(16) cshrq date xch varchar2(10)
c100-52 2008-05-11 F121
c100-52 2008-05-11 F32
c100-52 2008-05-11 F172
c98-98 2008-02-23 F21
c100-52 2007-08-25 F32
c100-52 2006-04-21 F32
c100-52 2006-04-21 F172
c98-98 2006-06-09 F21
c98-98 2006-06-09 F152查询条件是对比2008年度及2006年度,找到所有jh和xch对应相等的所有记录,如上例可查到的正确结果为:
c100-52 2008-05-11 F32
c100-52 2008-05-11 F172
c100-52 2006-04-21 F32
c100-52 2006-04-21 F172
c98-98 2008-02-23 F21
c98-98 2006-06-09 F21
c100-52 2008-05-11 F121
c100-52 2008-05-11 F32
c100-52 2008-05-11 F172
c98-98 2008-02-23 F21
c100-52 2007-08-25 F32
c100-52 2006-04-21 F32
c100-52 2006-04-21 F172
c98-98 2006-06-09 F21
c98-98 2006-06-09 F152查询条件是对比2008年度及2006年度,找到所有jh和xch对应相等的所有记录,如上例可查到的正确结果为:
c100-52 2008-05-11 F32
c100-52 2008-05-11 F172
c100-52 2006-04-21 F32
c100-52 2006-04-21 F172
c98-98 2008-02-23 F21
c98-98 2006-06-09 F21
解决方案 »
- oracle数据库导出,如何不导出Blob字段内容
- 在线急等回复``
- 高分求助一个select语句,结帖又重发, 还是有点问题
- 急急!PRO*C/C++程序能否处理存储过程或函数返回的游标结果(处理多行多列的返回结果)?
- 怎样在oracle中获取两个时间之间的秒数差 这两个时间字段是sysdate
- 很简单的执行存储过程的问题
- 在调用JAVA存储过程时出现如下错误:ORA-29532: Java 调用被未捕获的 Java 例外终止
- Oracle portal是来做什么的?
- 关于 dblink
- oracle出现频率统计
- 用pl/sql将excel数据导入到oracle中的问题。
- TNS 配置 SSL 连接方式后,连接不上,请问哪位知道怎么配置SSL连接方式??
(select jh , xch , to_char(cshrq,'YYYY') yy from tb where to_char(cshrq,'YYYY') in ('2006','2008') group by jh , xch , to_char(cshrq,'YYYY') having count(*) > 1) n
where n.jh = m.jh and n.xch = m.xch and to_char(n.cshrq,'YYYY') = to_char(m.cshrq,'YYYY')
)
--试下
select * from table_name t where exists(
select jh,xch,count(1) from table_name t1 where t.jh=t1.jh and t.xch=t1.xch and to_char(t1.cshrq,'yyyy') in('2006','2008') group by jh,xch having count(1)>1) and to_char(t.cshrq,'yyyy') in('2006','2008');
insert into tb values('c100-52' , to_date('2008-05-11','YYYY-MM-DD HH24:MI:SS') , 'F121')
insert into tb values('c100-52' , to_date('2008-05-11','YYYY-MM-DD HH24:MI:SS') , 'F32')
insert into tb values('c100-52' , to_date('2008-05-11','YYYY-MM-DD HH24:MI:SS') , 'F172')
insert into tb values('c98-98' , to_date('2008-02-23','YYYY-MM-DD HH24:MI:SS') , 'F21')
insert into tb values('c100-52' , to_date('2007-08-25','YYYY-MM-DD HH24:MI:SS') , 'F32')
insert into tb values('c100-52' , to_date('2006-04-21','YYYY-MM-DD HH24:MI:SS') , 'F32')
insert into tb values('c100-52' , to_date('2006-04-21','YYYY-MM-DD HH24:MI:SS') , 'F172')
insert into tb values('c98-98' , to_date('2006-06-09','YYYY-MM-DD HH24:MI:SS') , 'F21')
insert into tb values('c98-98' , to_date('2006-06-09','YYYY-MM-DD HH24:MI:SS') , 'F152')select m.* from tb m where exists (select 1 from
(select distinct jh , xch from tb where to_char(cshrq,'YYYY') in ('2006','2008') group by jh , xch having count(*) = 2) n
where n.jh = m.jh and n.xch = m.xch)drop table tb
from (select jh,
cshrq,
xch,
row_number() over(partition by jh, xch order by cshrq) rn
from a)
where rn >= 2
create table tb(jh varchar2(16) , cshrq date ,xch varchar2(10))
insert into tb values('c100-52' , to_date('2008-05-11','YYYY-MM-DD HH24:MI:SS') , 'F121')
insert into tb values('c100-52' , to_date('2008-05-11','YYYY-MM-DD HH24:MI:SS') , 'F32')
insert into tb values('c100-52' , to_date('2008-05-11','YYYY-MM-DD HH24:MI:SS') , 'F172')
insert into tb values('c98-98' , to_date('2008-02-23','YYYY-MM-DD HH24:MI:SS') , 'F21')
insert into tb values('c100-52' , to_date('2007-08-25','YYYY-MM-DD HH24:MI:SS') , 'F32')
insert into tb values('c100-52' , to_date('2006-04-21','YYYY-MM-DD HH24:MI:SS') , 'F32')
insert into tb values('c100-52' , to_date('2006-04-21','YYYY-MM-DD HH24:MI:SS') , 'F172')
insert into tb values('c98-98' , to_date('2006-06-09','YYYY-MM-DD HH24:MI:SS') , 'F21')
insert into tb values('c98-98' , to_date('2006-06-09','YYYY-MM-DD HH24:MI:SS') , 'F152')select m.* from tb m where exists (select 1 from
(select distinct jh , xch from tb where to_char(cshrq,'YYYY') in ('2006','2008') group by jh , xch having count(*) = 2) n
where n.jh = m.jh and n.xch = m.xch) and to_char(cshrq,'YYYY') in ('2006','2008') order by m.jh , m.xch
drop table tb
from a
where (jh, xch) in
(select jh, xch
from (select jh, xch, count(*) cu from a group by jh, xch)
where cu > 1)
5 第四油矿 C50 四矿三工区 10C60-126 F172 2008-4-21 9
6 第四油矿 C50 四矿三工区 10C60-126 F172 2008-6-8 10
23 第四油矿 C55 四矿一工区 10C74-132 F21 2006-9-22
24 第四油矿 C55 四矿一工区 10C74-132 F21 2006-9-22
出现这两对不符条件的结果,其他都正确!
(select * from tb where to_char(cshrq, 'yyyy')= '2006') n
where m.jh = n.jh and m.xch =n.xch
union all
select distinct m.* from (select * from tb where to_char(cshrq, 'yyyy')= '2008') m,
(select * from tb where to_char(cshrq, 'yyyy')= '2006') n
where m.jh = n.jh and m.xch =n.xch试一下这个,我测过好像没问题
(
select jh,cshrq,xch from yourtable a
where to_char(cshrq,'YYYY')='2008'
and exists(select * from yourtable where jh=a.jh and xch=a.xch and to_char(cshrq,'YYYY')='2006')
union all
select jh,cshrq,xch from yourtable a
where to_char(cshrq,'YYYY')='2006'
and exists(select * from yourtable where jh=a.jh and xch=a.xch and to_char(cshrq,'YYYY')='2008')
)
order by jh,cshrq desc,xch
select jh, cshrq, xch, rownum num
from table_name tn
where to_char(tn.cshrq, 'yyyy') in ('2008', '2006')
)
select td.jh, td.cshrq, td.xch
from temp_data td, temp_data td2
where td.jh = td2.jh
and td.xch = td2.xch
and td.num <> td2.num