时间戳

首页 > 解决方案 > sqlite 将来自不同表的 2 个查询组合成一个

python-3.x - sqlite 将来自不同表的 2 个查询组合成一个

问题描述

我最近又开始使用sql了，我最后一次使用它是在microsoft access 2000中，所以如果我落后了一点，请多多包涵。

我的 discord 服务器上有 2 种毫无意义的虚拟货币供我的玩家玩毫无意义的游戏。这两种货币的交易目前都存储在单独的表中。

我希望总结每个玩家的所有交易，为他们提供每种货币的单一当前金额。单独我可以这样做：

SELECT
 tblPlayers.PlayerID AS PlayerID,
 tblPlayers.Name AS Name,
 SUM(tblGorillaTears.Amount) 
FROM 
 tblPlayers 
INNER JOIN 
 tblGorillaTears
ON 
 tblPlayers.PlayerID = tblGorillaTears.PlayerID 
GROUP BY 
 tblPlayers.PlayerID;

和

SELECT
 tblPlayers.PlayerID AS PlayerID,
 tblPlayers.Name AS Name,
 SUM(tblKebabs.Amount) 
FROM 
 tblPlayers 
INNER JOIN 
 tblKebabs 
ON 
 tblPlayers.PlayerID = tblKebabs.PlayerID 
GROUP BY 
 tblPlayers.PlayerID;

我需要的是一个表格，在一行上输出用户名、id 和每种货币的总数，但是当我这样做时：

SELECT
 tblPlayers.PlayerID AS PlayerID,
 tblPlayers.Name AS Name,
 SUM(tblGorillaTears.Amount) AS GT,
 0 as Kebabs
FROM 
 tblPlayers 
INNER JOIN 
 tblGorillaTears
ON 
 tblPlayers.PlayerID = tblGorillaTears.PlayerID 
GROUP BY 
 tblPlayers.PlayerID
UNION 
SELECT
 tblPlayers.PlayerID AS PlayerID,
 tblPlayers.Name AS Name,
 0 as GP,
 SUM(tblKebabs.Amount) 
FROM 
 tblPlayers 
INNER JOIN 
 tblKebabs 
ON 
 tblPlayers.PlayerID = tblKebabs.PlayerID 
GROUP BY 
 tblPlayers.PlayerID;

每种货币的每个玩家的结果都以一行结束。我怎样才能让两种货币出现在同一行？

以前在 MSAccess 中，我能够创建两个查询，然后对这两个查询进行查询，就好像它们是一个表一样，但我不知道在这种情况下如何做到这一点。谢谢 <3

标签： python-3.xsqlite

解决方案

UNION肯定会添加新行，您可以尝试以下查询。

SELECT TP.playerid                        AS PlayerID, 
       TP.NAME                            AS NAME, 
       (SELECT Sum(TG.amount) 
        FROM   tblgorillatears TG 
        WHERE  TG.playerid = TP.playerid) AS GT, 
       (SELECT Sum(TG.amount) 
        FROM   tblkebabs TG 
        WHERE  TG.playerid = TP.playerid) AS Kebabs 
FROM   tblplayers TP

推荐阅读