WITH avg_all AS(
SELECT
AVG(POINT) AS avg_all
FROM
TEST_RESULTS
WHERE TEST_ID = '100'),
--分散取得
variance AS
(SELECT 
USER_ID,
SQRT(AVG(POWER(POINT - avg_all,2))) AS variance
FROM
TEST_RESULTS, avg_all
WHERE TEST_ID = '100')
SELECT
t1.USER_ID AS USER,
t1.POINT AS PT,
CASE WHEN
variance <> 0 THEN
ROUND(((POINT - avg_all)*10/variance)+50, 1)
ELSE 50
END AS DEV_VAL
FROM
TEST_RESULTS t1,
avg_all t2,
variance t3
WHERE TEST_ID = '100'
ORDER BY DEV_VAL DESC,
USER ASC;