样品表
EmployeeID | AssignmentID | WageCode | CompanyName | BillRate | BillTotal
1 | 1 | Regular | CompanyOne | 10 | 400
1 | 2 | Regular | CompanyTwo | 11 | 440
1 | 1 | Overtime | CompanyOne | 15 | 150
1 | 1 | Mileage | CompanyOne | 0 | 20
2 | 3 | Regular | CompanyThree| 20 | 800
2 | 3 | Regular | CompanyThree| 20 | 800
2 | 3 | Overtime | CompanyThree| 30 | 90
2 | 3 | Mileage | CompanyThree| 0 | 60
我只想显示 WageCode 为“Regular”的行,按 EmployeeID、WageCode、AssignmentID、CompanyName 和 BillRate 分组,并将其他工资代码转入列。
最终结果应如下所示:
EmployeeID | AssignmentID | CompanyName | RegBillRate | RegBill | OTBillRate | OTBill | MileageBill
1 | 1 | CompanyOne | 10 | 400 | 15 | 150 | 20
1 | 2 | CompanyTwo | 11 | 440 | 0 | 0 | 0
2 | 3 | CompanyThree| 20 | 1600 | 30 | 90 | 60
有什么更简洁的方法可以做到这一点,而不是像这样的一堆 with 语句:
with regular as
(select EmployeeID, AssignmentID, CompanyName, BillRate, sum(BillTotal) Total from SampleTable where wage code = 'Regular' group by EmployeeID, AssignmentID, CompanyName, BillRate
),
overtime as
(select EmployeeID, AssignmentID, CompanyName, BillRate, sum(BillTotal) Total from SampleTable where wage code = 'Overtime' group by EmployeeID, AssignmentID, CompanyName, BillRate
),
mileage as
(select EmployeeID, AssignmentID, CompanyName, BillRate, sum(BillTotal) Total from SampleTable where wage code = 'Mileage' group by EmployeeID, AssignmentID, CompanyName, BillRate
)
select r.*, o.BillRate, o.Total, m.Total
from regular r
left outer join overtime o
on r.EmployeeID = o.EmployeeID and r.AssignmentID= o.AssignmentID and r.CompanyName= o.CompanyName and r.BillRate= o.BillRateand
left outer join mileage m
on r.EmployeeID = m.EmployeeID and r.AssignmentID= m.AssignmentID and r.CompanyName= m.CompanyName and r.BillRate= m.BillRateand
上面的查询是转述的,可能不起作用。
使用解码和数据透视的某种组合来做到这一点的更好方法是什么?是否可以使用单个数据透视表?
PIVOT: Oracle PIVOT 子句允许您从 Oracle 11g 开始编写交叉表查询。这意味着您可以汇总结果并将行旋转为列。
DECODE: Oracle/PLSQL DECODE 函数具有 IF-THEN-ELSE 语句的功能。
对于您的用例,您可以通过以下方式使用数据透视和解码:
SELECT
EmployeeID, AssignmentID, CompanyName,
decode(REG_BILLRATE, NULL, 0, REG_BILLRATE) AS REG_BILLRATE,
decode(REG_FILL, NULL, 0, REG_FILL) AS REG_FILL,
decode(OT_BILLRATE, NULL, 0, OT_BILLRATE) AS OT_BILLRATE,
decode(OT_FILL, NULL, 0, OT_FILL) AS OT_FILL,
decode(MILEAGE_FILL, NULL, 0, MILEAGE_FILL) AS MILEAGE_FILL
FROM nbitra.tmp
pivot
(
max(BillRate) AS BillRate, sum(BillTotal) AS Fill
for WageCode IN ('Regular' Reg , 'Overtime' OT , 'Mileage' Mileage )
);
注意:代码将空值替换为 0。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句