Задача по SQL

Question

Задача по SQL

Maikl King Профи (645), на голосовании 3 недели назад

Помогите, пожалуйста, составить запрос, который помогает выбрать количество книг в разрезе «Название книги-Город» используя в первом варианте соединение JOIN вместо подзапросов, а во втором- подзапрос в селекте.

Answer 1

 SELECT  
    book.title AS book_title, 
    shop.city AS city, 
    COUNT(sales.book_id) AS book_count 
FROM  
    sales 
JOIN  
    book ON sales.book_id = book.id 
JOIN  
    shop ON sales.shop_id = shop.id 
GROUP BY  
    book.title, shop.city 
ORDER BY  
    book.title, shop.city;

 SELECT  
    b.title AS book_title, 
    s.city AS city, 
    (SELECT COUNT(*)  
     FROM sales AS sa  
     WHERE sa.book_id = b.id AND sa.shop_id = s.id) AS book_count 
FROM  
    book AS b 
CROSS JOIN  
    shop AS s 
WHERE  
    (SELECT COUNT(*)  
     FROM sales  
     WHERE sales.book_id = b.id AND sales.shop_id = s.id) > 0 
ORDER BY  
    b.title, s.city;