Сражения, корабли sql запрос

Question

Вот схема о линейных кораблях и битвах, в которых они сражались:

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

Типичный кортеж Ships будет:

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

это означает, что линкор Нью-Джерси был запущен в 1943 году; он принадлежал США, имел 9 орудий размером 16 дюймов (диаметр ствола или внутренний диаметр ствола) и весил (смещен в морском выражении) 46 000 тонн. Типичный кортеж для Battles:

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

То есть HMS Hood был потоплен в битве за Северную Атлантику. Другие возможные результаты "хорошо" и "повреждены"

Вопрос: Перечислите все пары стран, которые сражались друг с другом в битвах. Перечислите каждую пару только один раз, и перечислите их в первую очередь в алфавитном порядке.

Ответ: я написал это:

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