数据库查询,在线等待

[grey_whp]

表A
id status c d
------------------------------------
11 2 19 48
12 2 29 58
13 2 39 88

表B
id status c d
--------------------------------------
19 11 y 48
29 11 n 48
39 11 y 48
119 12 y 58
129 12 n 58
139 12 y 58


现在需要查询再A表中d字段中出现的记录在B表中id号码为最大的记录,并且B表中字段c='y',再根据筛选出来的(必定为单一记录)B表中status对应记录查询A表中ID号码对应的记录!

在线等待立即给分!不够再加!

[pengdali]

select * from 表A aa,(
SELECT * FROM 表B tem WHERE ID=(SELECT MAX(ID) FROM 表B WHERE d=tem.d WHERE C='Y')) bb
where aa.id=bb.status

[pengdali]

楼主最好把要的结果集列出来。

[vrv0129]

select * from A where A.d in (select max(B.d) from B where B.c='y')

[vrv0129]

ok!问题解决了，请加分

[grey_whp]

揭帖啦~~

[jiezhi]

A表中d字段中出现的记录在B表中id号码为最大的记录,并且B表中字段c='y'(如果有多個d在b表中出現怎麼辦？需要分組嗎？)-->>
select max(b.id),b.staus from a,b x where a.d=b.d and b.c='y'
------------------
再根据筛选出来的(必定为单一记录)B表中status对应记录查询A表中ID号码对应的记录!
???
不理解。

[bzszp]

SELECT * FROM A WHERE A.ID=(SELECT MAX(B.ID) FROM A,B WHERE B.STATUS=A.ID(+) WHERE B.C='Y');

[zhngk]

SELECT A.ID FROM A WHERE A.ID=(SELECT B.STATUS FROM A,B
WHERE A.D=B.ID
AND B.ID=MAX(B.ID)
AND B.C='Y');

[beckhambobo]

select * from a where (c,id) in (select max(id),status from a,b where a.d=b.d and b.c='y' group by status)

[liandu]

create table a
(
id varchar2(4),
status varchar2(2),
c varchar2(2),
d varchar2(4)
)
/
create table b as
select * from a;

insert into a(id,status,c,d)
values('11','2','19','48');
insert into a(id,status,c,d)
values('12','2','29','58');
insert into a(id,status,c,d)
values('13','2','39','68');

insert into b(id,status,c,d)
values('19','11','y','48');
insert into b(id,status,c,d)
values('29','11','n','48');
insert into b(id,status,c,d)
values('39','11','y','48');
insert into b(id,status,c,d)
values('119','12','y','58');
insert into b(id,status,c,d)
values('129','12','n','58');
insert into b(id,status,c,d)
values('139','12','y','58');
建表并初始化成功！

运行查询时不对呀，为什么？请DX解释一下：
bzszp(SongZip)兄写的： SELECT * FROM A WHERE A.ID=(SELECT MAX(B.ID) FROM A,B WHERE B.STATUS=A.ID(+) WHERE B.C='Y');不能运行，提示 缺少右括号

我也写了一个（初学，一看就知道很菜的：））：
select a.id from a where a.id=(select b.status from b where c='y' and b.id=(select max(b.id) from b where b.d in (select a.d from a)));
在pl/sql里运行错误，但在sql*plus里正确？由结果为证哦,id=11