改进 SQL 代码

Question

我正在使用普适 SQL。我有以下多个 SQL 语句的 UNION。有没有办法清理这个问题，特别是在每个报表中选择的支付日期和位置号字段。有没有一种方法可以将其拉出来并且只有一个地方需要更改这两个字段？

(
    SELECT 
    '23400' as Gl_Number,
        y.Plan as Description,
        0 as Hours,
        ROUND(SUM(Ee_Curr),2) as Debit,
        0 as Credit
    FROM "PR_YLOC" y
    LEFT JOIN PR_SUMM s ON (s.Summ_No = y.Summ_No)
    WHERE y.Loc_No = 1041
    AND s.Pay_Date = '2010-04-02'
    AND y.Code IN (100, 105, 110)
    AND y.Type = 3
    GROUP BY y.Plan
) UNION (
    SELECT 
    '72000' as Gl_Number,
        y.Plan,
        0,
        ROUND(SUM(Er_Curr),2),
        0
    FROM "PR_YLOC" y
    LEFT JOIN PR_SUMM s ON (s.Summ_No = y.Summ_No)
    WHERE y.Loc_No = 1041
    AND s.Pay_Date = '2010-04-02'
    AND y.Code IN (100, 105, 110)
    AND y.Type = 3
    GROUP BY y.Plan
) UNION (
SELECT '24800',
       c.Plan,
       0,
       ROUND(SUM(Ee_Amt),2),
       0
    FROM "PR_CDED" c WHERE Pay_Date = '2010-04-02' AND Loc_No = 1041 AND Code = 100
    GROUP BY c.Plan
) UNION (
SELECT '24800',
       c.Plan,
       0,
       0,
       ROUND(SUM(Ee_Amt),2)
    FROM "PR_CDED" c WHERE Pay_Date = '2010-04-02' AND Loc_No = 1041 AND Code = 115
    GROUP BY c.Plan
) UNION (
SELECT '24150',
       c.Plan,
       0,
       0,
       ROUND(SUM(Ee_Amt),2)    
    FROM "PR_CDED" c WHERE Pay_Date = '2010-04-02' AND Loc_No = 1041 AND Code = 241
    GROUP BY c.Plan
) UNION (
SELECT '24150',
       c.Plan,
       0,
       ROUND(SUM(Ee_Amt),2),
       0
    FROM "PR_CDED" c WHERE Pay_Date = '2010-04-02' AND Loc_No = 1041 AND Code = 239
    GROUP BY c.Plan
) UNION (
SELECT '24120',
       c.Plan,
       0,
       ROUND(SUM(Ee_Amt),2),
       0
    FROM "PR_CDED" c WHERE Pay_Date = '2010-04-02' AND Loc_No = 1041 AND Code = 230
    GROUP BY c.Plan
) UNION (
SELECT '24100',
       c.Plan,
       0,
       ROUND(SUM(Ee_Amt),2),
       0
    FROM "PR_CDED" c WHERE Pay_Date = '2010-04-02' AND Loc_No = 1041 AND Code = 225
    GROUP BY c.Plan
) UNION (
SELECT '23800',
       c.Plan,
       0,
       ROUND(SUM(Ee_Amt),2),
       0
    FROM "PR_CDED" c WHERE Pay_Date = '2010-04-02' AND Loc_No = 1041 AND Code = 245
    GROUP BY c.Plan
) UNION (
select m.Def_Dept as Gl_Number, t.Short_Desc,
    (SELECT SUM(Hours) FROM pr_earn en WHERE en.Loc_No = e.Loc_No AND en.Emp_No = e.Emp_No AND en.Pay_Date = e.Pay_Date AND en.Pay_Code = e.Pay_Code) as Hours,
    (SELECT SUM(Pay_Amt) FROM pr_earn en WHERE en.Loc_No = e.Loc_No AND en.Emp_No = e.Emp_No AND en.Pay_Date = e.Pay_Date AND en.Pay_Code = e.Pay_Code) as Debit,
    0
from pr_earn e
left join pr_mast m on (e.Loc_No = m.Loc_No and e.Emp_No = m.Emp_No)
left join pr_ptype t ON (t.Code = e.Pay_Code)
where e.loc_no = 1041 and e.pay_date = '2010-04-02'
group by m.Def_Dept, t.Short_Desc
)

