谁帮我解释一下这段SQL的意思啊

Question

select description Route_name,decode(sign(pass),-1,0,pass) pass,fail,repass,refail,round(pass/decode((pass+fail),0,1,(pass+fail)),4) yield from 
(select * from (select a.route_code,sum(b.pass) pass,sum(b.fail) fail, sum(b.repass) repass,sum(b.refail) refail 
from UW_Site_table a,UW_site_statistics_View b where a.ws_id=b.ws_id
 and work_time >=:Begin_date and (work_time<=:end_date)
 group by a.route_code) a,
 (select current_route,order_no from UW_route where Route_key=:route_key and current_route<>990 and enabled='Y') b
 where a.route_code(+)=b.current_route) a, (select lookup_code,description from UW_PARAMETERS where lookup_type='ROUTE_CODE') b 
 where b.lookup_code=a.current_route order by order_no

Answer 1

这段SQL 的意思谁能解释出来倒也强的,谁都不知道你的表是做什么的,怎么解释这段SQL是干什么的?最多解释下语法,像DECODE啊SIGN啊之类的

Answer 2

其实也不太难，这是我整理之后的句子：
select description Route_name,
       decode(sign(pass), -1, 0, pass) pass,
       fail,
       repass,
       refail,
       round(pass / decode((pass + fail), 0, 1, (pass + fail)), 4) yield
  from (select *
          from (select a.route_code,
                       sum(b.pass) pass,
                       sum(b.fail) fail,
                       sum(b.repass) repass,
                       sum(b.refail) refail
                  from UW_Site_table a, UW_site_statistics_View b
                 where a.ws_id = b.ws_id
                   and work_time >= :Begin_date
                   and (work_time <= :end_date)
                 group by a.route_code) a,
               (select current_route, order_no
                  from UW_route
                 where Route_key = :route_key
                   and current_route <> 990
                   and enabled = 'Y') b
         where a.route_code(+) = b.current_route) a,
       (select lookup_code, description
          from UW_PARAMETERS
         where lookup_type = 'ROUTE_CODE') b
 where b.lookup_code = a.current_route
 order by order_no就是个在from子句嵌套查询。希望对你能有所帮助。

Answer 3

多层嵌套查询。不知道你想要解释到什么程度？
Select Description Route_Name,
       Decode(Sign(Pass), -1, 0, Pass) Pass,
       Fail,
       Repass,
       Refail,
       Round(Pass / Decode((Pass + Fail), 0, 1, (Pass + Fail)), 4) Yield
  From (Select *
          From (Select a.Route_Code,
                       Sum(b.Pass) Pass,
                       Sum(b.Fail) Fail,
                       Sum(b.Repass) Repass,
                       Sum(b.Refail) Refail
                  From Uw_Site_Table a, Uw_Site_Statistics_View b
                 Where a.Ws_Id = b.Ws_Id
                   And Work_Time >= :Begin_Date
                   And (Work_Time <= :End_Date)
                 Group By a.Route_Code) a,
               (Select Current_Route, Order_No
                  From Uw_Route
                 Where Route_Key = :Route_Key
                   And Current_Route <> 990
                   And Enabled = 'Y') b
         Where a.Route_Code(+) = b.Current_Route) a,
       (Select Lookup_Code, Description
          From Uw_Parameters
         Where Lookup_Type = 'ROUTE_CODE') b
 Where b.Lookup_Code = a.Current_Route
 Order By Order_No

Answer 4

Select a.Route_Code,
                       Sum(b.Pass) Pass,
                       Sum(b.Fail) Fail,
                       Sum(b.Repass) Repass,
                       Sum(b.Refail) Refail
                  into a1  --插入表a1
 From Uw_Site_Table a, Uw_Site_Statistics_View b
                 Where a.Ws_Id = b.Ws_Id
                   And Work_Time >= :Begin_Date
                   And (Work_Time <= :End_Date)
                 Group By a.Route_Code
Select Current_Route, Order_No
               into b1 --插入表b1
   From Uw_Route
                 Where Route_Key = :Route_Key
                   And Current_Route <> 990
                   And Enabled = 'Y'
以下是转换的sql：
Select Description Route_Name,
       Decode(Sign(Pass), -1, 0, Pass) Pass,
       Fail,
       Repass,
       Refail,
       Round(Pass / Decode((Pass + Fail), 0, 1, (Pass + Fail)), 4) Yield
  From a1,b1 Where b.Lookup_Code = a.Current_Route
 Order By Order_No
另外，要想看a1，及vb1是什么内容，可以
select * from a1,
select * from b1