结合两个 Postgresql 查询

查询量很大。必须有一些方法可以将它们拆分为更小的子查询。至少更好地格式化它们会有所帮助。以下可能有效（没有数据或工作示例，这当然很难测试）：

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) ;