排名的计算
表格自我连接(self join),然后将结果依序列出,算出每一行之前(包括那一行本身)有多少行数
select A1.Name, A1.Sales, count(A2.Sales) rank from Total_Sales A1, Total_Sales A2 -> where A1.Sales < A2.Sales or (A1.Sales=A2.Sales and A1.Name=A2.Name) -> group by A1.Name, A1.Sales order by A1.Sales desc;
中位数的计算
方法一:使用派生表
select Name, Sales Middle from (select A1.Name,A1.Sales,count(A2.Sales) rank from Total_Sales A1,Total_Sales A2 -> where A1.Sales < A2.Sales or (A1.Sales = A2.Sales and A1.Name >= A2.Name) -> group by A1.Name, A1.Sales order by A1.Sales desc) A3 -> where A3.Rank = (select (count(*)+1) div 2 from Total_Sales);
#每个派生表必须有自己的别名,所以别名A3必须要有 #DIV是在MySQL中算出商的方式
方法二:使用视图表
create view V_1 as select A1.Name,A1.Sales,count(A2.Sales) rank from Total_Sales A1,Total_Sales A2 where A1.Sales < A2.Sales or (A1.Sales = A2.Sales and A1.Name >= A2.Name) group by A1.Name, A1.Sales order by A1.Sales desc; select Name,Sales Middle from V_1 where Rank = (select (count(*)+1) div 2 from Total_Sales);
累积总计的计算
表格自我连接(self join),然后将结果依序列出,算出每一行之前(包括那一行本身)的总和
select A1.Name, A1.Sales, SUM(A2.Sales) Total_Sales ,count(A2.Sales) Rank from Total_Sales A1, Total_Sales A2 where A2.Sales > A1.Sales or (A2.Sales = A1.Sales and A2.Name >= A1.Name) GROUP BY A1.Name,A1.Sales ORDER BY count(A2.Sales);
总合百分比的计算
select A1.Name, A1.Sales, ROUND(A1.Sales/(select sum(Sales) from Total_Sales)*100,2) || '%' Per_Sales,count(A2.Sales) Rank from Total_Sales A1, Total_Sales A2 where A2.Sales > A1.Sales or (A2.Sales = A1.Sales and A2.Name >= A1.Name) GROUP BY A1.Name,A1.Sales ORDER BY count(A2.Sales);
空值(NULL)和无值(“”)的区别
无值的长度为0,不占用空间;而空值null 的长度是null,是占用空间的;
IS NULL或者IS NOT NULL,是用来判断字段是不是NULL或者不是NULL,是不能查出是不是无值的;
无值的判断使用=’‘或者<>’'来处理。<>代表不等于;
在通过count()指定字段统计又多少行数时,如果遇到NULL值会自动忽略掉,遇到空值会自动加入记录中进行计算。
1、判断空值和无值的字符长度
select length(NULL),length(''),length('1');
2、判断并查找空值和非空值
select id,name from test10 where name is NULL; select id,name from test10 where name is not NULL;
3、判断并查找无值和非无值
select id,name from test10 where name=''; select id,name from test10 where name <> '';
4、使用count统计行数(体现null与空值的区别)
count(*) 表示包括所有列的行数,不会忽略null值;空值正常统计
count(列名) 表示只包括这一列,统计时会忽略null值的行;空值正常统计
select id,name from test10; select count(*) from test10; select count(name) from test10;