假設我在圖片中有下面的例子，在表格下方有預期的結果
到目前為止，我能夠獲得租金
SELECT inventory.film_id,
SUM((SELECT COUNT(*) FROM rental WHERE rental.inventory_id = inventory.inventory_id)) AS rentals,
(SELECT title FROM film WHERE film.film_id = inventory.film_id) AS title
FROM inventory
GROUP BY inventory.film_id
ORDER BY inventory.film_id
結果
film_id rentals title
1 23 Academy Dinosaur
2 7 Ace Goldfinger
. .. ................
我似乎無法弄清楚如何讓我們說，category.name 從鏈接 film_id 到 film_category 然后到類別。我曾嘗試添加此代碼
INNER JOIN film_category ON inventory.film_id = film_category.film_id
但它只是回傳相同的結果，它沒有加入它。無論如何，這不會像我需要的那樣抓取 category.name 。
任何幫助理解這個例子背后的邏輯都會很棒。謝謝
uj5u.com熱心網友回復：
您可以使用 JOIN 撰寫簡單的查詢
SELECT inventory.film_id, film.title, COUNT(*) AS rentals
FROM inventory
JOIN rental ON rental.inventory_id = inventory.inventory_id
JOIN film ON film.film_id = inventory.film_id
GROUP BY inventory.film_id, film.title
ORDER BY inventory.film_id;
PostgreSQL 實體
如果您需要獲取電影類別，請使用下一個版本：
SELECT
inventory.film_id,
film.title,
COUNT(DISTINCT rental_id) AS rentals,
ARRAY_AGG(DISTINCT category.title)
FROM inventory
JOIN rental ON rental.inventory_id = inventory.inventory_id
JOIN film ON film.film_id = inventory.film_id
JOIN film_category ON film_category.film_id = inventory.film_id
JOIN category ON film_category.category_id = category.category_id
GROUP BY inventory.film_id, film.title
ORDER BY inventory.film_id;
在這里擺弄
uj5u.com熱心網友回復：
這里要小心。一部電影可以有很多租借并且屬于很多類別。所以你要加入出租數量和類別串列。這意味著，您應該首先聚合您的資料，然后加入：
select f.film_id, f.title, c.categories, r.number_of_rentals
from film f
left join
(
select fc.film_id, string_agg(c.name, ', ' order by c.name) as categories
from film_category fc
join category c on c.category_id = fc.category_id
group by fc.film_id
) c on c.film_id = f.film_id
left join
(
select i.film_id, count(*) as number_of_rentals
from rental r
join inventory in on i.inventory_id = r.inventory_id
group by i.film_id
) r on r.film_id = f.film_id
order by f.film_id;
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/401780.html
標籤：sql PostgreSQL的 加入