有表 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) 算出来不止一个结果晕谢谢各位
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) 算出来不止一个结果晕谢谢各位
解决方案 »
- 求一个简单的单表查询~~~
- ORALCE DATA GUARD 关于最大保护的问题。
- ORA-12514:TNS:无法解析指定的连接标识符 急求
- 求一个给定一个日期和完工期限的天数,推算出具体完工日期的函数,要求排除所有节假日。
- Hibernate查询Oracle10,中文参数问题.
- 请教各高手,怎样提高列新我速度,急!!!!
- 使用 PLSQL Developer 编 SP, FN,为什么总不返回错误信息
- 有两个问题:一个DB_LINK的问题,另一个是关于如何判断与远程数据库通讯时网络是否好的
- 刚装好的ORACLE 8.17 ,进入manager console里没有数据库啊,搜索也不行,说要我配置...
- django.db.utils.DatabaseError: ORA-12541: TNS:no listener
- 查询数据特别慢,普通的一个查询也需要几十分钟
- Oracle 10G 的安装问题
select department,sum(payment) total
*
ERROR 位于第 3 行:
ORA-00979: 不是 GROUP BY 表达式
错误ps:请不要用top 或者 rownum得出答案。谢谢
第二个问自己写了个,不怎么好
select distinct t2.code , t2.name1,paynum,to_char(t2.parday,'yyyy-mm') p,paytotal
from(
select code,count(code) as paynum,to_char(parday,'yyyy-mm') as p,sum(payment) as paytotal
from worker
group by to_char(parday,'yyyy-mm'),code
having count(code)>1) t1,worker t2
where t1.code = t2.code and t1.p = to_char(t2.parday,'yyyy-mm')
where rownum=1
where rownum=1
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;
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,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'))
worker group by to_char(parday ,'yyyymm') rq,code,name having count(*)>1)
8楼的我也想到过了 觉得代码重复太多 我就把那段重命名为他t1却得不出正确结果了
9楼的运行就有错误拉
继续up等待解答
select department from (select department,sum(payment) sum from worker where to_char(parday,'yyyymm')='200805' group by department,to_char(parday,'yyyymm') order by sum)
where rownum=1;
以上会反馈唯一结果.
这提可以这么写:
select code,name,department,to_char(parday,'yyyy-mm') paytime,count(1) count,sum(payment) totalsal from worker
group by code,name,department,to_char(parday,'yyyy-mm')
having count(1)>1;
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
SQL> select nw.department,nw.sumpay
2 from (select w.department,sum(w.payment) sumpay from worker w
3 where w.payday between '2008-05-01' and '2008-05-31'
4 group by w.department
5 ) nw
6 where nw.sumpay=(select max(sum(iw.payment)) from worker iw
7 where iw.payday between '2008-05-01' and '2008-05-31'
8 group by iw.department)
9 ;
code int --编号
name varchar2 --姓名
department varchar2 --部门
parday date --日期,格式为 yyyy-mm-dd
payment number --薪水 1、写出查询2008-05月发工资最多的部门sql
2、写出查询每个月发薪水超过1次的人员编号、姓名、薪水次数、薪水总额的sql 题1:
SELECT
DEPARTMENT,
HJ,
ROW_NUMBER()OVER(ORDER BY HJ DESC)XH
FROM
(select
DEPARTMENT,
SUM(PAYMENT)HJ
FROM WORKER
where
to_char(parday,'YYYY-MM')='2008-05'
GROUP BY DEPARTMENT)
WHERE
ROW_NUMBER()OVER(ORDER BY HJ DESC)<=2题2:
select
distinct
code,name,yf,cs,xshj
from
(select
code ,name ,to_char(parday,'yyyy-mm')yf ,count(code)cs,sum(payment)xshj
from
worker
group by code,name,to_char(parday,'yyyy-mm'))
where
cs>1
SELECT
DEPARTMENT,
HJ,
ROW_NUMBER()OVER(ORDER BY HJ DESC)XH
FROM
(select
DEPARTMENT,
SUM(PAYMENT)HJ
FROM WORKER
where
to_char(parday,'YYYY-MM')='2008-05'
GROUP BY DEPARTMENT)
WHERE
ROW_NUMBER()OVER(ORDER BY HJ DESC) =1