WITH
T1 AS(
	SELECT AVG(POINT) AS AVG_POINT FROM TEST_RESULTS WHERE TEST_ID = '100'
),
T2 AS( -- 分散
	SELECT
		SQRT(
			AVG(
				POW(POINT - (SELECT AVG_POINT FROM T1), 2)
			)
		) AS HENSA
	FROM
		TEST_RESULTS
	WHERE
		TEST_ID = '100'
)
SELECT
	USER_ID AS USER
	, POINT AS PT
	--, ((POINT - (SELECT AVG_POINT FROM T1) * 10) / (SELECT HENSA FROM T2)) + 50 AS DEV_VAL
	, CASE (SELECT HENSA FROM T2) WHEN 0 THEN 50 ELSE ROUND((POINT - (SELECT AVG_POINT FROM T1)) * 10 / (SELECT HENSA FROM T2) + 50, 2) END AS DEV_VAL
FROM
	TEST_RESULTS
WHERE 
	TEST_ID = '100'
ORDER BY
	DEV_VAL DESC
	, USER_ID