新人报道，求解SQL

[pmt_style]

[pmt_style]

谢谢哥哥 这个真不错

[pmt_style]

[pmt_style]

[yyw6637]

我擦，哪来的题，出题人脑子有问题吧，员工表和薪水级别表没什么联系啊

[pmt_style]

所以不好写啊

[nwsuafer]

查询工资的时候case when ，decode都可以的吧 判断empSal在哪个salClass的minSal和maxSal之间

[yyw6637]

写是可以写，但是难度很大，case when和decode在这是用不着的

[pmt_style]

楼上说的我都试过了，Case when end 不好实现， 只能用表连接 ，不过嵌套很多，求答案

[yyw6637]

select e.empname,
       e.empsal,
       (select sal.salclass
          from sal
         where e.empsal between sal.minsal and sal.maxsal) as 级别,
       (select count(*)
          from emp
         where emp.empsal between
               (select sal.minsal
                  from sal
                 where sal.salclass =
                       (select sal.salclass
                          from sal
                         where e.empsal between sal.minsal and sal.maxsal))
           and (select sal.maxsal
                  from sal
                 where sal.salclass =
                       (select sal.salclass
                          from sal
                         where e.empsal between sal.minsal and sal.maxsal))) as 人数,
       (select avg(emp.empsal)
          from emp
         where emp.empsal between
               (select sal.minsal
                  from sal
                 where sal.salclass =
                       (select sal.salclass
                          from sal
                         where e.empsal between sal.minsal and sal.maxsal))
           and (select sal.maxsal
                  from sal
                 where sal.salclass =
                       (select sal.salclass
                          from sal
                         where e.empsal between sal.minsal and sal.maxsal))) as 平均
  from emp e
 where e.empid = 1

[yyw6637]

写是写出来了，不过有点繁琐，不知道谁还有更好的方法

[pmt_style]

哇，这么多，运行结果出来木

[pmt_style]

Select sal ,salClass ,count(*), vag(sal) from (Select salClass,empName,empId from emp e join sal s on e.sal between s.losal and s.hisal) where salClass ='select slaClass from emp where empId ='1'' group by salClass 你看看我写的哪里不对

[yyw6637]

我写的结果是正确，你用group by是什么意思？

[pmt_style]

按级别分组啊， 第一步取得没个员工的薪水级别 与原来的来做表连接 ，第二部 取得薪水级别 与empID=1级别的所有员工信息 ，第三个 取 平均薪水，人员数量 ，面试题，不会那么长吧，帮我写简单点，第一次怎么给你分呢？ 我全给你了

[yyw6637]

首先你的想法说错误的，你group by查出来的结果是什么样的你可以想到，是多条，而根据题目查出来数据应该是一条，这不算长吧，才30多行

[pmt_style]

谢了，你头像我好喜欢~

[yyw6637]

select a.empname 姓名,
       a.empsal 薪水,
       (select salclass from sal where a.empsal between minsal and maxsal) 薪水级别,
       (select count(b.empname)
          from emp b, sal c
         where c.salclass =
               (select salclass
                  from sal
                 where a.empsal between minsal and maxsal)
           and b.empsal between c.minsal and c.maxsal) 同级别人数,
       (select avg(b.empsal)
          from emp b, sal c
         where c.salclass =
               (select salclass
                  from sal
                 where a.empsal between minsal and maxsal)
           and b.empsal between c.minsal and c.maxsal) 同级别平均
  from emp a
 where a.empid = '1';

这个稍微好点