SQL Server 中位数、标准差、平均数

create table examines(    [e_id] [int] IDENTITY(1,1) NOT NULL,    [dept_name] [nvarchar](200) NULL,    [ph_score] [int] NULL)SELECT   dept_name,   AVG(sp) as '中位數'FROM(   SELECT      dept_name,      cast(ph_score as decimal(5,2)) sp,      ROW_NUMBER() OVER (         PARTITION BY dept_name          ORDER BY ph_score ASC, e_id ASC) AS RowAsc,      ROW_NUMBER() OVER (         PARTITION BY dept_name          ORDER BY ph_score DESC, e_id DESC) AS RowDesc   FROM examines SOH) xWHERE    RowAsc IN (RowDesc, RowDesc - 1, RowDesc + 1)GROUP BY dept_nameORDER BY dept_name;select dept_name,STDEV(ph_score) as '標準差' from examines group by dept_nameselect dept_name,avg(ph_score) as '平均數' from examines group by dept_name

复制代码