我想问一下,这个SQL语句应该怎样写?
例如,两个表:
cityID userID valueA
------ ------ ------
1 1 100
1 2 200
2 1 300
2 2 400
cityID userID valueB
------ ------ ------
1 1 50
1 2 55
2 2 60
我想得出这样的结果:
cityID userID valueA valueB
------ ------ ------ ------
1 1 100 50
1 2 200 55
2 1 0 0
2 2 400 65
直接用表连接,就显示不了cityID=2和userID=1那行了,应该怎样写呢?
例如,两个表:
cityID userID valueA
------ ------ ------
1 1 100
1 2 200
2 1 300
2 2 400
cityID userID valueB
------ ------ ------
1 1 50
1 2 55
2 2 60
我想得出这样的结果:
cityID userID valueA valueB
------ ------ ------ ------
1 1 100 50
1 2 200 55
2 1 0 0
2 2 400 65
直接用表连接,就显示不了cityID=2和userID=1那行了,应该怎样写呢?
from A a, B b
where a.cityID = b.cityID and a.userID = b.cityID
union
select a.cityID, a.userID, 0, 0
from A a, B b
where a.cityID <> b.cityID or a.userID <> b.userID
union all
select cityID,userID,valueA,'0'
from table1
where cityID,userID not in (select cityID,userID from table2)
希望给你灵感冲裤衩中 给点分是点 谢谢
create table t2(cityID number(10),userID number(10),valueB number(10));
insert into t1 values(1,1,100);
insert into t1 values(1,2,200);
insert into t1 values(2,1,300);
insert into t1 values(2,2,400);
insert into t2 values(1,1,50);
insert into t2 values(1,2,55);
insert into t2 values(2,2,60);cityID userID valueA valueB
------ ------ ------ ------
1 1 100 50
1 2 200 55
2 1 0 0
2 2 400 65select cityID,userID,decode(valueB,NULL,0,valueA) valueA,NVL(valueB,0) valueB from
(select t1.cityID,t1.userID,t1.valueA valueA,t2.valueB
from t1,t2
where
t1.cityID=t2.cityID(+) and
t1.userID=t2.userID(+));