我有这个查询，根据驱动程序编号返回驱动程序工作的天数。我检查工作天数的方式只是计算不同的 order_date 及其驱动程序编号。让我们称之为天工作：
SELECT driver_no, count(distinct order_date)
FROM orders o INNER JOIN order_settlements os ON o.control_no =
os.control_no
WHERE os.company_no = '001' and o.service_type not in (17, 30, 31, 34, 35,
90, 94, 96, 97, 98, 99) and customer_reference != 'PARCEL ADJUSTMENT' and
order_date between (date '2017-6-11' - integer '7') and '2017-6-11' and
posting_status <> '9' and settlement_period_end_date is null
GROUP BY driver_no
我有这个查询来计算司机赚了多少钱，他交付了多少，等等。让我们称之为主要：
SELECT Driver_Number, Driver_Name, Branch, Driver_Type, sum(Revenue) AS Revenue, sum(Booking) as Booking, CASE WHEN round(sum(Support_Pay * Settlement_Per/100), 2) != 0 THEN round(sum(Support_Pay * Settlement_Per/100), 2) END as Support_Pay, round(sum(fuel * Settlement_Per/100), 2) as Fuel, round(sum(Booking * Settlement_Per/100), 2) as Settlement, sum(Stops) As Stops, sum(Pieces) As Pieces
FROM
( SELECT os.driver_no as Driver_Number, d.driver_name as Driver_Name, d.report_sort_key as Branch, (CASE WHEN d.driver_type = '0' THEN 'Contractor' WHEN d.driver_type = '1' THEN 'Employee' END) as Driver_Type,
sum(o.rate_bucket1+o.rate_bucket2+o.rate_bucket3+o.rate_bucket4+o.rate_bucket5+o.rate_bucket6+
o.rate_bucket7+o.rate_bucket8+o.rate_bucket9+o.rate_bucket10+o.rate_bucket11) as Revenue,
sum(os.charge1+os.charge2+os.charge3+os.charge4+os.charge5+os.charge6) as Booking, CASE WHEN (o.service_type = '35') THEN sum(os.charge1+os.charge2+os.charge3+os.charge4+os.charge5+os.charge6) END AS Support_Pay, CASE WHEN (o.service_type = '34') THEN sum(os.charge1+os.charge2+os.charge3+os.charge4+os.charge5+os.charge6) END AS Fuel,
os.settlement_percent as Settlement_Per, CASE WHEN (o.service_type != '17' or o.service_type != '30' or o.service_type != '31' or o.service_type != '34' or o.service_type != '35' or o.service_type != '90' or o.service_type != '94' or o.service_type != '96' or o.service_type != '97' or o.service_type != '98' or o.service_type != '99') THEN count(os.control_no) END as Stops, CASE WHEN (o.service_type != '17' or o.service_type != '30' or o.service_type != '31' or o.service_type != '34' or o.service_type != '35' or o.service_type != '90' or o.service_type != '94' or o.service_type != '96' or o.service_type != '97' or o.service_type != '98' or o.service_type != '99') THEN sum(o.pieces) END as Pieces
FROM
orders o INNER JOIN order_settlements os ON o.control_no = os.control_no INNER JOIN drivers d ON os.driver_no = d.driver_no
WHERE
d.company_no = '001' and
order_date BETWEEN '2017-4-9' AND '2017-6-11' AND
os.company_no = '001' and o.company_no = '001' AND posting_status <> '9' AND
settlement_period_end_date is NULL AND os.driver_no is not null and os.driver_no !=0 and d.driver_no between '1' and '7999'
GROUP BY
o.service_type, order_date, Settlement_Per, o.customer_no, os.driver_no, d.driver_name, d.driver_type, d.report_sort_key) Sub
GROUP BY
Branch, Driver_Number, Driver_Name, Driver_Type
ORDER BY
Driver_Number
现在我想将工作天数作为主查询中的一列，但我需要保持日期范围相同（工作天数应该只回顾上周，而主查询需要回顾几个月对于任何追溯输入的订单）。
我尝试将工作查询的天数放入主查询末尾的“CASE WHEN”语句中，它返回了不正确的数据（我认为它排除了这些服务类型注册而不是仅仅传递它们的任何天数）。我尝试在 Main 查询的末尾放置一个内部 select 语句，它将从 days_worked 匹配 driver_no 到主查询，并且它需要很长时间才能运行。我尝试在主“FROM”语句中创建两个不同的查询，一个查看 Sub，另一个查看 days_worked，它返回的记录太多，根本不是我想要的。
将此信息返回到一个查询中的最佳方法是什么？顺便说一下，这是在 Postgresql 8.1 上。感谢你的帮助。
查询量很大。必须有一些方法可以将它们拆分为更小的子查询。至少更好地格式化它们会有所帮助。以下可能有效（没有数据或工作示例，这当然很难测试）：
SELECT a.*, b.days_worked FROM (
SELECT driver_number,
driver_name,
branch,
driver_type,
SUM(revenue) AS Revenue,
SUM(booking) AS Booking,
CASE
WHEN Round(SUM(support_pay * settlement_per / 100), 2) != 0 THEN
Round(SUM(support_pay * settlement_per / 100), 2)
END AS Support_Pay,
Round(SUM(fuel * settlement_per / 100), 2) AS Fuel,
Round(SUM(booking * settlement_per / 100), 2) AS Settlement,
SUM(stops) AS Stops,
SUM(pieces) AS Pieces
FROM (SELECT os.driver_no AS Driver_Number,
d.driver_name AS Driver_Name,
d.report_sort_key AS Branch,
( CASE
WHEN d.driver_type = '0' THEN 'Contractor'
WHEN d.driver_type = '1' THEN 'Employee'
END ) AS Driver_Type,
SUM(o.rate_bucket1 + o.rate_bucket2
+ o.rate_bucket3 + o.rate_bucket4
+ o.rate_bucket5 + o.rate_bucket6
+ o.rate_bucket7 + o.rate_bucket8
+ o.rate_bucket9 + o.rate_bucket10
+ o.rate_bucket11) AS Revenue,
SUM(os.charge1 + os.charge2 + os.charge3 + os.charge4
+ os.charge5 + os.charge6) AS Booking,
CASE
WHEN ( o.service_type = '35' ) THEN SUM(
os.charge1 + os.charge2 + os.charge3 + os.charge4
+ os.charge5 + os.charge6)
END AS Support_Pay,
CASE
WHEN ( o.service_type = '34' ) THEN SUM(
os.charge1 + os.charge2 + os.charge3 + os.charge4
+ os.charge5 + os.charge6)
END AS Fuel,
os.settlement_percent AS Settlement_Per,
CASE
WHEN ( o.service_type != '17'
OR o.service_type != '30'
OR o.service_type != '31'
OR o.service_type != '34'
OR o.service_type != '35'
OR o.service_type != '90'
OR o.service_type != '94'
OR o.service_type != '96'
OR o.service_type != '97'
OR o.service_type != '98'
OR o.service_type != '99' ) THEN Count(os.control_no)
END AS Stops,
CASE
WHEN ( o.service_type != '17'
OR o.service_type != '30'
OR o.service_type != '31'
OR o.service_type != '34'
OR o.service_type != '35'
OR o.service_type != '90'
OR o.service_type != '94'
OR o.service_type != '96'
OR o.service_type != '97'
OR o.service_type != '98'
OR o.service_type != '99' ) THEN SUM(o.pieces)
END AS Pieces
FROM orders o
inner join order_settlements os
ON o.control_no = os.control_no
inner join drivers d
ON os.driver_no = d.driver_no
WHERE d.company_no = '001'
AND order_date BETWEEN '2017-4-9' AND '2017-6-11'
AND os.company_no = '001'
AND o.company_no = '001'
AND posting_status <> '9'
AND settlement_period_end_date IS NULL
AND os.driver_no IS NOT NULL
AND os.driver_no != 0
AND d.driver_no BETWEEN '1' AND '7999'
GROUP BY o.service_type,
order_date,
settlement_per,
o.customer_no,
os.driver_no,
d.driver_name,
d.driver_type,
d.report_sort_key) Sub
GROUP BY branch,
driver_number,
driver_name,
driver_type
ORDER BY driver_number
) a JOIN (
SELECT driver_no,
Count(DISTINCT order_date) as days_worked
FROM orders o
inner join order_settlements os
ON o.control_no = os.control_no
WHERE os.company_no = '001'
AND o.service_type NOT IN (17,
30,
31,
34,
35,
90,
94,
96,
97,
98,
99)
AND customer_reference != 'PARCEL ADJUSTMENT'
AND order_date BETWEEN (DATE '2017-6-11' - INTEGER '7') AND '2017-6-11'
AND posting_status <> '9'
AND settlement_period_end_date IS NULL
GROUP BY driver_no
) b ON (a.driver_number = b.driver_no) ;
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句