员工表(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)的列表。
解决方案 »
- 请教这样的函数应该怎么写,求计算公式数值在51-100范围内返回1 ,101-150返回1.5,151-200返回2。。。依次类推
- oracle查询的问题?
- 1个很简单的数据库问题:找出部门10中所有经理(MANAGER)和部门20中所有办事员(CLERK)的详细资料.
- 请问:为什么远程数据库连接总是出错呢?我错在哪里了?
- 关于ORACLE全文检索,请大家来说两句:)
- 改了服务器,就出现ora-12535的错误
- oracle外键级联更新!
- oracle高手看一下ora-00917错误
- 想写一个修改字段数据类型的过程,可是出错,请高手指点,谢谢。
- 小问题
- 紧急求救: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 ) ;