WITH SUB AS ( 
    -- １．点数の平均値を算出
    SELECT
        AVG(POINT) AS AVERAGE 
    FROM
        TEST_RESULTS 
    WHERE
        TEST_ID = '100'
) 
, SUB2 AS ( 
    SELECT
        -- ４．標準偏差（分散の平方根）を求める
        SQRT( 
            -- ３．二乗の平均（分散）を求める
            AVG( 
                -- ２．個々の点数と平均値の差の二乗を求める
                (T.POINT - SUB.AVERAGE) * (T.POINT - SUB.AVERAGE)
            )
        ) AS STANDARD_DEVIATION 
    FROM
        TEST_RESULTS AS T 
        -- 求めた平均値を交差結合し計算に使用する
        CROSS JOIN SUB 
    WHERE
        TEST_ID = '100'
) 
SELECT
    USER_ID AS USER
    , POINT AS PT
    , CASE 
        WHEN STANDARD_DEVIATION <> 0 
            THEN ROUND( 
            -- ５．個々の点数と平均値の差に10を掛ける
            -- ６．上記の値を標準偏差で割る
            (((POINT - AVERAGE) * 10) / STANDARD_DEVIATION)
            , 1
        -- ７．標準偏差で割った値に50を足す
        ) + 50 
        -- 標準偏差が0の場合は一律で偏差値は50となる
        ELSE 50 
        END AS DEV_VAL 
FROM
    TEST_RESULTS 
    CROSS JOIN SUB 
    CROSS JOIN SUB2 
WHERE
    TEST_ID = '100' 
ORDER BY
    DEV_VAL DESC
    , USER_ID ASC;