select 
user_id as USER
,point as PT
,case when hensa = 0
    then 50
    else round(((point - ave)*10)/hensa+50,1)
end as DEV_VAL
from test_results
inner join (
    select --標準偏差
    sqrt(avg((point - ave)*(point - ave))) as hensa
    from test_results
    inner join (
        select
        avg(point) as ave
        from test_results
        where test_id = '100'
    )
    where test_id = '100'
)
inner join (
    select
    avg(point) as ave
    from test_results
    where test_id = '100'
)
where test_id = '100'
order by DEV_VAL desc,user_id