×

Loading...

Topic

This topic has been archived. It cannot be replied.
  • 工作学习 / IT技术讨论 / help! SQL assignment due tomorrow... -ely(Ely); 2001-2-26 {814} (#28803@0)
    Table EMPLOYEE has three fields: empname, deptname and empsalary, how to write the query to find the employee name whose salary is higher than the average salary of his department?

    For example. the data look like this
    EMPNAME EMPSALARY DEPTNAME
    Mike 1000 A
    Tom 1100 A
    Jame 1800 A
    Peter 2000 A
    Bob 1900 B
    Alice 2200 B
    Ben 1000 B
    Joe 1300 B

    How to get this output:
    Jame 1800 A
    Peter 2000 A
    Bob 1900 B
    Alice 2200 B
    • 好长时间不用了 -guest:guest; 2001-2-26 {80} (#28808@0)
      select * from table_name where salary > (select average(salary) from table_name)
      • You mean WHERE EMPSALARY>(SELECT AVG(EMPSALARY) FROM EMP GROUP BY DEPTNAME)? wrong.... -ely(Ely); 2001-2-26 (#28809@0)
      • you have to compare the salary with the avg in the same dept. -ely(Ely); 2001-2-26 (#28810@0)
        • 不好意思，没看到下半截 -guest:guest; 2001-2-26 {143} (#28819@0)
          这样行不行？
          select name, salary, dept d from tb where salary > (select avg(salary) from tb where dept = d)
          如果不行，可以用stored procedure.
          • 有错了 -guest:guest; 2001-2-26 {100} (#28822@0)
            这样呢？
            select name, salary, dept d from tb where salary > (select avg(salary) from tb group by d)
          • 别傻了，不是这样的， 不要用store procedure来干这种简单的工作. -tuantuan(团团); 2001-2-26 (#28834@0)
    • very simple select * from employee t1 where t1.empsalary > (select avg(empsalary) from employee t2 where t2.depname = t1.depname ) -tuantuan(团团); 2001-2-26 (#28820@0)
      • (select avg(empsalary) from employee t2 where t2.depname = t1.depname ) doesn't work at all, there is no t1 inside braket.. -ely(Ely); 2001-2-26 (#28823@0)
        • please try the whole comment, statement in () can inherit those alias like "t1"defined outside, I guess you are a new guy, and havn't got the sql sever here, but I do, and try the statement already. Trust me. -tuantuan(团团); 2001-2-26 {1063} (#28841@0)
          本文发表在 rolia.net 枫下论坛my work result in sql server.
          select * from employee
          empname depname empsalary
          -------------------- -------------------- --------------------
          Mike A 1000
          Tom A 1100
          Jame A 1800
          Peter A 2000
          Bob B 1900
          Alice B 2200
          Ben B 1000
          Joe B 1300

          (8 row(s) affected)

          select * from employee t1 where t1.empsalary >
          (select avg(empsalary) from employee t2 where t2.depname = t1.depname )


          empname depname empsalary
          -------------------- -------------------- --------------------
          Jame A 1800
          Peter A 2000
          Bob B 1900
          Alice B 2200

          (4 row(s) affected)更多精彩文章及讨论，请光临枫下论坛 rolia.net
          • ic, thanks!!! -ely(Ely); 2001-2-26 (#28843@0)
          • I remember that avg should have group ! -guest:guest; 2001-2-26 (#28850@0)
          • the whole question is for each department, get the average salary of emloyees who have higher salaries than the average salary of the department -ely(Ely); 2001-2-27 {257} (#28853@0)
            so the answer should be:

            SELECT A.DEPTNAME, AVG(A.EMPSALARY)
            FROM EMP A
            WHERE A.EMPSALARY > (SELECT AVG(B.EMPSALARY)
            FROM EMPLOYEE B
            GROUP BY B.DEPTNAME
            WHERE B.DEPTNAME = A.DEPTNAME )
            GROUP BY A.DEPTNAME

            IS IT RIGHT?
            i HAVE NO SQL AT HOME...
          • 顽固分子们，group by 只是对结果进行分类，只是显示顺序不一样，对结果没有影响的。 -tuantuan(团团); 2001-2-27 {681} (#28863@0)
            如果实在要用group by , try
            select * from employee t1 where t1.empsalary >
            (select avg(empsalary) from employee t2 where t2.depname = t1.depname ) group by t1 depname
            Ely 的statment 有两处严重错误 1.Invalid object name 'EMP'
            2.在 avg 后 用GROUP BY B.DEPTNAME Incorrect syntax near the keyword 'WHERE'.
            让我详细讲解我的statement的含义吧.
            把employee 映射到 t1, t2,这样指针在t1,t2中的移动就不会互相影响,t1 中的记录会每一条扫一遍,而每扫一条t1的记录, 就会在t2对应t1.depname 将t2中相同depname做avg. 所以要是某个dep有10个员工,就会分别扫t1 符合条件的记录一次, 扫t2 每条符合条件的记录10 次! ,t1中的记录和avg比较后,符合条件的就会放到结果集中,再用group by 或order by 分组或排序.
            • Got you! -ely(Ely); 2001-2-27 (#28919@0)
    • 看到你的帖子，真是有很多感慨！自从去年6月递表之后，开始从技术管理转向开发——苦！ -feather(feather); 2001-2-27 {210} (#28855@0)
      先把ASP捡了起来；七月MS出了.NET，ASPNG在WIN2000下的确不错，就是要用C#或VB7，一怒之下转向JAVA，在WINDOWS平台上的APPLICATION SERVER找不到感觉，现在回头去装solaris——这可是我四年前干的活呀。早知如此，何必当初呢。
      • are you talking to me? -ely(Ely); 2001-2-27 (#28856@0)
        • Not specific to anybody.Just some comments on my own experience. -feather(feather); 2001-2-27 (#28857@0)
          • I finished three assignments(projects) for different courses today, didn't eat anything.. you say 苦?, will not be regret in the future..it's late, i 'm going to sleep now, take care. -ely(Ely); 2001-2-27 (#28859@0)
枫下论坛主坛 /  工作学习 /  IT技术讨论