两个表结构一样,但是有不同的用途,利用下面的语句后会有不必要的记录出现:
SELECT a.BEARDATE, a.OUTUNIT, a.CARNO, b.NETWEIGHT, a.NETWEIGHT,
a.NETWEIGHT - b.NETWEIGHT AS wastenum
FROM ININFO a, INPRONDREC b
WHERE a.CARNO = b.CARNO AND a.BEARDATE = b.BEARDATE AND
a.OUTUNIT = b.OUTUNIT
ORDER BY a.BEARDATE, a.carno
查询结果:
2004-12-6 3 陕C-09372 10 10.54 .54
2004-12-6 3 陕C-09372 10.54 10.54 0
2004-12-6 3 陕C-09372 10 10 0
2004-12-6 3 陕C-09372 10.54 10 -.54
2004-12-6 3 陕C-09494 10.86 10.86 0
2004-12-6 3 陕C-11055 10.66 10.66 0
2004-12-6 3 陕C-11295 10.08 10.08 0
2004-12-6 3 陕C-13970 10.39 10.39 0其中:
2004-12-6 3 陕C-09372 10 10.54 .54
2004-12-6 3 陕C-09372 10.54 10 -.54
是两条多余的记录,如何去除
SELECT a.BEARDATE, a.OUTUNIT, a.CARNO, b.NETWEIGHT, a.NETWEIGHT,
a.NETWEIGHT - b.NETWEIGHT AS wastenum
FROM ININFO a, INPRONDREC b
WHERE a.CARNO = b.CARNO AND a.BEARDATE = b.BEARDATE AND
a.OUTUNIT = b.OUTUNIT
ORDER BY a.BEARDATE, a.carno
查询结果:
2004-12-6 3 陕C-09372 10 10.54 .54
2004-12-6 3 陕C-09372 10.54 10.54 0
2004-12-6 3 陕C-09372 10 10 0
2004-12-6 3 陕C-09372 10.54 10 -.54
2004-12-6 3 陕C-09494 10.86 10.86 0
2004-12-6 3 陕C-11055 10.66 10.66 0
2004-12-6 3 陕C-11295 10.08 10.08 0
2004-12-6 3 陕C-13970 10.39 10.39 0其中:
2004-12-6 3 陕C-09372 10 10.54 .54
2004-12-6 3 陕C-09372 10.54 10 -.54
是两条多余的记录,如何去除
a.OUTUNIT = b.OUTUNIT);
SELECT a.BEARDATE, a.OUTUNIT, a.CARNO, b.NETWEIGHT, a.NETWEIGHT,
a.NETWEIGHT - b.NETWEIGHT AS wastenum
FROM ININFO a, INPRONDREC b
WHERE a.CARNO = b.CARNO AND a.BEARDATE = b.BEARDATE AND
a.OUTUNIT = b.OUTUNIT AND a.NETWEIGHT = b.NETWEIGHT
ORDER BY a.BEARDATE, a.carno
引起记录重复的原因,并不是数据库中有重复记录,而是查询的时候引起的,
如果同一个a.CARNO 、a.BEARDATE 、a.OUTUNIT 在两个表中有两条记录的时候会发生笛卡尔积
但是a.NETWEIGHT = b.NETWEIGHT等式不一定成立
a.NETWEIGHT - b.NETWEIGHT AS wastenum
FROM
(select * from ININFO
union
select * from ININFO ) a,
(select * from INPRONDREC
union
select * from INPRONDREC ) b
WHERE a.CARNO = b.CARNO AND a.BEARDATE = b.BEARDATE AND
a.OUTUNIT = b.OUTUNIT
ORDER BY a.BEARDATE, a.carno
from
(select * from ININFO
union
select * from INPRONDREC ) a,
(select * from INPRONDREC
union
select * from ININFO ) b
........
如何知道多余的记录是
2004-12-6 3 陕C-09372 10 10.54 .54
2004-12-6 3 陕C-09372 10.54 10 -.54
而不是
2004-12-6 3 陕C-09372 10.54 10.54 0
2004-12-6 3 陕C-09372 10 10 0
a.OUTUNIT = b.OUTUNIT);
这个对于数据量很大的会不会很慢???
2004-12-6 3 陕C-09372 10 10.54 .54
2004-12-6 3 陕C-09372 10.54 10 -.54
这两条记录本来就不存在
------
即使两个表记录一样,也会出现这样的记录啊
ININFO表
2004-12-6 3 陕C-09372 10.54
2004-12-6 3 陕C-09372 10
INPRONDREC表
2004-12-6 3 陕C-09372 10.54
2004-12-6 3 陕C-09372 10
表连接时就是你上面那四条记录,那你如何确定哪两条记录时不要的?
我的假定是a.NETWEIGHT = b.NETWEIGHT,如果不对,那是什么?