不知道下面的语句能否实现呢?
各位高人,麻烦帮看看,谢谢:select * from table where rowid in
(
select max(rowid) from table where name in
(select name from table where cz in ('0I','0J')
minus
select t.name from table t, table y where t.cz='0I' and y.cz='0J' and t.name=y.name
) group by name
) order by name
各位高人,麻烦帮看看,谢谢:select * from table where rowid in
(
select max(rowid) from table where name in
(select name from table where cz in ('0I','0J')
minus
select t.name from table t, table y where t.cz='0I' and y.cz='0J' and t.name=y.name
) group by name
) order by name
select t.*
from 表 t
where (select count(1)
from 表
where name=t.name
and cz!=t.cz
)=1
2.select t.*
from 表 t
where (select count(1)
from 表
where name=t.name
and cz=t.cz
)>1
and
not exists(select 1
from 表
where name=t.name
and cz=t.cz
and "date">t."date")
您的帖子已经被我们转载到本次评选官方网站的“专家在线答疑”区。
http://www.bestdba.cn/match_discussion.aspx在那里,进入本次评选终选的30位数据库工程师将与您展开积极的互动。他们会为您的问题提供满意的答案,此外,您还可以在“专家在线答疑”区提出新的问题并参与讨论。您的帖子位于:
http://www.bestdba.cn/match_discussion3.aspx?pointid=578&pointid2=1&pointid3=5&pcount=stc
非常感谢您对本次活动的支持!
处理流水(clls) 使用者(name) 操作(cz) 日期(date)
0001 A 0I 2008-08-15 00:00:01
0002 B 0I 2008-08-15 00:00:02
0003 C 0I 2008-08-15 01:01:01
0004 D 0I 2008-08-15 02:00:02
0005 A 0J 2008-08-16 00:00:01
0006 B 0J 2008-08-16 00:00:02
0007 C 0J 2008-08-16 02:01:01
0008 D 0J 2008-08-16 02:00:02
0009 C 0J 2008-08-16 02:01:01
0010 D 0J 2008-08-16 02:01:020011 A XX 2008-08-15 00:00:01
0012 B TT 2008-08-15 00:00:02
0013 C YY 2008-08-15 01:01:01
0014 D QQ 2008-08-15 02:00:02
select t.*
from 表 t
where t.cz in ('0I','0J')
and
(select count(1)
from 表
where name=t.name
and cz!=t.cz
)=1
2.select t.*
from 表 t
where t.cz in ('0I','0J')
and
(select count(1)
from 表
where name=t.name
and cz=t.cz
)>1
and
not exists(select 1
from 表
where name=t.name
and cz=t.cz
and "date">t."date")
create table tb
(
clls varchar2(20),
name varchar2(20),
cz varchar2(10),
"date" date
)
--插入测试数据
insert into tb
select '0001','A','0I',to_date('2008-08-15 00:00:01','yyyy-mm-dd HH24:MI:SS') from dual
union
select '0002','B','0I',to_date('2008-08-15 00:00:02','yyyy-mm-dd HH24:MI:SS') from dual
union
select '0003','C','0I',to_date('2008-08-15 01:01:01','yyyy-mm-dd HH24:MI:SS') from dual
union
select '0004','D','0I',to_date('2008-08-15 02:00:02','yyyy-mm-dd HH24:MI:SS') from dual
union
select '0005','A','0J',to_date('2008-08-16 00:00:01','yyyy-mm-dd HH24:MI:SS') from dual
union
select '0006','B','0J',to_date('2008-08-16 00:00:02','yyyy-mm-dd HH24:MI:SS') from dual
union
select '0007','C','0J',to_date('2008-08-16 02:01:01','yyyy-mm-dd HH24:MI:SS') from dual
union
select '0008','D','0J',to_date('2008-08-16 02:00:02','yyyy-mm-dd HH24:MI:SS') from dual
union
select '0009','C','0J',to_date('2008-08-16 02:01:01','yyyy-mm-dd HH24:MI:SS') from dual
union
select '0010','D','0J',to_date('2008-08-16 02:01:02','yyyy-mm-dd HH24:MI:SS') from dual
union
select '0011','A','XX',to_date('2008-08-15 00:00:01','yyyy-mm-dd HH24:MI:SS') from dual
union
select '0012','B','TT',to_date('2008-08-15 00:00:02','yyyy-mm-dd HH24:MI:SS') from dual
union
select '0013','C','YY',to_date('2008-08-15 01:01:01','yyyy-mm-dd HH24:MI:SS') from dual
union
select '0014','D','QQ',to_date('2008-08-15 02:00:02','yyyy-mm-dd HH24:MI:SS') from dual
--测试1
select t.*
from tb t
where t.cz in ('0I','0J')
and
(select count(1)
from tb
where name=t.name
and cz='0J'
)=1
order by t.name--测试2
select t.*
from tb t
where t.cz in ('0I','0J')
and
(select count(1)
from tb
where name=t.name
and cz=t.cz
)>1
and
not exists(select 1
from tb
where name=t.name
and cz=t.cz
and to_number(clls)>to_number(t.clls))--删除测试环境
drop table tb;
(1)测试语句1,返回的结果为
0001 A 0I 2008-8-15 上午 12:00:01
0005 A 0J 2008-8-16 上午 12:00:01
0002 B 0I 2008-8-15 上午 12:00:02
0006 B 0J 2008-8-16 上午 12:00:02实际需要的数据应为:
0001 A 0I 2008-8-15 上午 12:00:01
0005 A 0J 2008-8-16 上午 12:00:01
0002 B 0I 2008-8-15 上午 12:00:02
0006 B 0J 2008-8-16 上午 12:00:020003 C 0I 2008-8-15 上午 01:01:01
0007 C 0J 2008-8-16 上午 02:01:010004 D 0I 2008-8-15 上午 02:00:02
0008 D 0J 2008-8-16 上午 02:00:02因为C跟D的'0I'和'0J'操作记录也有成对出现的情况,需要滤掉..因此语句1检索出的结果还未充分满足条件.(2)对于语句2,执行返回的结果如下:
0009 C 0J 2008-8-16 上午 02:01:01
0010 D 0J 2008-8-16 上午 02:01:02但如果数据表中增加如下的记录:
insert into tb
select '0015','C','0I',to_date('2008-08-18 02:01:02','yyyy-mm-dd HH24:MI:SS') from dual
union
select '0016','D','0J',to_date('2008-08-18 02:02:02','yyyy-mm-dd HH24:MI:SS') from dual
union
select '0017','E','0I',to_date('2008-08-18 02:03:02','yyyy-mm-dd HH24:MI:SS') from dual
union
select '0018','F','0J',to_date('2008-08-18 02:04:02','yyyy-mm-dd HH24:MI:SS') from dual那么,用语句2检索出来的结果为:
0009 C 0J 2008-8-16 上午 02:01:01
0015 C 0I 2008-8-18 上午 02:01:02
0016 D 0J 2008-8-18 上午 02:02:02与实际需要检索出的结果有出入.实际需要检索出的结果应为:
0009 C 0J 2008-8-16 上午 02:01:01
0010 D 0J 2008-8-16 上午 02:01:020017 E 0I 2008-8-18 上午 02:03:02
0018 F 0J 2008-8-18 上午 02:04:02
0001 A 0I 2008-8-15 上午 12:00:01
0005 A 0J 2008-8-16 上午 12:00:01
0002 B 0I 2008-8-15 上午 12:00:02
0006 B 0J 2008-8-16 上午 12:00:02
========>
必须对应?一条?多条?第二个结果集是什么意思?
002 A 0J注:
由于A用户clls为003的0I的记录没有 与之对应 的0J记录,因此排除在结果集外.