0

## 請問有誰可以幫忙嗎? (AVG (COUNT (...))

### 2 個回答

1
rogeryao
iT邦大師 1 級 ‧ 2020-11-05 20:42:46

``````SELECT
TEMP.A AS "Artist Code",
TEMP.B AS "Artwork No.",
TEMP.C AS "Artwork Title",
TEMP.D AS "Number of Movements"
FROM(
SELECT
ARTIST.artist_code          AS "A",
ARTWORK.artwork_no          AS "B",
ARTWORK.artwork_title       AS "C",
COUNT(AW_STATUS.aws_action) AS "D",
AVG(COUNT(AW_STATUS.aws_action)) OVER () AS "E"
FROM ARTIST
JOIN ARTWORK
ON ARTIST.artist_code = ARTWORK.artist_code
JOIN AW_STATUS
ON AW_STATUS.artist_code = ARTWORK.artist_code
AND AW_STATUS.artwork_no = ARTWORK.artwork_no
GROUP BY
ARTIST.artist_code,
ARTWORK.artwork_no,
ARTWORK.artwork_title
) TEMP
WHERE TEMP.D < TEMP.E
ORDER BY
TEMP.D ASC,
TEMP.A ASC,
TEMP.B ASC;
``````

windaura iT邦新手 5 級 ‧ 2020-11-05 20:57:23 檢舉

windaura iT邦新手 5 級 ‧ 2020-11-05 20:58:22 檢舉

rogeryao iT邦大師 1 級 ‧ 2020-11-05 21:01:30 檢舉

) AS TEMP

) TEMP

) "TEMP" 試試

windaura iT邦新手 5 級 ‧ 2020-11-05 22:49:10 檢舉

) TEMP (倒數第6), 要replace which one?

rogeryao iT邦大師 1 級 ‧ 2020-11-05 23:05:00 檢舉

0

iT邦大神 1 級 ‧ 2020-11-05 21:02:42

COUNT 是 COUNT GROUP BY 之後的結果

windaura iT邦新手 5 級 ‧ 2020-11-05 21:49:15 檢舉

AW_STATUS.aws_action 是表示動作,此題是要求我計算"動作的次數小於平均動作的次數"

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

windaura iT邦新手 5 級 ‧ 2020-11-06 14:46:07 檢舉