SELECT id_film, sq1.counter
FROM(
SELECT id_film, COUNT(*) as counter
FROM film_vorstellung
GROUP BY id_film
) as sq1 WHERE sq1.id_film = id_film
查詢回傳：
| id_film | 柜臺 |
|---|---|
| 3 | 1 |
| 5 | 1 |
| 4 | 1 |
| 6 | 2 |
| 2 | 3 |
| 1 | 1 |
到目前為止，一切都很好。
現在我想獲取每個 id_film 哪個計數器高于平均計數器值..我現在正在嘗試這樣做幾個小時。最常見的語法錯誤是我無法使用我嘗試使用的平均方法...
uj5u.com熱心網友回復：
您可以使用 CTE 和標量查詢運算式來完成此操作。幾乎用簡單的語言來說，首先計算 per 的計數器值id_film，然后“獲取計數器高于平均計數器值的每個 id_film”。
with cntrs as -- your subquery
(
SELECT id_film, COUNT(*) as cntr
FROM film_vorstellung
GROUP BY id_film
)
select id_film, cntr
from cntrs
where cntr > (select avg(cntr) from cntrs);
uj5u.com熱心網友回復：
如果您不想使用 with ，替代解決方案可能是這樣的
select y.id_film,y.cnt
from film_vorstellung x
JOIN
( select id_film,COUNT(id_film) as cnt ,
CAST( (SELECT DISTINCT SUM(Count(g.id_film)) OVER () as SumCount FROM film_vorstellung g GROUP BY g.id_film) as DECIMAL) /
CAST((SELECT count(DISTINCT id_film) FROM film_vorstellung) AS DECIMAL ) avgs
from film_vorstellung
group by id_film
) y
on x.id_film=y.id_film
where y.cnt >y.avgs
group by y.id_film,y.cnt
或者
SELECT id_film, sq1.counter
FROM(
SELECT id_film, COUNT(*) as counter
FROM film_vorstellung
GROUP BY id_film
) as sq1 WHERE sq1.id_film = id_film
group by id_film,sq1.counter
having AVG (sq1.counter)> (SELECT AVG(X.counter) FROM ( SELECT id_film, COUNT(*) as counter FROM film_vorstellung group by id_film )X )
go
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/348285.html
標籤：sql PostgreSQL