SQL语句两表关联的,如何得出如下结果呢?好象有点麻烦!
TableA
No cuno ctype qty
A 001 H 2
A 001 M 1
A 001 L 3
A 001 M 2
TableB
No cuno ctype qty
A 002 H 1
A 002 M 3
A 001 H 2
A 001 M 1
要求结果为:
No cuno ctype qty
A 001 L 3
A 001 M 2
A 002 H 1
A 002 M 3
请问如何得到呢? 意思是TableA与TableB两表相比较, 得出两表中不同的记录,放一起! 请指教,谢谢
[解决办法]
优化一下：
VFP7.0以上：
SELECT NO,省份,地区,qty AS qtyA,000000 AS qtyB FROM tableA union all ;
SELECT NO,省份,地区,000000 AS qtyA,qty AS qtyB FROM tableB ;
INTO CURSOR T1
SYS(3099,70)
SELECT * INTO TABLE 目标表 FROM T1 GROUP BY NO,省份,地区 HAVING COUNT(*)=1
SYS(3099,90)
VFP7.0以下
SELECT NO,省份,地区,qty AS qtyA,000000 AS qtyB FROM tableA union all ;
SELECT NO,省份,地区,000000 AS qtyA,qty AS qtyB FROM tableB ;
INTO CURSOR T1
SELECT * INTO TABLE 目标表 FROM T1 GROUP BY NO,省份,地区 HAVING COUNT(*)=1
[解决办法]
SELECT A1.*,0 AS qtyA FROM A1 LEFT JOIN B1 ON A1.NO=B1.NO AND;
A1.省份=B1.省份 AND A1.地区=B1.地区 WHERE ISNULL(B1.NO);
UNION;
SELECT B1.NO,B1.省份,B1.地区 ,0 AS QtyA,B1.QTY AS QTYB FROM A1 RIGHT JOIN B1 ON A1.NO=B1.NO AND;
A1.省份=B1.省份 AND A1.地区=B1.地区 WHERE ISNULL(A1.NO);
