loading..
   English
01:28

56 . 5

3.12.5
  1. SELECT t1.class, COUNT(*) AS cnt
  2. FROM (SELECT a.class, b.name
  3. FROM Classes a LEFT JOIN
  4. -- Ships :
  5. Ships b ON a.class = b.class AND
  6. a.class <> b.name
  7. ) AS t1 JOIN
  8. -- ,
  9. Outcomes t2 ON t1.class = t2.ship OR
  10. t1.name = t2.ship
  11. WHERE result = 'sunk'
  12. GROUP BY t1.class
  13. -- , .
  14. -- , .
  15. UNION
  16. SELECT class, '0'
  17. FROM Classes
  18. WHERE class NOT IN (SELECT DISTINCT t1.class
  19. FROM (SELECT a.class, b.name
  20. FROM Classes a LEFT JOIN
  21. Ships b ON a.class = b.class AND
  22. a.class <> b.name
  23. ) AS t1 JOIN
  24. Outcomes t2 ON t1.class = t2.ship OR
  25. t1.name = t2.ship
  26. WHERE result = 'sunk'
  27. );


3.12.6
  1. SELECT d.class class, (SELECT COUNT(f.result)
  2. FROM (SELECT c.result
  3. FROM Ships b LEFT OUTER JOIN
  4. Outcomes c ON (b.name = c.ship)
  5. WHERE c.result = 'sunk' AND
  6. d.class = b.class
  7. UNION ALL
  8. SELECT c.result
  9. FROM Outcomes c
  10. WHERE c.result = 'sunk' AND
  11. d.class = c.ship
  12. ) f
  13. ) Sunks
  14. FROM Classes d;


3.12.5 3.12.6 . Ships ( ):

name class
ship1_class_1 class_1
ship2_class_1 class_1

Outcomes:

ship result
ship1_class_1 sunk
class_1 sunk

3.12.5

  1. ON t1.class = t2.ship OR
  2. t1.name = t2.ship
ship1_class_1 Ships, Outcomes , . 3 , 2.

3.12.6 , ( ) ship1_class_1, class_1. , .

Ships

name                class
ship1_class_2    class_2
class_2    class_2

Outcomes:

ship                  result
ship1_class_2    sunk
class_2             sunk

class_2, . UNION ALL, , 3 2. UNION ALL UNION ,   , 3.12.4, 1.

, 3.12.5 3, , .

SQL-EX.RU

Bookmark and Share
: 1 2 3 4 5

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