多表联合查询。。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)
