怎样处理几个表之间的关联--过江项羽

luhongjun 2002-05-14 03:47:45

有三个表：A，B，C分别有两个字段并且有一个相同的字段。
在A表中字段为ID和NUMBER，其中ID是可重复的。
在B表中字段也为ID和NUMBER，其中ID也是可重复的。B表中的所有ID值都在A表中出现过。
在C表中字段为ID和NAME，ID是唯一的，但ID是一个全集，既在A表中ID可能出现的所有值都在C表的ID中存在

例如：
A表 B表 C表
ID NUMBER ID NUMBER ID NAME
1 10 1 12 1 A1
1 15 1 25 2 A2
1 28 3 16 3 A3
2 20 10 14 4 A4
2 13 5 A5
3 18 6 A6
4 20 7 A7
5 6 8 A8
6 10 9 A9
6 10 10 B1
6 11 11 B2
6 8 12 C1
7 12
8 10
9 11
10 18
10 1

要求：产生一个查询或视图D。
条件是：包括ID ，NAME，NUMBER三个字段，其中NUMBER的值要大于10。并且NUMBER是A表
相同ID的NUMBER的和减去B表相同ID的NUMBER的最大值。如果在A表中存在但B表
中不存在则相当于B表中此ID的NUMBER的值为0。如果在C表中存在但A表中不存在
则NUMBER可记0。
上例中最后结果应该是：
ID NAME NUMBER
1 A1 28---------------（10+15+28-25=28）
2 A2 33---------------（ 20+13- 0=33）
3 A3 2---------------（ 18-16= 2）----应剔除
4 A4 20---------------（ 20-0=20）
5 A5 6---------------（ 6-0=28）----应剔除
6 A6 39--------------（10+10+11+8-0=39）
7 A7 12---------------（ 12-0=12）
8 A8 10---------------（ 10-0=10）
9 A9 11---------------（ 11-0=11）
10 B1 4---------------（ 18-14=4）----应剔除
11 B2 0---------------（ 0-0=0）----应剔除
12 C1 0---------------（ 0-0=0）----应剔除
所以最后返回的结果为：
ID NAME NUMBER
1 A1 28---------------（10+15+28-25=28）
2 A2 33---------------（ 20+13- 0=33）
4 A4 20---------------（ 20-0=20）
6 A6 39--------------（10+10+11+8-0=39）
7 A7 12---------------（ 12-0=12）
8 A8 10---------------（ 10-0=10）
9 A9 11---------------（ 11-0=11）

三个表中任何一个表中的数量都非常大，A表有1300多万记录，B表有15万多记录，C表有10万多记录
希望找到好一点的方法解决此问题，行营的检索速度能快一些。
数据库使用的是ORACLE8I。各位大虾多多帮忙。

...全文

159 34 打赏收藏转发到动态举报

写回复

用AI写文章

34 条回复

切换为时间正序

请发表友善的回复…

发表回复

yuyulily 2002-05-28

打赏
举报

老师：
今天试的存储过程我觉得有问题，你可以把过程语句拷到SQL PLUS试试，我想应该有错误，过程不能直接返回结果集，可以在你的三个临时表后再建一个临时表，把SELECT 的结果INTO到临时表中，查看记录到临时表中查询，或使用游标。

luhongjun 2002-05-16

打赏
举报

效率很低，三千建议使用物化视图。可以实现实时更新，和触发器差不多。把时间花在平时。
物化视图例子如下，也许对大家有用。

CREATE MATERIALIZED VIEW mv1 REFRESH FAST ON COMMIT
BUILD IMMEDIATE
AS SELECT t.month, p.prod_name, SUM(f.sales) AS sum_sales
FROM time t, product p, fact f
WHERE f.curDate = t.curDate AND f.item = p.item
GROUP BY t.month, p.prod_name;

luhongjun 2002-05-16

打赏
举报

最后结果：

select c.JSZH , --驾驶证号
c.XM,
--c.YXQKS, --有效期驶
--c.YXQJS, --有效期止
--c.ZKSJ, --制卡日期
--c.ZKDWDM, --制卡单位代码
--c.ZKRDM, --制卡人代码
--c.ZKRXM, --制卡人姓名
c.XQDM, --辖区代码
x.T_WZFZ
from (select JSZH, sum(WZFZ) T_WZFZ from JTUSER.JG_WZXXB group by JSZH
union all
select JSZH, -max(WZJF) T_WZFZ from JTUSER.JG_WZJSYXFB group by JSZH) x,
(select JSZH,XM,YXQKS,YXQJS,ZKSJ,ZKDWDM,ZKRDM,ZKRXM,XQDM
from JTUSER.JG_JFKXXB z where ZKSJ in (select max(ZKSJ) from JTUSER.JG_JFKXXB where JSZH =z.JSZH)) c
where c.JSZH = x.JSZH and x.T_WZFZ>=12

KingSunSha 2002-05-15

打赏
举报

select x.id, c.name,c.name,c.birthday nvl(x.no,0) - nvl(y.no,0) result

