【ORACLE数据库中,现有两个表A和B。表描述和结构内容如下】
表A:
ID NAME PN SN
1 A1 B1 C1
2 A2 B2 C2
3 A2 B2 88888
4 A2 B2 88888
5 A3 B5 C5
6 A3 B6 88888
表B:
ID NAME PN SN DESC
001 A1 B1 C1 D1
002 A1 B1 88888 D2
003 A1 B1 88888 D3
004 A2 B2 C2
005 A2 B2 88888 D5
006 A2 B2 88888 D6
007 A2 B2 88888 D7
008 A2 B2 88888 D8
009 A2 B3 88888 D9
010 A3 B5 C5 D10
【A、B表说明】
A表是某次交易库单据的货品记录。
B表是仓库库存的记录。
A、B表NAME、PN加起来可重复,SN可以是某特定字符串88888,DESC可以为空,且只作为参考描述,不作为查询条件。
B表的ID不重复。
【查询需求】
现在要联合A、B表,根据A表的NAME、PN、SN三个字段在B表里查询ID,如果没有则ID为空。如果有多条符合的ID则取其中一条。并且查询结果中ID不可以重复。
查询的一种可能结果如下:
ID NAME PN SN BID DESC
1 A1 B1 C1 001 D1
2 A2 B2 C2 004
3 A2 B2 88888 005 D5
4 A2 B2 88888 006 D6
5 A3 B5 C5 010 D10
6 A3 B6 88888
【注意】
上面的结果只是一种可能的结果,还有其他可能的结果。
其中结果中第3、4行的BID和DESC可以是B表中任何NAME='A2' AND PN='B2' AND SN='88888'的行,只要不重复就可以。
表A:
ID NAME PN SN
1 A1 B1 C1
2 A2 B2 C2
3 A2 B2 88888
4 A2 B2 88888
5 A3 B5 C5
6 A3 B6 88888
表B:
ID NAME PN SN DESC
001 A1 B1 C1 D1
002 A1 B1 88888 D2
003 A1 B1 88888 D3
004 A2 B2 C2
005 A2 B2 88888 D5
006 A2 B2 88888 D6
007 A2 B2 88888 D7
008 A2 B2 88888 D8
009 A2 B3 88888 D9
010 A3 B5 C5 D10
【A、B表说明】
A表是某次交易库单据的货品记录。
B表是仓库库存的记录。
A、B表NAME、PN加起来可重复,SN可以是某特定字符串88888,DESC可以为空,且只作为参考描述,不作为查询条件。
B表的ID不重复。
【查询需求】
现在要联合A、B表,根据A表的NAME、PN、SN三个字段在B表里查询ID,如果没有则ID为空。如果有多条符合的ID则取其中一条。并且查询结果中ID不可以重复。
查询的一种可能结果如下:
ID NAME PN SN BID DESC
1 A1 B1 C1 001 D1
2 A2 B2 C2 004
3 A2 B2 88888 005 D5
4 A2 B2 88888 006 D6
5 A3 B5 C5 010 D10
6 A3 B6 88888
【注意】
上面的结果只是一种可能的结果,还有其他可能的结果。
其中结果中第3、4行的BID和DESC可以是B表中任何NAME='A2' AND PN='B2' AND SN='88888'的行,只要不重复就可以。
我觉得应该是B表左连接A表。查询结果数量和A表相等,A表的记录全部出现在结果中。
我已经试过以下方法了都不行:
1、直接左连接。由于B表中存在多条NAME+PN+SN相同、ID不同的记录,所以直接左连接不可以。
2、在BID字段使用(select id from b where name=a.name and pn=a.pn and sn=a.sn and rownum=1) as bid来代替。这样记录数正确。可是会出现重复记录。
3、由于A表的记录数量的不确定性,必须使用1条SQL语句实现,所以什么循环之类的办法也不可以。
SELECT ID,NAME,PN,BID,DESC1 FROM (
SELECT ID,NAME,PN,BID,DESC1, ROW_NUMBER()OVER(PARTITION BY ID ORDER BY BID) T FROM
(SELECT A.ID,A.NAME,A.PN,B.ID BID,B.DESC1
FROM A1 A LEFT JOIN B1 B
ON A.NAME=B.NAME AND A.PN=B.PN AND A.SN=B.SN) )WHERE T=1
用一条SQL我想了一天都没想到,高手做出来了叫我来看看啊.
DUANZILIN是高手,请他来看看.水清也是牛人.看他们能不能帮解决?
fuxf(布衣)查出来是几条记录,1条吗?我要的是查出来的行数和A表一样。
那你就(select distinct id from b where name=a.name and pn=a.pn and sn=a.sn and rownum=1) as bid 不行吗
select c.id,c.name,c.pn,c.sn,b.id as bid,b.desc
from (select a.id,a.name,a.pn,a.sn,row_number()over(partition by a.name,a.pn,a.sn order by a.id) as rn1 from a) c,
(select b.id,b.name,b.pn,b.sn,b.desc,row_number()over(partition by b.name,b.pn,b.sn order by b.id ) as rn2 from b) d
where c.name=d.name and c.pn=d.pn and c.sn=d.sn and c.rn1=d.rn2
union
select a.id,a.name,a.pn,a.sn,null as bid,null as "desc"
from a
where not exist(select 1 from b
where a.name=b.name and a.pn=b.pn and a.sn=b.sn)
select c.*,bb.desc
from
(select A.ID,a.NAME,A.PN,A.SN,MIN(B.ID) as BID
FROM A
left outer join B on A.Name=b.name and a.pn=b.pn and a.sn=b.sn
group by A.ID,a.NAME,A.PN,A.SN ) c
left join B bb on bb.id=c.bid
(
select * from b b1
where id in (select min(id) from b b2 group by name,pn,sn)
) tt
where a.name=tt.name(+) and a.pn=tt.pn(+) and a.sn=tt.sn(+);