关于 SQL 查询.......sos.....sos.....sos

Question

--定义业务员的提成参数表
declare @h5 float    --输入参数：香港件收件每票价钱(5kg)
declare @hs float    --输入参数：续重
declare @hh float    --输入参数：最高declare @s5 float    --输入参数：国内件收件每票价钱(5kg)
declare @ss float    --输入参数：续重
declare @sh float    --输入参数：最高declare @p5 float    --输入参数：国内件派件每票价钱(5kg)
declare @ps float    --输入参数：续重
declare @ph float    --输入参数：最高
-------赋值
select @h5=3.5,@hs=0.4,@hh=43.5
select @s5=2.8,@ss=0.3,@sh=25
select @p5=0.75,@ps=0.15,@ph=12--------------------------------------------------------------------------select a.workerno as 工号,a.name as 姓名,
       isnull(b.copys,0) as 收件份数,isnull(b.weight,0) as 收件重量,
       isnull(b.money,0) as 收件金额,isnull(b.pay,0) as 收件奖金,
       isnull(c.copys,0) as 派件份数,isnull(c.weight,0) as 派件重量,
       isnull(c.money,0) as 派件金额,isnull(c.pay,0) as 派件奖金,
       isnull(b.pay,0)+isnull(c.pay,0) as 奖金总额,
       a.salary as 工资,
       a.salary+isnull(b.pay,0)+isnull(c.pay,0) as 应发金额
from (select workerno,name,salary from worker) a
left outer join(select workerno,count(*) as copys,sum(weight) as weight,sum(fmoney) as money,
 
-----------------------------------------求收件奖金-------------------------------------------  
      
        sum(case when substring(sarea,1,3)='852' then case  when weight<=5 then @h5
                                                            else case when (floor(weight/5)*@h5+(weight-floor(weight/5))*@hs)>=@hh 
                                                                      then @hh
                                                                      else floor(weight/5)*@h5+(weight-floor(weight/5))*@hs
                                                                 end
                                                      end  
                 else  case  when weight<=5 then @s5
                             else case when (floor(weight/5)*@s5+(weight-floor(weight/5))*@ss)>=@sh 
                                       then @sh
                                       else floor(weight/5)*@s5+(weight-floor(weight/5))*@ss
                                  end
                       end  
            end) as pay---------------------------------------------------------------------------------------------       from fastorder where workerno=worker.workerno group by workerno)b
                                    --这里
on (a.workerno=b.workerno)        
left outer join(select sworker,count(*) as copys,sum(weight) as weight,sum(fmoney) as money,-----------------------------------------求派件奖金-------------------------------------------        sum(case when weight<=5 then @p5
                 else case when (floor(weight/5)*@p5+(weight-floor(weight/5))*@ps)>=@ph 
                           then @sh
                           else floor(weight/5)*@p5+(weight-floor(weight/5))*@ps
                      end 
            end) as pay----------------------------------------------------------------------------------------------        from fastorder where sworker=worker.workerno group by sworker)c
                                     --这里
on (a.workerno=c.sworker)

Answer 1

select * from (select workerno,name,salary from worker) a  
select * from (select * from (select workerno,name,salary from worker) a  )tem----------------------------------------------------------------------------------定义业务员的提成参数表
declare @h5 float    --输入参数：香港件收件每票价钱(5kg)
declare @hs float    --输入参数：续重
declare @hh float    --输入参数：最高declare @s5 float    --输入参数：国内件收件每票价钱(5kg)
declare @ss float    --输入参数：续重
declare @sh float    --输入参数：最高declare @p5 float    --输入参数：国内件派件每票价钱(5kg)
declare @ps float    --输入参数：续重
declare @ph float    --输入参数：最高
-------赋值
select @h5=3.5,@hs=0.4,@hh=43.5
select @s5=2.8,@ss=0.3,@sh=25
select @p5=0.75,@ps=0.15,@ph=12--------------------------------------------------------------------------select a.workerno as 工号,a.name as 姓名,
       isnull(b.copys,0) as 收件份数,isnull(b.weight,0) as 收件重量,
       isnull(b.money,0) as 收件金额,isnull(b.pay,0) as 收件奖金,
       isnull(c.copys,0) as 派件份数,isnull(c.weight,0) as 派件重量,
       isnull(c.money,0) as 派件金额,isnull(c.pay,0) as 派件奖金,
       isnull(b.pay,0)+isnull(c.pay,0) as 奖金总额,
       a.salary as 工资,
       a.salary+isnull(b.pay,0)+isnull(c.pay,0) as 应发金额
from (select workerno,name,salary from worker) a
left outer join(select workerno,count(*) as copys,sum(weight) as weight,sum(fmoney) as money,
 
-----------------------------------------求收件奖金-------------------------------------------  
      
        sum(case when substring(sarea,1,3)='852' then case  when weight<=5 then @h5
                                                            else case when (floor(weight/5)*@h5+(weight-floor(weight/5))*@hs)>=@hh 
                                                                      then @hh
                                                                      else floor(weight/5)*@h5+(weight-floor(weight/5))*@hs
                                                                 end
                                                      end  
                 else  case  when weight<=5 then @s5
                             else case when (floor(weight/5)*@s5+(weight-floor(weight/5))*@ss)>=@sh 
                                       then @sh
                                       else floor(weight/5)*@s5+(weight-floor(weight/5))*@ss
                                  end
                       end  
            end) as pay---------------------------------------------------------------------------------------------       from fastorder,worker where workerno=worker.workerno group by workerno)b