在birthday后面少了一个,号：）

KingSunSha 2002-05-15

打赏
举报

luhongjun(过江项羽)：
请注意对B表进行的是左连接，而你的代码中用的是完全连接，会导致在B表中不存在的ID不返回

luhongjun 2002-05-15

打赏
举报

我先测试一下，然后回复。

三千你的第一个回复：
select x.id, c.name, nvl(x.no,0) - nvl(y.no,0) result
from (select id, sum(no) no from a group by id) x,
(select id, max(no) no from b group by id) y, c
where c.id = x.id and c.id = y.id and nvl(x.no,0)-nvl(y.no,0)>=10;
我不知为什么没有得到最后结果？
and c.id = y.id 会使最后结果记录小于B表即在B表中不存在的ID就无法返回了，并且一条记录会重复多次。还有如果我C表中存在其他的字段，例如Birthday，
select x.id, c.name,c.name,c.birthday nvl(x.no,0) - nvl(y.no,0) result 就会出现列数目错误的提示。
我是在视图中测试的我的SQL拷贝如下
select x.JSZH ,
c.XM ,
(x.T_WZFZ - y.T_WZFZ) AS T_WZFZ
from (select JSZH, sum(WZFZ) as T_WZFZ from JTUSER.JG_WZXXB group by JSZH) x,
(select JSZH, max(WZJF) as T_WZFZ from JTUSER.JG_WZJSYXFB group by JSZH) y,
JTUSER.JG_JFKXXB c
where c.JSZH = x.JSZH and c.JSZH=y.JSZH and (x.T_WZFZ - y.T_WZFZ) >= 10

完成时间用了4分50秒。

好了,我继续看下面的回复.

TR@SOE 2002-05-15

打赏
举报

哈哈，还是老项羽魅力无穷，连KINGSUN都出现了。

说到KINGSUN，你个兔崽子怎么隐藏了这么久，也不和我联系！真TNND。

guo 2002-05-15

打赏
举报

项羽兄好,很长时间看不到你了,最近什么?
弱水兄已经将正确答案贴出来了,但从速度上讲我建议将查询改为:
create global temporary table t1 as select id, sum(no) no from a group by id;
create global temporary table t2 as select id, max(no) no from b group by id;
select x.id, c.name, nvl(x.no,0) - nvl(y.no,0) result
from t1 x,t2 y,c
where c.id = x.id (+)
and c.id = y.id (+)
and nvl(x.no,0) - nvl(y.no,0) > 10;

一般对大量数据表的聚集计算的结果进行直接关联不如首先创建临时表,会快很多.

KingSunSha 2002-05-15

打赏
举报

再来考虑性能的优化：
刚才的做法中有两个左连接，通常左连接对性能不利，所以能不用的话最好。
假定表A/B中的NO都是正整数或0，那么可以判定当A中没有记录的时候，SUM(A.NO) - MAX(B.NO)永远不会大于0，所以可以把从C->A的连接变成全连接，如下：
select x.id, c.name, x.no - nvl(y.no,0) result
from (select id, sum(no) no from a group by id) x,
(select id, max(no) no from b group by id) y, c
where c.id = x.id
and c.id = y.id (+)
and x.no - nvl(y.no,0) > 10;
这句sql的性能应该已经有所提高了。

再来考虑能不能把C->B的左连接去掉，实际上获得SUM(A.NO)-MAX(B.NO)可以用这样的方式完成：即用UNION ALL把-MAX(NO)并入A表中，在对NO取SUM就得到了相同的结果，语句如下：
SELECT C.ID, C.NAME, SUM(NO) RESULT
FROM (SELECT ID,SUM(NO) NO FROM A GROUP BY ID
UNION ALL
SELECT ID, -MAX(NO) NO FROM B GROUP BY ID) X, C;
WHERE C.ID = X.ID;

上述两种写法究竟哪种更快，要在数据库中做测试才能确定。而且我想真正的表不是这么简单的，各种因素对查询的影响很难单单用规则来讨论，还是以实测为准。

KingSunSha 2002-05-15

打赏
举报

IT-司马青衫 2002-05-15

打赏
举报

发现项鱼哥好象数据库理论没学好

KingSunSha 2002-05-15

打赏
举报

1、结果是重复结果还是没有结果？如果有重复纪录，那说明JTUSER.JG_JFKXXB这个表中有重复的JSZH，其他两个子查询已经通过group by保证了JSZH的唯一性

2、ORA-12730:指定的列名数无效
这个错误居然在文档里找不到，真奇怪

rak 2002-05-15

打赏
举报

我试了一下..弱水的第二种方法快些..

SELECT x.id, x.aNumber - ISNULL(w.bNumber, 0) AS ResultNumber, c.Name
FROM (SELECT id, SUM(Number) AS bNumber
FROM b
GROUP BY id) w RIGHT OUTER JOIN
c ON w.id = c.ID FULL OUTER JOIN
(SELECT id, SUM(number) AS aNumber
FROM a
GROUP BY id) x ON c.ID = x.id
WHERE (x.aNumber - ISNULL(w.bNumber, 0) > 10)

