我有以下两个SQL选择:
select
u.ID, name, "Desc", sum(pp.amount) as paid
from
[dbo].[Users] u, [dbo].[UserTypes] ut, [dbo].[PlayerPayments] pp
where
u.UserTypeID = ut.ID
and u.ID = pp.UserID
group by
u.ID, Name, "Desc";
select
u.ID,name, "Desc", sum(ga.GamePrice) as owed, count(ga.ID) as gamesplayed
from
[dbo].[Users] u,[dbo].[UserTypes] ut, [dbo].[Games] ga, [dbo].[GamePlayers] gp
where
u.UserTypeID = ut.ID
and u.ID = gp.UserID
and gp.GameID = ga.ID
group by
u.ID, Name, "Desc";
它们返回以下结果,如下所示:
如何通过在第一个结果集上添加两列(所欠和游戏性)来将结果合并在一起?
这是一个通用的解决方案:
SELECT T1.*, T2.owed, T2.gamesplayed FROM
(
select u.ID,name, "Desc", sum(pp.amount) as paid
from [dbo].[Users] u,[dbo].[UserTypes] ut, [dbo].[PlayerPayments] pp
where u.UserTypeID = ut.ID and u.ID = pp.UserID
group by u.ID,Name,"Desc"
) T1
JOIN
(
select u.ID,name, "Desc", sum(ga.GamePrice) as owed, count(ga.ID) as gamesplayed
from [dbo].[Users] u,[dbo].[UserTypes] ut, [dbo].[Games] ga, [dbo].[GamePlayers] gp
where u.UserTypeID = ut.ID and u.ID = gp.UserID and gp.GameID = ga.ID
group by u.ID,Name,"Desc"
) T2
ON T1.ID=T2.ID
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句