求教SQL语句如下:
表1
id dpart_id name selary
1 1 jake 2500
2 2 Lict 3500
3 3 jack 3500
4 1 Licy 2600
5 2 Kite 3000表2
dpart_id department
1 M &R
2 Developer
3 Manger写出返回 平均薪水avg(selary)<3000部门的薪水和部门名department 的SQL语句。
表1
id dpart_id name selary
1 1 jake 2500
2 2 Lict 3500
3 3 jack 3500
4 1 Licy 2600
5 2 Kite 3000表2
dpart_id department
1 M &R
2 Developer
3 Manger写出返回 平均薪水avg(selary)<3000部门的薪水和部门名department 的SQL语句。
from 表1 join 表2 on 表1.dpart_id =表2.dpart_id
having(avg(selary)<3000)
SELECT TABLE3.DPART_ID,TABLE2.DEPARTMENT FROM TABLE2 INNER JOIN
(SELECT DPART_ID,AVG(SALARY) AS DPART_AVG FROM TABLE1 GROUP BY DPART_ID) AS TABLE3
ON TABLE2.DPART_ID=TABLE3.DPART_ID
自己看看help,不到一个星期,要怎样,就写这样的。
不会 ,问问邹建,他是sql顶级高手
(SELECT DPART_ID,AVG(SALARY) AS DPART_AVG FROM TABLE1 GROUP BY DPART_ID) AS TABLE3
ON TABLE2.DPART_ID=TABLE3.DPART_ID
WHERE TABLE3.DPART_AVG<3000
SELECT TABLE3.DPART_ID,TABLE2.DEPARTMENT,TABLE3.DPART_AVG FROM TABLE2 INNER JOIN
(SELECT DPART_ID,AVG(SALARY) AS DPART_AVG FROM TABLE1 GROUP BY DPART_ID HAVING AVG(SALARY)<3000) AS TABLE3
ON TABLE2.DPART_ID=TABLE3.DPART_ID