一道数据库的面试题
有表 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) 

算出来不止一个结果。。。晕

谢谢各位

------解决方案--------------------

引用楼主 pxhoonet 的帖子:

有表 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
fr…

------解决方案--------------------
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'))
------解决方案--------------------

引用楼主 pxhoonet 的帖子:

有表 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
fr…

------解决方案--------------------
第二题：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
------解决方案--------------------

引用楼主 pxhoonet 的帖子:

有表 worker 结构如下
code int --编号
name varchar2 --姓名
department varchar2 --部门
parday date --日期，格式为 yyyy-mm-dd
payment number --薪水

1、写出查询2008-05月发工资最多的部门sql
2、写出查询每个月发薪水超过1次的人员编号、姓名、薪水次数、薪水总额的sql

顺便帮我看看我写的第一题答案为什么得不到结果
select department
from(