---------------------^^^^^^^^---------------^^^^^^^^^^^^^^^----------
on (a.workerno=b.workerno)        
left outer join(select sworker,count(*) as copys,sum(weight) as weight,sum(fmoney) as money,-----------------------------------------求派件奖金-------------------------------------------        sum(case when weight<=5 then @p5
                 else case when (floor(weight/5)*@p5+(weight-floor(weight/5))*@ps)>=@ph 
                           then @sh
                           else floor(weight/5)*@p5+(weight-floor(weight/5))*@ps
                      end 
            end) as pay----------------------------------------------------------------------------------------------        from fastorder,worker where sworker=worker.workerno group by sworker)c
----------------------^^^^^^^---------------^^^^^^^^^^^^^^^^
on (a.workerno=c.sworker)

Answer 2

--定义业务员的提成参数表
declare @h5 float    --输入参数：香港件收件每票价钱(5kg)
declare @hs float    --输入参数：续重
declare @hh float    --输入参数：最高declare @s5 float    --输入参数：国内件收件每票价钱(5kg)
declare @ss float    --输入参数：续重
declare @sh float    --输入参数：最高declare @p5 float    --输入参数：国内件派件每票价钱(5kg)
declare @ps float    --输入参数：续重
declare @ph float    --输入参数：最高
-------赋值
select @h5=3.5,@hs=0.4,@hh=43.5
select @s5=2.8,@ss=0.3,@sh=25
select @p5=0.75,@ps=0.15,@ph=12--------------------------------------------------------------------------select a.workerno as 工号,a.name as 姓名,
       isnull(b.copys,0) as 收件份数,isnull(b.weight,0) as 收件重量,
       isnull(b.money,0) as 收件金额,isnull(b.pay,0) as 收件奖金,
       isnull(c.copys,0) as 派件份数,isnull(c.weight,0) as 派件重量,
       isnull(c.money,0) as 派件金额,isnull(c.pay,0) as 派件奖金,
       isnull(b.pay,0)+isnull(c.pay,0) as 奖金总额,
       a.salary as 工资,
       a.salary+isnull(b.pay,0)+isnull(c.pay,0) as 应发金额
from (select workerno,name,salary from worker) aleft outer join(select workerno,count(*) as copys,sum(weight) as weight,sum(fmoney) as money,
 
-----------------------------------------求收件奖金-------------------------------------------  
      
        sum(case when substring(sarea,1,3)='852' then case  when weight<=5 then @h5
                                                            else case when (floor(weight/5)*@h5+(weight-floor(weight/5))*@hs)>=@hh 
                                                                      then @hh
                                                                      else floor(weight/5)*@h5+(weight-floor(weight/5))*@hs
                                                                 end
                                                      end  
                 else  case  when weight<=5 then @s5
                             else case when (floor(weight/5)*@s5+(weight-floor(weight/5))*@ss)>=@sh 
                                       then @sh
                                       else floor(weight/5)*@s5+(weight-floor(weight/5))*@ss
                                  end
                       end  
            end) as pay---------------------------------------------------------------------------------------------       from fastorder where workerno=a.workerno group by workerno) b
on (a.workerno=b.workerno) 
       
left outer join(select sworker,count(*) as copys,sum(weight) as weight,sum(fmoney) as money,-----------------------------------------求派件奖金-------------------------------------------        sum(case when weight<=5 then @p5
                 else case when (floor(weight/5)*@p5+(weight-floor(weight/5))*@ps)>=@ph 
                           then @sh
                           else floor(weight/5)*@p5+(weight-floor(weight/5))*@ps
                      end 
            end) as pay----------------------------------------------------------------------------------------------        from fastorder where sworker=a.workerno group by sworker) c
on (a.workerno=c.sworker)

Answer 3

将下列去掉：
where workerno=a.workerno
where sworker=a.workerno

Answer 4

--定义业务员的提成参数表
declare @h5 float    --输入参数：香港件收件每票价钱(5kg)
declare @hs float    --输入参数：续重
declare @hh float    --输入参数：最高declare @s5 float    --输入参数：国内件收件每票价钱(5kg)
declare @ss float    --输入参数：续重
declare @sh float    --输入参数：最高declare @p5 float    --输入参数：国内件派件每票价钱(5kg)
declare @ps float    --输入参数：续重
declare @ph float    --输入参数：最高
-------赋值
select @h5=3.5,@hs=0.4,@hh=43.5
select @s5=2.8,@ss=0.3,@sh=25
select @p5=0.75,@ps=0.15,@ph=12--------------------------------------------------------------------------select a.workerno as 工号,a.name as 姓名,
       isnull(b.copys,0) as 收件份数,isnull(b.weight,0) as 收件重量,
       isnull(b.money,0) as 收件金额,isnull(b.pay,0) as 收件奖金,
       isnull(c.copys,0) as 派件份数,isnull(c.weight,0) as 派件重量,
       isnull(c.money,0) as 派件金额,isnull(c.pay,0) as 派件奖金,
       isnull(b.pay,0)+isnull(c.pay,0) as 奖金总额,
       a.salary as 工资,
       a.salary+isnull(b.pay,0)+isnull(c.pay,0) as 应发金额
