Table查詢不完全相等問題 - yide

随笔-14 评论-7 文章-1 trackbacks-0

如表一 :    key1 , qty1
                   001     2
                   002     5
    表二 :    key2 , qty2
                   002     7
                   003     3
將表一和表二中key1和key2相等時 qty1和qty2加 , 不同時全都顯示出來, 結果如下

                  001      2
                  002     12
                  003     3

posted on 2006-01-12 11:46 yide 阅读(206) 评论(3) 编辑收藏网摘

评论:

#1楼 2006-01-12 15:15 | feiyun0112

select key1 , qty1 from T1 where key1 not in (select key2 from T2)
union
select key2 as key 1 , qty2 as qty1 from T2 where key2 not in (select key1 from T1)
union
select key1 , qty1+qty2 as qty1 from T2,T1 where T2.key2=T1key1
order by key1
回复引用查看

#2楼 2006-01-13 08:41 | ----[未注册用户]

謝謝 , 這個方法我試過 ,不知道有沒有其他方法 .
回复引用

#3楼 2006-01-19 11:12 | Yin Peng[未注册用户]

SELECT key3, SUM(qty3) AS qty3
FROM (
SELECT key1 AS key3, qty1 AS qty3
FROM table1
UNION
SELECT key2 AS key3, qty2 AS qty3
FROM table2
) A
GROUP BY key3
回复引用