谢谢

Answer 1

你能用这样的东西替换中间的一组 SELECT 语句吗？

) UNION (
SELECT CASE Code
         WHEN 100 THEN '24800'
         WHEN 115 THEN '24800',
         WHEN 241 THEN '24150'
         WHEN 239 THEN '24150',
         WHEN 230 THEN '24120',
         WHEN 225 THEN '24100'
         WHEN 245 THEN '23800',
       END,
       c.Plan,
       0,
       ROUND(SUM(Ee_Amt),2),
       0
    FROM "PR_CDED" c WHERE Pay_Date = '2010-04-02' AND Loc_No = 1041 AND Code IN (100, 115, 241, 239, 230, 225, 245)
    GROUP BY c.Plan
) UNION (

这是一个 T-SQL CASE 语句，但我确信 Oracle PL/SQL 也有类似的东西。

Answer 2

您可以仅用一个具有过滤条件 AND Code IN (... ，...，...）。

Answer 3

似乎您正在尝试在不影响性能的情况下获得更多可维护性。我不知道这个平台，但我猜你可以将一个 where 子句移到最后，将 Union 作为子查询，并且查询优化器可能能够找出在每个联合集中应用 where 条件。

Answer 4

您可以为此使用 case 语句。例如：

SELECT  
'23400' as Gl_Number, 
    y.Plan as Description, 
    0 as Hours, 
    ROUND(SUM(Ee_Curr),2) as Debit, 
    0 as Credit 
FROM "PR_YLOC" y 
LEFT JOIN PR_SUMM s ON (s.Summ_No = y.Summ_No) 
WHERE y.Loc_No = 1041 
AND s.Pay_Date = '2010-04-02' 
AND y.Code IN (100, 105, 110) 
AND y.Type = 3 
GROUP BY y.Plan 
UNION
SELECT  
'72000' as Gl_Number, 
    y.Plan, 
    0, 
    ROUND(SUM(Er_Curr),2), 
    0 
FROM "PR_YLOC" y 
LEFT JOIN PR_SUMM s ON (s.Summ_No = y.Summ_No) 
WHERE y.Loc_No = 1041 
AND s.Pay_Date = '2010-04-02' 
AND y.Code IN (100, 105, 110) 
AND y.Type = 3 
GROUP BY y.Plan 
UNION
SELECT case Code 
            when Code in (100,115) then'24800'
            when Code in (241, 239) then'24150'
            when Code = 230 then'24120'
            when Code = 225 then'24100'
            when Code = 245 then'23800'
        end, 
       c.Plan, 
       0, 
       ROUND(SUM(Ee_Amt),2), 
       0 
    FROM "PR_CDED" c WHERE Pay_Date = '2010-04-02' AND Loc_No = 1041 AND Code in (100,115,241,239,230,225,245)
    GROUP BY c.Plan 
select m.Def_Dept as Gl_Number, t.Short_Desc, 
    (SELECT SUM(Hours) FROM pr_earn en WHERE en.Loc_No = e.Loc_No AND en.Emp_No = e.Emp_No AND en.Pay_Date = e.Pay_Date AND en.Pay_Code = e.Pay_Code) as Hours, 
    (SELECT SUM(Pay_Amt) FROM pr_earn en WHERE en.Loc_No = e.Loc_No AND en.Emp_No = e.Emp_No AND en.Pay_Date = e.Pay_Date AND en.Pay_Code = e.Pay_Code) as Debit, 
    0 
from pr_earn e 
left join pr_mast m on (e.Loc_No = m.Loc_No and e.Emp_No = m.Emp_No) 
left join pr_ptype t ON (t.Code = e.Pay_Code) 
where e.loc_no = 1041 and e.pay_date = '2010-04-02' 
group by m.Def_Dept, t.Short_Desc