from (select workerno,name,salary from worker) as aleft outer join(select workerno,count(*) as copys,sum(weight) as weight,sum(fmoney) as money,
 
-----------------------------------------求收件奖金-------------------------------------------  
      
        sum(case when substring(sarea,1,3)='852' then case  when weight<=5 then @h5
                                                            else case when (floor(weight/5)*@h5+(weight-floor(weight/5))*@hs)>=@hh 
                                                                      then @hh
                                                                      else floor(weight/5)*@h5+(weight-floor(weight/5))*@hs
                                                                 end
                                                      end  
                 else  case  when weight<=5 then @s5
                             else case when (floor(weight/5)*@s5+(weight-floor(weight/5))*@ss)>=@sh 
                                       then @sh
                                       else floor(weight/5)*@s5+(weight-floor(weight/5))*@ss
                                  end
                       end  
            end) as pay---------------------------------------------------------------------------------------------       from fastorder group by workerno) as b
on (a.workerno=b.workerno) 
       
left outer join(select sworker,count(*) as copys,sum(weight) as weight,sum(fmoney) as money,-----------------------------------------求派件奖金-------------------------------------------        sum(case when weight<=5 then @p5
                 else case when (floor(weight/5)*@p5+(weight-floor(weight/5))*@ps)>=@ph 
                           then @sh
                           else floor(weight/5)*@p5+(weight-floor(weight/5))*@ps
                      end 
            end) as pay----------------------------------------------------------------------------------------------        from fastorder group by sworker) as c
on (a.workerno=c.sworker)

Answer 5

修改后的：错误主要大家都说出来了。我还真是太差了。--定义业务员的提成参数表
declare @h5 float    --输入参数：香港件收件每票价钱(5kg)
declare @hs float    --输入参数：续重
declare @hh float    --输入参数：最高declare @s5 float    --输入参数：国内件收件每票价钱(5kg)
declare @ss float    --输入参数：续重
declare @sh float    --输入参数：最高declare @p5 float    --输入参数：国内件派件每票价钱(5kg)
declare @ps float    --输入参数：续重
declare @ph float    --输入参数：最高
-------赋值
select @h5=3.5,@hs=0.4,@hh=43.5
select @s5=2.8,@ss=0.3,@sh=25
select @p5=0.75,@ps=0.15,@ph=12--------------------------------------------------------------------------select a.workerno as 工号,a.name as 姓名,
       isnull(b.copys,0) as 收件份数,isnull(b.weight,0) as 收件重量,
       isnull(b.money,0) as 收件金额,isnull(b.pay,0) as 收件奖金,
       isnull(c.copys,0) as 派件份数,isnull(c.weight,0) as 派件重量,
       isnull(c.money,0) as 派件金额,isnull(c.pay,0) as 派件奖金,
       isnull(b.pay,0)+isnull(c.pay,0) as 奖金总额,
       a.salary as 工资,
       a.salary+isnull(b.pay,0)+isnull(c.pay,0) as 应发金额
from (select * from (select workerno,name,salary from worker)tmp ) as a
left outer join(select workerno,count(*) as copys,sum(weight) as weight,sum(fmoney) as money,
 
-----------------------------------------求收件奖金-------------------------------------------  
      
        sum(case when substring(sarea,1,3)='852' then case  when weight<=5 then @h5
                                                            else case when (floor(weight/5)*@h5+(weight-floor(weight/5))*@hs)>=@hh 
                                                                      then @hh
                                                                      else floor(weight/5)*@h5+(weight-floor(weight/5))*@hs
                                                                 end
                                                      end  
                 else  case  when weight<=5 then @s5
                             else case when (floor(weight/5)*@s5+(weight-floor(weight/5))*@ss)>=@sh 
                                       then @sh
                                       else floor(weight/5)*@s5+(weight-floor(weight/5))*@ss
                                  end
                       end  
            end) as pay---------------------------------------------------------------------------------------------       from fastorder group by workerno)b
on (a.workerno=b.workerno)        
left outer join(select sworker,count(*) as copys,sum(weight) as weight,sum(fmoney) as money,-----------------------------------------求派件奖金-------------------------------------------        sum(case when weight<=5 then @p5
                 else case when (floor(weight/5)*@p5+(weight-floor(weight/5))*@ps)>=@ph 
                           then @sh
                           else floor(weight/5)*@p5+(weight-floor(weight/5))*@ps
                      end 
            end) as pay----------------------------------------------------------------------------------------------        from fastorder group by sworker)c
on (a.workerno=c.sworker)order by a.workerno