战斗，船只sql查询

Question

下面是一个关于战列舰及其战斗的模式：

 Ships(name, yearLaunched, country, numGuns, gunSize, displacement)
 Battles(ship, battleName, result)

典型的船舶元组是：

 ('New Jersey', 1943, 'USA', 9, 16, 46000)

这意味着新泽西号战舰是1943年发射的；它属于美国，携带9支16英寸口径(内径或内径的枪管)枪支，重量46,000吨。一个典型的战斗元组是：

 ('Hood', 'North Atlantic', 'sunk')

也就是说，HM.S.胡德号在北大西洋战役中沉没了。其他可能的结果是“ok”和“损坏”。

问题:列出所有在战斗中互相战斗的国家。每对只列出一次，然后按照字母顺序列出第一位的国家。

答:我写了这个：

SELECT 
    a.country, b.country 
FROM 
    ships a, ships b, battles b1, battles b2 
WHERE 
    name = ship 
    and b1.battleName = b2.battleName 
    and a.country > b.country

但上面写着不明确的列名。我该怎么解决呢？提前感谢

Answer 1

name = ship才是问题所在。name可能来自a或b，ship来自b1或b2

你可以这样做：

select distinct s1.country, s2.country
from ships s1
inner join Battles b1 on b.ship = s1.name
inner join Battles b2 on b2.ship <> s1.name and b2.battleName = b1.battleName
inner join ships s2 on s2.name = b2.ship and s2.country < s1.country

Answer 2

你能尝试一个嵌套的查询，得到一个赢家和输家的表，然后加入他们的战斗名称吗？

SELECT
WINNER.country as Country1
,LOSER.country as Country2

FROM
(
  SELECT DISTINCT country, battleName
  FROM Battles 
  INNER JOIN Ships ON Battles.ship = Ships.name
  WHERE Battles.Result = 1
) AS WINNER

INNER JOIN 
(
  SELECT DISTINCT country, battleName
  FROM Battles 
  INNER JOIN Ships ON Battles.ship = Ships.name
  WHERE Battles.Result = 0
) AS LOSER

ON WINNER.battlename = LOSER.battlename
ORDER BY WINNER.country

Answer 3

试试看，我认为它很简洁：

SELECT DISTINCT s1.country, s2.country
FROM ships s1, ships s2, battles b1, battles b2 -- i just list all tables here but you can use the left join etc.
WHERE s1.name=b1.ship AND s2.name=b2.ship AND b1.battleName=b2.battleName -- for the tables to join each other
AND s1.country < s2.country --key condition
ORDER BY s1.country ASC