rak 2002-05-15

打赏
举报

OK... 搞定
不过性能..
估计不会很好.
弱水的方法比较多...

SELECT x.id, x.aNumber - ISNULL(w.bNumber, 0) AS ResultNumber, c.Name
FROM (SELECT id, SUM(number) AS aNumber
FROM a
GROUP BY id) x LEFT OUTER JOIN
(SELECT id, SUM(Number) AS bNumber
FROM b
GROUP BY id) w ON x.id = w.id FULL OUTER JOIN
c ON x.id = c.ID
WHERE (x.aNumber - ISNULL(w.bNumber, 0) > 10)

Result :

ID ResultNumber Name

1 16 a1
2 33 a2
4 20 a5
6 39 a7
7 12 a8
9 11 a10

rak 2002-05-15

打赏
举报

:)...

SQL-Server

SELECT x.id, x.aNumber, w.bNumber AS bNumber, c.Name
FROM (SELECT id , SUM(number) AS aNumber
FROM a
GROUP BY id) as x LEFT OUTER JOIN
c ON x.id = c.ID LEFT OUTER JOIN
(select id,Sum(Number) as bNumber
From b
Group by id) as w ON x.id = w.ID

Result

id aNumber bNumber name

1 53 37 a1
10 19 14 b1
2 33 NULL a2
3 18 16 a3
4 20 NULL a5
5 6 NULL a6
6 39 NULL a7
7 12 NULL a8
8 10 NULL a9
9 11 NULL a10

????这个 bNumber 的NULL怎么去掉..或有没有函数计算一下.
就不清楚了..:

luhongjun 2002-05-15

打赏
举报

加上左连接
select x.JSZH ,
c.XM ,
(x.T_WZFZ - nvl(y.T_WZFZ,0) AS T_WZFZ
from (select JSZH, sum(WZFZ) T_WZFZ from JTUSER.JG_WZXXB group by JSZH) x,
(select JSZH, max(WZJF) as T_WZFZ from JTUSER.JG_WZJSYXFB group by JSZH) y,
JTUSER.JG_JFKXXB c
where c.JSZH = x.JSZH and c.JSZH=y.JSZH and (x.T_WZFZ - nvl(y.T_WZFZ,0)) >= 10
效果还是没变,重复记录仍然存在.

select x.id, c.name,c.name,c.birthday nvl(x.no,0) - nvl(y.no,0) result
我实际中没有此错误,但仍然提示列个数错误.
我的语句为:
select x.JSZH , --驾驶证号
c.XM , --姓名
c.DAH, --档案号
c.YXQKS, --有效期驶
c.YXQJS, --有效期止
c.ZKSJ, --制卡日期
c.ZKDWDM, --制卡单位代码
c.ZKRDM, --制卡人代码
c.ZKRXM, --制卡人姓名
c.SCSJ,
c.XQDM, --辖区代码
c.JSZH18, --驾驶证号
(x.T_WZFZ - nvl(y.T_WZFZ,0)) AS T_WZFZ
from (select JSZH, sum(WZFZ) T_WZFZ from JTUSER.JG_WZXXB group by JSZH) x,
(select JSZH, max(WZJF) T_WZFZ from JTUSER.JG_WZJSYXFB group by JSZH) y,
JTUSER.JG_JFKXXB c
where c.JSZH = x.JSZH (+)
and c.JSZH=y.JSZH (+)
and (x.T_WZFZ - nvl(y.T_WZFZ,0)) >= 12
编译出现"ORA-12730:指定的列名数无效"的错误提示
去掉
c.DAH, --档案号
c.YXQKS, --有效期驶
c.YXQJS, --有效期止
c.ZKSJ, --制卡日期
c.ZKDWDM, --制卡单位代码
c.ZKRDM, --制卡人代码
c.ZKRXM, --制卡人姓名
c.SCSJ,
c.XQDM, --辖区代码
c.JSZH18, --驾驶证号
无错误,增加任何一个字段都有此错误.

aawolf 2002-05-14

打赏
举报

老天，怎么老早不见的今天都上来了？看星星看的我头晕。
不过数据库我不在行，凑凑热闹：）

KingSunSha 2002-05-14

打赏
举报

上面的结果少了一条8/A8/10，因为项羽说的是大于10，我没看仔细，把最后一个条件加上等于就可以
...
and nvl(x.no,0) - nvl(y.no,0) >= 10;

KingSunSha 2002-05-14

打赏
举报

好像不用临时表吧？我在oracle上实现了正确的结果：
select x.id, c.name, nvl(x.no,0) - nvl(y.no,0) result
from (select id, sum(no) no from a group by id) x,
(select id, max(no) no from b group by id) y, c
where c.id = x.id (+)
and c.id = y.id (+)
and nvl(x.no,0) - nvl(y.no,0) > 10;

ID NAME RESULT
---------- -------------------- ----------
1 A1 28
2 A2 33
4 A4 20
6 A6 39
7 A7 12
9 A9 11