With AVG_P AS (			--点数平均
	SELECT AVG(POINT) AVGP
	FROM TEST_RESULTS
	WHERE TEST_ID = '100'
),
DIFF AS (
	SELECT
	 USER_ID
	,POINT-AVGP DIFP
	,(POINT-AVGP) * (POINT-AVGP) SQNM			--差の二乗
	FROM TEST_RESULTS,AVG_P
	WHERE TEST_ID = '100'
),
HENSA AS (
	SELECT SQRT(AVG(SQNM)) HENSA		--二乗の平均と標準偏差
	FROM DIFF
)
SELECT
 USER_ID
,CASE WHEN HENSA <> 0 
 THEN ROUND((((POINT-AVGP) * 10) / HENSA),1)+ 50
 ELSE 50
 END DEV_VAL 
FROM TEST_RESULTS,AVG_P,HENSA
WHERE TEST_ID = '100'
;