(mySQL Aggregate function, JOINS, and GROUP BY) sakila 數據庫項目

目前的大流行使我很難聯繫我的老師尋求幫助，所以我希望我能很好地描述我的問題，以便嘗試在這裡獲得一些幫助。

使用的數據庫是 sakila 範例數據庫

（我試圖創建一個小提琴，但表太大了）

我的數據將按每個商店（只有 2 個音像商店）分組，我會得到

1. 每個店舖的總銷售額（老師提示：store table 有庫存。庫存可以租用，rental table 有付款。
2. 每家商店的顧客數量
3. 每家商店的庫存數量
4. 每家商店的電影數量
5. 尚未歸還的租借電影數量

這是我的錯誤輸出：

--------------------------------------------------------------------------------------------------------------------------------
| Jordan_Rasmussen | store_id | total_sales | num_customers | count_inventory | count_titles | inventory_cost | num_rentals_out |
--------------------------------------------------------------------------------------------------------------------------------
| (date&time)      | 1        | 68359569.18 | 326           | 2270            | 759          | 952923.30      | 29992 |
----------------------------------------------------------------------------------------------------------------------------
| (date&time)      | 2        | 56966647.92 | 273           | 2311            | 762          | 970134.69      | 24843 |              |
--------------------------------------------------------------------------------------------------------------------------------

這是正確的輸出：

--------------------------------------------------------------------------------------------------------------------------------
| First_and_last N | store_id | total_sales | num_customers | count_inventory | count_titles | inventory_cost | num_rentals_out |
--------------------------------------------------------------------------------------------------------------------------------
| (date&time)      | 1        | 209691.93   | 326           | 2270            | 759          | 46205.30       | 92              |
--------------------------------------------------------------------------------------------------------------------------------
| (date&time)      | 2        | 208669.04   | 273           | 2311            | 762          | 46415.89       | 91              |
--------------------------------------------------------------------------------------------------------------------------------

這是我嘗試獲取上述輸出的程式碼：

SELECT NOW() AS 'Jordan_Rasmussen',
s.store_id,

-- Get the total sales
SUM(p.amount) AS total_sales,

-- Get the number of customers
COUNT(DISTINCT c.customer_id) AS num_customers,

-- Get the inventory count
COUNT(DISTINCT i.inventory_id) AS inventory_count,

-- Get the number of movie titles
COUNT(DISTINCT f.title) AS num_titles,

-- Get the inventory value 
SUM(DISTINCT f.replacement_cost) * COUNT(DISTINCT i.inventory_id)   AS inventory_value,

-- Get the number of movies rented that have not yet been returned 
COUNT(r.rental_date) AS num_rentals_out 

FROM store AS s 
LEFT JOIN inventory AS i ON s.store_id = i.store_id 
LEFT JOIN customer AS c ON s.store_id = c.store_id 
INNER JOIN rental AS r ON i.inventory_id = r.inventory_id
INNER JOIN payment AS p ON r.rental_id = p.rental_id
INNER JOIN film AS f ON i.film_id = f.film_id 


GROUP BY store_id;

我自己可以得到正確的結果，但我注意到我將表連接在一起的次數越多，結果變化就越大。我一直在為此撓頭一段時間，但我只是不確定我錯過了什麼。

我應該使用子查詢嗎？還是我加入不當？

對不起這麼大的問題，但我現在一無所知。

問題是您正在加入不相關的表。如果您不加入他們，例如

SELECT NOW() AS 'Dorjan Masrussen',
       s.store_id,
       (select sum(p.amount)
          from payment p
          where p.rental_id in (
            select r.rental_id
              from rental r
              join inventory i
                on i.inventory_id = r.inventory_id
              where i.store_id = s.store_id)) total_sales,
       (select count(*)
          from customer c
          where c.store_id = s.store_id) num_customers,
       (select count(*)
          from inventory i
          where i.store_id = s.store_id) count_inventory,
       (select count(distinct film_id)
          from inventory i
          where i.store_id = s.store_id) count_titles,
       (select sum(f.replacement_cost)
          from inventory i
          join film f on f.film_id = i.film_id
          where i.store_id = s.store_id) inventory_cost,
       (select count(*)
          from rental r
          join inventory i
            on i.inventory_id = r.inventory_id
          where i.store_id = s.store_id) num_rentals_out
 from store as s

你得到：

+---------------------+----------+-------------+---------------+-----------------+--------------+----------------+-----------------+
| Dorjan Masrussen    | store_id | total_sales | num_customers | count_inventory | count_titles | inventory_cost | num_rentals_out |
+---------------------+----------+-------------+---------------+-----------------+--------------+----------------+-----------------+
| 2020-03-31 17:32:18 |        1 |    33679.79 |           326 |            2270 |          759 |       46205.30 |            7923 |
| 2020-03-31 17:32:18 |        2 |    33726.77 |           273 |            2311 |          762 |       46415.89 |            8121 |
+---------------------+----------+-------------+---------------+-----------------+--------------+----------------+-----------------+

Total sales 和 num_rentals_out 與您的正確輸出不同。我想知道誰是對的。Sakila 結構圖，如果我沒記錯的話，是魚尾紋倒置的。

使用 CTE（公用表表達式“WITH”）僅查詢一次庫存時，可以獲得相同的結果，如下所示：

WITH idata as (
 select i.store_id,
        count(*) num,
        count(distinct i.film_id) num_films,
        sum(f.replacement_cost) repl_cost,
        sum((select count(*)
               from rental r
               where r.inventory_id = i.inventory_id)) num_rentals,
        sum((select sum(p.amount)
               from payment p
               where p.rental_id in (
                 select r.rental_id
                   from rental r
                   where r.inventory_id = i.inventory_id
               ))) sum_rentals
   from inventory i
   join film f on f.film_id = i.film_id
   group by i.store_id
)
SELECT NOW() AS 'Dorjan Masrussen',
       s.store_id,
       idata.sum_rentals total_sales,
       (select count(*)
          from customer c
          where c.store_id = s.store_id) num_customers,
       idata.num count_inventory,
       idata.num_films count_titles, 
       idata.repl_cost inventory_cost,
       idata.num_rentals num_rentals_out
 from store as s
 join idata on idata.store_id = s.store_id

也可以刪除查詢 store 表，因為我們只需要 store_id，我們也可以在庫存中找到它。

引用自：https://dba.stackexchange.com/questions/263852