请教SQL语句

[水之轮回]

医生对病人看病开药，每开出一种药都有数量。对应如下关系：

医生工作量(ysdm,ypdm,ypsl).

ysdm：医生代码，

ypdm：药品代码，

ypsl：药品数量

函数依赖：(ysdm,ypdm)->ypsl;主码：(ysdm,ypdm)

求sql语句：

1、用药数量最多的前五名药品代码；

2、在用药数量最多的前五名药品里，列出每一种药用量最多的前十名医生代码。

[wkl1985411]

select top 5 ypdm ypsl from "表名"
group by ypdm

[水之轮回]

第一问：select top 5 ypdm from table
group by ypdm
order by sum(ypsl) desc
这个没问题
但第二问是要求第一问中5种药品用药量前十名的医生代码，一般查询结果应该是50条记录吧。

[HappinessBoy]

学习噶

[simda]

如果有两个第5名的话，上面的方法会有问题的吧

[simda]

with test as(
select ypdm, rank()over(order by sum(ypsl) desc) as Rank
from 医生工作量
group by ypdm
)
select * from test where rank <=5

这样写就可以把所有有重复名次的记录得出，比如说有两个第5名会显示6条记录

[simda]

第二个问题，同样道理

[水之轮回]

能不能将其都完整地写出来？simda(怪物史来克)说的应该是在ORACLE里的操作语句吧？不过也行。

[jyxhz]

1、用药数量最多的前五名药品代码

select top 5 ypdm from 医生工作量 group by ypdm order by sum(ypsl)

2、在用药数量最多的前五名药品里，列出每一种药用量最多的前十名医生代码

select top 5 ysdm,sum(ypsl) from 医生工作量 where ypdm in (select top 5 ypdm from 医生工作量 group by ypdm order by sum(ypsl) ) group by ysdm order by sum(ypsl)

[rookie_one]

1

select top 5 ypdm from
(
select ypdm,sum(ypsl) 数量 from 表名 group by ypdm
) t
order by 数量 DESC

[rookie_one]

select top 5 ysdm,sum(ypsl) 数量
from 表名
where ypdm in (select top 5 ypdm from 表名 group by ypdm order by sum(ypsl) DESC)
group by ysdm
order by sum(ypsl) DESC

[ojuju10]

1、
select top 5 ypdm from table
group by ypdm
order by sum(ypsl) desc
2、
select top 10 a.ysdm,b.ypdm,sum(b.ypsl) from table a,(select top 5 ypdm from table
group by ypdm order by sum(ypsl) desc ) b
where a.ypdm=b.ypdm
group by a.ysdm,b.ypdm
order by sum(b.ypsl) desc

[kenny_appleso]

1.
select top 5 ypdm from table
group by ypdm
order by sum(ypsl) desc
2.
select top 5 ysdm,sum(ypsl) 数量
from table
where ypdm in (select top 5 ypdm from 表名 group by ypdm order by sum(ypsl) DESC)
group by ysdm
order by sum(ypsl) DESC

[jyxhz]

1、用药数量最多的前五名药品代码

select top 5 ypdm from 医生工作量 group by ypdm order by sum(ypsl) DESC

2、在用药数量最多的前五名药品里，列出每一种药用量最多的前十名医生代码

select top 5 ysdm,sum(ypsl) from 医生工作量 where ypdm in (select top 5 ypdm from 医生工作量 group by ypdm order by sum(ypsl) DESC ) group by ysdm order by sum(ypsl) DESC

[simda]

在sql server 2005下写的

[xiao_yi]

1.select top 5 ypdm from hospital group by ypdm order by sum(ypsl) desc

2.select top 10 ysdm from hospital where ypdm in(select top 5 ypdm from hospital group by ypdm order by sum(ypsl) desc)


暂没考虑重名次哦。。。