有表 worker 结构如下
code int --编号
name varchar2 --姓名
department varchar2 --部门
parday date --日期，格式为 yyyy-mm-dd
payment number --薪水
1、写出查询2008-05月发工资最多的部门sql
2、写出查询每个月发薪水超过1次的人员编号、姓名、薪水次数、薪水总额的sql
顺便帮我看看我写的第一题答案为什么得不到结果
select department
from(
select department,sum(payment) as total
from worker
group by department , to_char(parday,'yyyy-mm')
having to_char(parday,'yyyy-mm') ='2008-05'
order by total desc
) t1
where total = (select max(t1.total) from t1)
算出来不止一个结果。。。晕
谢谢各位
------解决方案--------------------
------解决方案--------------------
select *
from (
select department, sum(payment) sum_pay, row_number() over(order by sum(payment) desc) rn
from worker t
where payday between date '2008-05-01' and date '2008-05-31'
group by department
)
where rn = 1;
------解决方案--------------------
select department from (
select department,sum(payment) as payment from worker
group by department ,
to_char(parday,'yyyy-mm') having to_char(parday,'yyyy-mm') ='2008-05')
where payment = (select max(payment) from (
select department,sum(payment) as payment from worker
group by department ,
to_char(parday,'yyyy-mm') having to_char(parday,'yyyy-mm') ='2008-05'))
------解决方案--------------------
------解决方案--------------------
第二题：selecct code,name,cs,zh from (select to_char(parday ,'yyyymm') rq,code,name,count(*) cs,sum(payment ) zh from
worker group by to_char(parday ,'yyyymm'),code,name having count(*)>1)
------解决方案--------------------
第二个问题，如果你的人员编号和姓名是一一对应的，可以用下面的语句：
select code, name,to_char(parday, 'yyyy-mm') month, count(name) times, sum(payment) payment from worker group by code, name, to_char(parday, 'yyyy-mm') having count(name) > 1;
如果编号和姓名不一一对应，可以用下面的语句：
select a.code, b.name, b.month, b.times, b.payment from worker a,
(select name,to_char(parday, 'yyyy-mm') month, count(name) times, sum(payment) payment
from worker group by name, to_char(parday, 'yyyy-mm') having count(name) > 1) b
where a.name = b.name
------解决方案--------------------