loading..
   English
13:55

57 ( )

, (, , ), .

  1. SELECT f.class, SUM(count_out) AS cnt
  2. FROM (SELECT t.class, SUM(cnt) AS count_out
  3. FROM (SELECT c.class, ship, COUNT(*) CNT
  4. FROM Classes c LEFT JOIN
  5. Ships s ON c.class = s.class INNER JOIN
  6. Outcomes o ON o.ship = s.name AND
  7. result = 'sunk'
  8. GROUP BY c.class,ship
  9. ) AS t
  10. GROUP BY t.class
  11. UNION ALL
  12. SELECT t.class, SUM(cnt) AS count_out
  13. FROM (SELECT c.class, ship, COUNT(*) cnt
  14. FROM Classes c INNER JOIN
  15. Outcomes o ON c.class = o.ship AND
  16. o.result = 'sunk' AND
  17. NOT EXISTS (SELECT *
  18. FROM Ships
  19. WHERE o.ship = name
  20. )
  21. GROUP BY c.class,ship
  22. ) AS t
  23. GROUP BY t.class
  24. ) AS f
  25. GROUP BY f.class
  26. HAVING 2 < (SELECT SUM(cnt)
  27. FROM (SELECT COUNT(c.class) AS cnt
  28. FROM Classes c, Ships s
  29. WHERE c.class = s.class AND
  30. c.class = f.class
  31. UNION
  32. SELECT COUNT(c.class) AS cnt
  33. FROM Classes c, Outcomes o
  34. WHERE c.class = o.ship AND
  35. c.class = f.class AND
  36. NOT EXISTS (SELECT *
  37. FROM Ships
  38. WHERE o.ship = name
  39. )
  40. ) AS k
  41. )

HAVING . Ships () Outcomes , ( Ships).

, UNION, , , . , , . , , ( COUNT ). . , , , , Ships.

, . ( ) .

57

SQL-EX.RU


Bookmark and Share

©SQL-EX,2008 [] [] [ ] [] [Team]
.
Rambler's Top100