关系数据库关系如下
药品表:M(药品编号(mno),药品名称(mname),规格(mst),价格(mprice),生产日期(mdate),保质期(mex));
处方表:C(处方编号(cno),医生编号(tno),病人编号(bno),处方日期(cdate),处方总价格(czj)) ;
药品处方细节表:CM(处方编号(cno),药品编号(mno),药品数量(mqu),药品单价(mprice));
题目
查询2019年每个月使用量最高的药品编号和药品名称,要求输出月份,药品编号,药品名称以及使用数量。
题目似乎要求直接查询,但是直接嵌套查询中似乎无法使用开窗函数,无法通过一个语句完成查询,需要借助视图,如果不用开窗函数则也需要中间表,所以暂时使用视图完成,后续学习询问后再待改进、修改。
create view 分类 as
select MONTH(cdate) as 月份,M.mno 药品编号,mname 药品名称,CM.mqu,sum(CM.mqu) over( partition by MONTH(cdate),M.mno order by CM.mqu)本月累计
from C,CM,M
where CM.mno=M.mno and CM.cno=C.cno and YEAR(cdate)=2019
create view 排名 as
select 月份,药品编号,药品名称,本月累计,rank()over(partition by 月份 order by 本月累计 desc) as [rank]
from 分类
select *
from 排名
where [rank]=1
知识总结:
1.(1)函数体:sum(待求和列名)over(partition by 待分组列名 order by 待排序列名 desc/asc) 分组中的sum列上出现累加,累加顺序为排序顺序
(2)函数体:sum(待求和列名)over(partition by 待分组列名) 不会在sum列上出现累加,分组中的sum列的值全部为该分组sum值
2.函数体:rank()over(partition by 待分组列名 order by 待排序列名 desc/asc)
rank()是跳跃排序,eg:两个第一之后是第三
dense_rank()是连续排序,eg:两个第一之后是第二