你的位置:首页 > ASP.net教程

[ASP.net教程]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