WITH heikin AS (-- 平均値
    SELECT
    AVG(POINT) AS AVG
FROM
    TEST_RESULTS
WHERE
    TEST_ID = '100'
    )
,
hensa AS (--偏差値
    SELECT
        SQRT(
            AVG(
                (TR.POINT - heikin.AVG) * (TR.POINT - heikin.AVG)
            )
        ) AS SUBQhensa
    FROM
        TEST_RESULTS AS TR
    CROSS JOIN
        heikin
    WHERE
        TEST_ID = '100'
    )

SELECT
    USER_ID AS USER
    ,POINT AS PT
    ,CASE WHEN SUBQhensa <> 0
          THEN ROUND((((POINT - AVG) * 10) / SUBQhensa), 1) + 50
          ELSE 50
          END AS DEV_VAL
FROM
    TEST_RESULTS
CROSS JOIN
    heikin
CROSS JOIN
    hensa
WHERE
    TEST_ID = '100'
ORDER BY
    DEV_VAL DESC
    ,USER ASC;