在不使用笛卡尔连接的情况下组合许多 sql 查询的结果

debugcn 发表于 Dev

瓦巴夫·乔汉

如何组合多个选择查询的结果，以便我可以在一个组合视图中获得有关所有战斗的信息。有关完整的架构，请查看此处，因为 stackoverflow 限制了我可以编写多少代码来描述我的问题。

|battle.id  |Attackers       | Defender           | Winner         |
--------------------------------------------------------------------
|    1      |'rishabhchauhan'|'vaibhavchauhan'    |'vaibhavchauhan'|
|    2      |'abhishekgaur'  |'vaibhavchauhan'    |'vaibhavchauhan'|
|    3      |'AnkitSharma'   |'vaibhavchauhan'    |'vaibhavchauhan'|
....

查询以查找攻击者名称：

select CONCAT(player.firstname, player.lastname) as Attacker, Battles.battle_id 
from ((player inner 
join Attacker_Battles on Attacker_Battles.player_id = player.player_id ) 
inner join Battles on Battles.battle_id = Attacker_Battles.battle_id);

查询以查找防御者姓名：

select CONCAT(player.firstname, player.lastname) as Defenders, Battles.battle_id 
from ((player inner 
join Defender_Battles on Defender_Battles.player_id = player.player_id ) 
inner join Battles on Battles.battle_id = Defender_Battles.battle_id);

查询中奖者姓名：

select CONCAT(player.firstname, player.lastname) as Winners, Battles.battle_id 
from ((player inner 
join Winner_battles on Winner_battles.player_id = player.player_id ) 
inner join Battles on Battles.battle_id = Winner_battles.battle_id);

缺口

要将所有的查询合并成一个，你只需要JOIN在Battles表中每一个的Attacker_Battles等表，然后每个那些到的player表，以获得适当的名称。这样的事情应该工作：

SELECT CONCAT(pa.firstname, pa.lastname) AS Attacker,
       CONCAT(pd.firstname, pd.lastname) AS Defender,
       CONCAT(pw.firstname, pw.lastname) AS Winner,
       b.battle_id As Battle
FROM Battles b
JOIN Attacker_Battles ab ON ab.battle_id = b.battle_id
JOIN player pa ON pa.player_id = ab.player_id
JOIN Defender_Battles db ON db.battle_id = b.battle_id
JOIN player pd ON pd.player_id = db.player_id
JOIN Winner_Battles wb ON wb.battle_id = b.battle_id
JOIN player pw ON pw.player_id = wb.player_id

输出（用于您的 pastebin 数据）

Attacker        Defender        Winner          Battle
rishabhchauhan  vaibhavchauhan  vaibhavchauhan  1
abhishekgaur    pawanyadav      abhishekgaur    2
abhishekgaur    pawanyadav      abhishekgaur    3
AnkitSharma     JayantVerma     AnkitSharma     4
vaibhavchauhan  JayantVerma     JayantVerma     5
rishabhchauhan  abhishekgaur    rishabhchauhan  6
vaibhavchauhan  AnkitSharma     vaibhavchauhan  8

SQLFiddle 上的演示

查询也可以写成（也许更直观一点）如下。如果给定的Battle可能没有Attacker,Defender和Winner，则JOIN此查询中的顶级s 可以替换为LEFT JOIN：

SELECT CONCAT(pa.firstname, pa.lastname) AS Attacker,
       CONCAT(pd.firstname, pd.lastname) AS Defender,
       CONCAT(pw.firstname, pw.lastname) AS Winner,
       b.battle_id As Battle
FROM Battles b
JOIN (Attacker_Battles ab
      JOIN player pa ON pa.player_id = ab.player_id) ON ab.battle_id = b.battle_id
JOIN (Defender_Battles db
      JOIN player pd ON pd.player_id = db.player_id) ON db.battle_id = b.battle_id
JOIN (Winner_Battles wb
      JOIN player pw ON pw.player_id = wb.player_id) ON wb.battle_id = b.battle_id

SQLFiddle 上的演示

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。