员工表(employee):employeeNo(员工No),employeeName,age(年龄).......
工资表(salary):salaryDate(工资发放时间),employeeNo(员工No),salary(工资)
返回2007年2月份的高于平均工资的员工信息和工资,也即返回内容为(salaryDate,employeeNo,employeeName,age,salary)的列表。
工资表(salary):salaryDate(工资发放时间),employeeNo(员工No),salary(工资)
返回2007年2月份的高于平均工资的员工信息和工资,也即返回内容为(salaryDate,employeeNo,employeeName,age,salary)的列表。
解决方案 »
- oracle sql 语法图 能附上图讲解大概怎么看吗?都基本有哪些可看性?(不同图型代表什么寓意)
- 请教一个查询语句,上火啊 !感谢大家帮忙!
- oracle如何获得一张表所在的数据库名
- Proc 调用.pc文件时,如何传入argv参数?
- 高分求一个字段里多个内容的SQL查询
- 这样的查询语句该怎么写呢?问题有点挑战性哦!
- 那个高手帮我改造下SQL
- oracle 10g 数据如何导入到oracle 9i中?
- 如何在非归档和归档备份模式之间进行切换
- 关于Oracle817的客户端直接做安装
- 紧急求救:mfc访问oracle的number类型字段出现小数百分位丢失的问题?
- C++中 有 SQL语句 能否有游标?
FROM EMPLOYEE T1, SALARY T2
WHERE T1.EMPLOYEENO = T2.EMPLOYEENO
AND T2.SALARYDATE ='200702'
AND T2.SALARY >
( SELECT AVG(T3.SALARY) FROM SALARY T3 WHERE T3.SALARYDAE ='200702')
;
SELECT S.salaryDate, E.employeeNo, E.employeeName, E.age, S.salary
FROM employee E, salary S
WHERE E.employeeNo = S.employeeNo
AND S.salaryDate >= TO_DATE('20070201', 'YYYYMMDD')
AND S.salaryDate <= TO_DATE('20070229', 'YYYYMMDD')
AND S.salary >
(SELECT AVG(salary)
FROM salary
WHERE salaryDate >= TO_DATE('20070201', 'YYYYMMDD')
AND salaryDate <= TO_DATE('20070229', 'YYYYMMDD'))
忘记了07年2月事多少天了,对你公司什么时候发工资也不清楚。。
FROM employee E, salary S
WHERE E.employeeNo = S.employeeNo
AND TO_DATE(S.salaryDate,'YYYYMM') = '200702'
AND S.salary >
(SELECT AVG(salary)
FROM salary
WHERE TO_DATE(salaryDate,'YYYYMM') = '200702')
不过就效率而言,先用一条sql取平均值,
在用一条sql去信息比较高。
1,SELECT AVG(salary) into avgSAL
FROM salary
WHERE TO_DATE(salaryDate,'YYYYMM') = '200702'
2,SELECT S.salaryDate, E.employeeNo, E.employeeName, E.age, S.salary
FROM employee E, salary S
WHERE E.employeeNo = S.employeeNo
AND TO_DATE(S.salaryDate,'YYYYMM') = '200702'
AND S.salary > avgSAL
而to_date(salarydate,'yyyymm')='200702'
这个条件明显是错误的,to_date应该改为to_char
这也要salaryDate是日期类型才能这样做
但我想salaryDate应该是200702、200703这样的字符串吧
如salaryDate是日期SELECT S.salaryDate, E.employeeNo, E.employeeName, E.age, S.salary
FROM employee E, salary S
WHERE E.employeeNo = S.employeeNo
AND to_char(S.salaryDate,'yyyymm') = '200702'
AND S.salary >
(SELECT AVG(salary)
FROM salary
WHERE to_char(salaryDate,'yyyymm') = '200702')如是200702这样的字串
直接等于就行salaryDate='200702'
FROM employee t1,salary t2
WHERE t1.employeeNo=t2.employeeNo
AND TO_CHAR(t2.employeeDate,'YYYYMM')='200702'
AND NVL(t2.salary,0)>(SELECT AVG(NVL(t3.salary,0))
FROM salary t3
WHERE TO_CHAR(t3.employeeDate,'YYYYMM')='200702')
FROM salary s, employee e
WHERE s.employeeno = e.employeeno
AND s.salarydate = '200702'
AND s.salary > (SELECT AVG (salary)
FROM salary
WHERE salarydate = '200702')
SELECT S.salaryDate, E.employeeNo, E.employeeName, E.age, S.salary
FROM employee E, salary S
WHERE E.employeeNo = S.employeeNo
AND TO_CHAR(S.salaryDate,'YYYYMM') = '200702'
AND S.salary > (SELECT AVG(salary)
FROM salary
WHERE TO_CHAR(salaryDate,'YYYYMM') = '200702')
FROM EMPLOYEE e, SALARY s
WHERE e.EMPLOYEENO = s.EMPLOYEENO
AND s.SALARY >
( SELECT AVG(sa.SALARY) FROM SALARY sa WHERE sa.SALARYDAE ='200702' and sa.SALARYDAE =s.SALARYDAE ) ;