SQL中求字段的众数和中位数

SQL中求字段中的众数

两种方法

SELECT name ,count(1) a from  db001.table001 group by name 
having count(1) >=  all( select count(1) from table001 group by name );

SELECT name ,count(1) a from  db001.table001 group by name 
having count(1)>=  (select max(a) from ( select count(1) a  from table001 group by name )t );

SQL中求字段中的中位数，简单理解，简单方法

如果数据量为奇数，则是第 (n+1)/2 条，偶数则为 n/2 和 (n+2)/2条的和的平均值。SQL如下

select * from 
(
	select 
		*
		,row_number() over( order by money ) top
		,count(*) over( ) cnt
	from table001
)a  
where if( cnt%2=0, top in (cnt/2,(cnt + 2)/2), top = (cnt + 1)/2) 

里面的SQL查询如下：

结果图如下：