多表联合查询。。union的问题
我现在有很多个游戏表如
A表B表C表
三表结构相同。每表代表一款游戏。
表结构
uid   sc
用户名   分数
我现在想查找用户在每款游戏的得分
select   sc,game_name=A   from   A   where   uid   =   10000
union   all   select   sc,game_name=B   from   B   where   uid   =   10000
union   all   select   sc,game_name=C   from   C   wehre   uid   =   1000
可是如果当其中有一个表无记录时如B表没有uid=10000的记录。我无法得到B的成绩
我们要求。无记录就记0

------解决方案--------------------
--try

select tmpA.*, sc=isnull(tmpB.sc, 0) from
(
select game_name= 'A '
union all select 'B '
union all select 'C '
)tmpA
left join
(
select sc=sum(sc), game_name= 'A ' from A
where uid = 10000
group by uid
union all
select sc=sum(sc),game_name= 'B ' from B
where uid = 10000
group by uid
union all
select sc=sum(sc), game_name= 'C ' from C
where uid = 10000
group by uid
)tmpB on tmpA.game_name=tmpB.game_name
------解决方案--------------------
这里假设有一个用户信息表UserInfo(UID INT,User_Name VARCHAR(20)),视图改为:

select sc,game_name=A from A
UNION ALL--将A游戏中没有出现的用户分数设置为0
SELECT sc = 0,game_name = A
FROM UserInfo
WHERE NOT EXISTS(SELECT * FROM A WHERE uid = UserInfo.uid)
UNION ALL select sc,game_name=B from B
UNION ALL
SELECT sc = 0,game_name = B
FROM UserInfo
WHERE NOT EXISTS(SELECT * FROM B WHERE uid = UserInfo.uid)
UNION ALL select sc,game_name=C from C
UNION ALL
SELECT sc = 0,game_name = C
FROM UserInfo
WHERE NOT EXISTS(SELECT * FROM C WHERE uid = UserInfo.uid)