帮我把“SELECT ppo.supplier_id, ppo.supplier_name, count(distinct ppo.id) FROM pm_purchase_order ppo inner join pm_purchase_order_detail ppod on ppo.id = ppod.purchase_order_id -- and ppod.confirm_status = '1' and ppod.del_flag = '0' WHERE ppo.del_flag = '0' GROUP BY ppo.supplier_id, ppo.supplier_name”和“SELECT ppo.supplier_id, ppo.supplier_name, count(distinct ppo.id) FROM pm_purchase_order ppo inner join pm_purchase_order_detail ppod on ppo.id = ppod.purchase_order_id and ppod.confirm_status = '1' and ppod.del_flag = '0' WHERE ppo.del_flag = '0' GROUP BY ppo.supplier_id, ppo.supplier_name”合并成一个SQL语句

可以使用`OR`运算符将两个`SELECT`语句合并成一个，同时在第二个`SELECT`语句中添加一个条件判断，当`ppod.confirm_status`不等于1时，不计入结果。合并后的 SQL 语句如下：

SELECT   ppo.supplier_id,   ppo.supplier_name,   count(distinct ppo.id) 
FROM   pm_purchase_order ppo 
inner join pm_purchase_order_detail ppod on ppo.id = ppod.purchase_order_id 
WHERE   ppo.del_flag = '0' AND (ppod.confirm_status = '1' OR ppod.confirm_status <> '1' AND ppod.del_flag = '0')
GROUP BY   ppo.supplier_id,   ppo.supplier_name

注意，这个合并的语句中，`ppod.del_flag = '0'`条件是包含在括号里面的，因为它只对第二个`SELECT`语句生效。如果要对两个`SELECT`语句都生效，需要将这个条件移动到`WHERE`语句的外面，并使用`AND`运算符连接两个条件。

相关问题

SELECT t1.supplier_id,IFNULL(t1.receiptcount,0) as receipt_count,IFNULL(t1.receiptamount,0) as receipt_amount,IFNULL(t2.refundcount,0) as refund_count,IFNULL(t2.refundamount,0) as refund_amount from ( SELECT a.supplier_id, count(DISTINCT a.order_id) AS receiptcount, sum(IFNULL(b.receipt_amount,0))/100 AS receiptamount FROM order_info a left JOIN order_receipt b ON a.order_id = b.order_id WHERE a.order_type IN (70, 75) AND a.is_del = 0 AND a.order_state IN (40, 50, 60) AND a.create_time >= '2023-05-28 00:00:00' AND a.create_time < '2023-05-29 00:00:00' AND a.supplier_id in ( 78,63,58,57,64,72,71,74,83,77,70,69,67,82,65,87,73,59,66,60,86,85,79,80,84,90 ) GROUP BY a.supplier_id order by a.supplier_id ASC ) t1 LEFT JOIN ( select c.supplier_id,count(DISTINCT c.order_id) as refundcount,sum(IFNULL(c.refund_amount,0)) / 100 as refundamount from order_refund c LEFT JOIN order_info d on c.order_id = d.order_id WHERE d.order_type in (70,75) AND d.order_state IN (40, 50, 60) and c.refund_state =13 AND c.is_del = 0 and c.create_time >= '2023-05-28 00:00:00' AND c.create_time < '2023-05-29 00:00:00' AND c.supplier_id in ( 78,63,58,57,64,72,71,74,83,77,70,69,67,82,65,87,73,59,66,60,86,85,79,80,84,90 ) GROUP BY c.supplier_id ) t2 on t1.supplier_id = t2.supplier_id order by t1.supplier_id asc

这是一个比较复杂的 SQL 语句，但是根据您提供的信息，我们可以从以下几个方面来考虑进行优化：

1.使用 EXISTS 子查询替代 LEFT JOIN

在语句中，我们使用了两个 LEFT JOIN 子查询，这在数据量大的情况下可能会影响查询性能。我们可以考虑使用 EXISTS 子查询替代 LEFT JOIN，如下所示：

SELECT t1.supplier_id,
       IFNULL(t1.receiptcount, 0) AS receipt_count,
       IFNULL(t1.receiptamount, 0) AS receipt_amount,
       IFNULL(t2.refundcount, 0) AS refund_count,
       IFNULL(t2.refundamount, 0) AS refund_amount
FROM (SELECT a.supplier_id,
             count(DISTINCT a.order_id) AS receiptcount,
             sum(IFNULL(b.receipt_amount, 0)) / 100 AS receiptamount
      FROM order_info a
      WHERE a.order_type IN (70, 75)
        AND a.is_del = 0
        AND a.order_state IN (40, 50, 60)
        AND a.create_time >= '2023-05-28 00:00:00'
        AND a.create_time < '2023-05-29 00:00:00'
        AND a.supplier_id IN (78, 63, 58, 57, 64, 72, 71, 74, 83, 77, 70, 69, 67, 82, 65, 87, 73, 59, 66, 60, 86, 85, 79, 80, 84, 90)
        AND EXISTS (SELECT 1 FROM order_receipt b WHERE a.order_id = b.order_id)
      GROUP BY a.supplier_id
      ORDER BY a.supplier_id ASC) t1
LEFT JOIN (SELECT c.supplier_id,
                  count(DISTINCT c.order_id) AS refundcount,
                  sum(IFNULL(c.refund_amount, 0)) / 100 AS refundamount
           FROM order_refund c
           WHERE c.refund_state = 13
             AND c.is_del = 0
             AND c.create_time >= '2023-05-28 00:00:00'
             AND c.create_time < '2023-05-29 00:00:00'
             AND c.supplier_id IN (78, 63, 58, 57, 64, 72, 71, 74, 83, 77, 70, 69, 67, 82, 65, 87, 73, 59, 66, 60, 86, 85, 79, 80, 84, 90)
             AND EXISTS (SELECT 1 FROM order_info d WHERE c.order_id = d.order_id AND d.order_type IN (70, 75) AND d.order_state IN (40, 50, 60)))
           GROUP BY c.supplier_id) t2 ON t1.supplier_id = t2.supplier_id
ORDER BY t1.supplier_id ASC;

2.尽可能减少使用 IFNULL 函数

在语句中，我们使用了 IFNULL 函数来处理 NULL 值，这可能会影响查询性能。我们可以尽可能减少使用 IFNULL 函数，改用 COALESCE 函数，如下所示：

SELECT t1.supplier_id,
       COALESCE(t1.receiptcount, 0) AS receipt_count,
       COALESCE(t1.receiptamount, 0) AS receipt_amount,
       COALESCE(t2.refundcount, 0) AS refund_count,
       COALESCE(t2.refundamount, 0) AS refund_amount
FROM (SELECT a.supplier_id,
             count(DISTINCT a.order_id) AS receiptcount,
             sum(COALESCE(b.receipt_amount, 0)) / 100 AS receiptamount
      FROM order_info a
      WHERE a.order_type IN (70, 75)
        AND a.is_del = 0
        AND a.order_state IN (40, 50, 60)
        AND a.create_time >= '2023-05-28 00:00:00'
        AND a.create_time < '2023-05-29 00:00:00'
        AND a.supplier_id IN (78, 63, 58, 57, 64, 72, 71, 74, 83, 77, 70, 69, 67, 82, 65, 87, 73, 59, 66, 60, 86, 85, 79, 80, 84, 90)
        AND EXISTS (SELECT 1 FROM order_receipt b WHERE a.order_id = b.order_id)
      GROUP BY a.supplier_id
      ORDER BY a.supplier_id ASC) t1
LEFT JOIN (SELECT c.supplier_id,
                  count(DISTINCT c.order_id) AS refundcount,
                  sum(COALESCE(c.refund_amount, 0)) / 100 AS refundamount
           FROM order_refund c
           WHERE c.refund_state = 13
             AND c.is_del = 0
             AND c.create_time >= '2023-05-28 00:00:00'
             AND c.create_time < '2023-05-29 00:00:00'
             AND c.supplier_id IN (78, 63, 58, 57, 64, 72, 71, 74, 83, 77, 70, 69, 67, 82, 65, 87, 73, 59, 66, 60, 86, 85, 79, 80, 84, 90)
             AND EXISTS (SELECT 1 FROM order_info d WHERE c.order_id = d.order_id AND d.order_type IN (70, 75) AND d.order_state IN (40, 50, 60)))
           GROUP BY c.supplier_id) t2 ON t1.supplier_id = t2.supplier_id
ORDER BY t1.supplier_id ASC;

3.尽可能减少使用 DISTINCT 关键字

在语句中，我们使用了 DISTINCT 关键字来去重，这可能会影响查询性能。我们可以尽可能减少使用 DISTINCT 关键字，改用 GROUP BY 子句，如下所示：

SELECT t1.supplier_id,
       COALESCE(t1.receiptcount, 0) AS receipt_count,
       COALESCE(t1.receiptamount, 0) AS receipt_amount,
       COALESCE(t2.refundcount, 0) AS refund_count,
       COALESCE(t2.refundamount, 0) AS refund_amount
FROM (SELECT a.supplier_id,
             COUNT(a.order_id) AS receiptcount,
             SUM(COALESCE(b.receipt_amount, 0)) / 100 AS receiptamount
      FROM order_info a
           LEFT JOIN order_receipt b ON a.order_id = b.order_id
      WHERE a.order_type IN (70, 75)
        AND a.is_del = 0
        AND a.order_state IN (40, 50, 60)
        AND a.create_time >= '2023-05-28 00:00:00'
        AND a.create_time < '2023-05-29 00:00:00'
        AND a.supplier_id IN (78, 63, 58, 57, 64, 72, 71, 74, 83, 77, 70, 69, 67, 82, 65, 87, 73, 59, 66, 60, 86, 85, 79, 80, 84, 90)
      GROUP BY a.supplier_id
      ORDER BY a.supplier_id ASC) t1
LEFT JOIN (SELECT c.supplier_id,
                  COUNT(c.order_id) AS refundcount,
                  SUM(COALESCE(c.refund_amount, 0)) / 100 AS refundamount
           FROM order_refund c
                LEFT JOIN order_info d ON c.order_id = d.order_id
           WHERE c.refund_state = 13
             AND c.is_del = 0
             AND c.create_time >= '2023-05-28 00:00:00'
             AND c.create_time < '2023-05-29 00:00:00'
             AND c.supplier_id IN (78, 63, 58, 57, 64, 72, 71, 74, 83, 77, 70, 69, 67, 82, 65, 87, 73, 59, 66, 60, 86, 85, 79, 80, 84, 90)
             AND d.order_type IN (70, 75)
             AND d.order_state IN (40, 50, 60)
           GROUP BY c.supplier_id) t2 ON t1.supplier_id = t2.supplier_id
ORDER BY t1.supplier_id ASC;

以上是三个简单的优化建议，具体优化方案需要根据实际情况进行调整。

SELECT t1.supplier_id, t1.ky_count, t1.ky_amount, IFNULL(t2.ky_refund_count,0) as ky_refund_count, IFNULL(t2.ky_refund_amount,0) as ky_refund_amount FROM ( SELECT a.supplier_id, count( DISTINCT c.order_no ) AS ky_count, SUM( IFNULL( c.record_amount, 0 )) / 100 AS ky_amount FROM settle_order a INNER JOIN settle_order_receipt b ON a.order_id = b.order_id INNER JOIN cash_withdrawal_record c ON b.third_trade_no = c.order_no WHERE a.is_del = 0 AND a.order_time >= '2023-05-28 00:00:00' AND a.order_time < '2023-05-29 00:00:00' AND a.order_type in (70,75) AND a.supplier_id IN (78,63,58,57,64,72,71,74,83,77,70,69,67,82,65,87,73,59,66,60,86,85,79,80,84,90) AND b.channel_code = 61 AND c.con_bank_account_no IN ( 247, 325 ) AND c.record_status = 1 AND c.record_time > '2023-05-01 00:00:00' GROUP BY a.supplier_id ) t1 LEFT JOIN ( SELECT a.supplier_id, count( DISTINCT b.order_no ) AS ky_refund_count, SUM( IFNULL( b.record_amount, 0 )) / 100 AS ky_refund_amount FROM settle_order_refund a INNER JOIN cash_withdrawal_record b ON a.third_refund_id = b.order_no WHERE a.is_del = 0 AND a.order_type in (70,75) AND a.apply_time >= '2023-05-28 00:00:00' AND a.apply_time < '2023-05-29 00:00:00' AND a.supplier_id IN (78,63,58,57,64,72,71,74,83,77,70,69,67,82,65,87,73,59,66,60,86,85,79,80,84,90) AND a.channel_code = 61 AND b.con_bank_account_no IN ( 247, 325 ) AND b.record_status = 5 AND b.record_time > '2023-05-01 00:00:00' GROUP BY a.supplier_id ) t2 ON t1.supplier_id = t2.supplier_id order by t1.supplier_id asc

以下是对该 SQL 语句的一些优化建议：

1. 使用 EXISTS 替代 DISTINCT + JOIN

在子查询中，使用了 DISTINCT 关键词去重，然后使用 JOIN 连接表。这样的方式效率较低，可以考虑使用 EXISTS 替代。具体来说，应该将连接条件改为 EXISTS 子查询的 WHERE 子句，如下所示：

SELECT
    a.supplier_id,
    COUNT(DISTINCT c.order_no) AS ky_count,
    SUM(IFNULL(c.record_amount, 0)) / 100 AS ky_amount
FROM
    settle_order AS a
    INNER JOIN settle_order_receipt AS b ON a.order_id = b.order_id
    INNER JOIN cash_withdrawal_record AS c ON EXISTS (
        SELECT 1
        FROM cash_withdrawal_record AS d
        WHERE b.third_trade_no = d.order_no
            AND d.con_bank_account_no IN (247, 325)
            AND d.record_status = 1
            AND d.record_time > '2023-05-01 00:00:00'
    )
WHERE
    a.is_del = 0
    AND a.order_time >= '2023-05-28 00:00:00'
    AND a.order_time < '2023-05-29 00:00:00'
    AND a.order_type IN (70, 75)
    AND a.supplier_id IN (78, 63, 58, 57, 64, 72, 71, 74, 83, 77, 70, 69, 67, 82, 65, 87, 73, 59, 66, 60, 86, 85, 79, 80, 84, 90)
    AND b.channel_code = 61
GROUP BY
    a.supplier_id

同样的，对于另一个子查询也可以使用 EXISTS 进行优化。

2. 使用 UNION ALL 代替 LEFT JOIN

在原 SQL 中，使用了 LEFT JOIN 来将两个子查询合并，可以考虑使用 UNION ALL 代替。使用 UNION ALL 的好处是它不会去重，而我们已经在子查询中使用了 GROUP BY 进行了去重，因此可以放心使用 UNION ALL。

SELECT
    t.supplier_id,
    t.ky_count,
    t.ky_amount,
    t.ky_refund_count,
    t.ky_refund_amount
FROM (
    SELECT
        a.supplier_id,
        COUNT(DISTINCT c.order_no) AS ky_count,
        SUM(IFNULL(c.record_amount, 0)) / 100 AS ky_amount,
        0 AS ky_refund_count,
        0 AS ky_refund_amount
    FROM
        settle_order AS a
        INNER JOIN settle_order_receipt AS b ON a.order_id = b.order_id
        INNER JOIN cash_withdrawal_record AS c ON EXISTS (
            SELECT 1
            FROM cash_withdrawal_record AS d
            WHERE b.third_trade_no = d.order_no
                AND d.con_bank_account_no IN (247, 325)
                AND d.record_status = 1
                AND d.record_time > '2023-05-01 00:00:00'
        )
    WHERE
        a.is_del = 0
        AND a.order_time >= '2023-05-28 00:00:00'
        AND a.order_time < '2023-05-29 00:00:00'
        AND a.order_type IN (70, 75)
        AND a.supplier_id IN (78, 63, 58, 57, 64, 72, 71, 74, 83, 77, 70, 69, 67, 82, 65, 87, 73, 59, 66, 60, 86, 85, 79, 80, 84, 90)
        AND b.channel_code = 61
    GROUP BY
        a.supplier_id
    UNION ALL
    SELECT
        a.supplier_id,
        0 AS ky_count,
        0 AS ky_amount,
        COUNT(DISTINCT b.order_no) AS ky_refund_count,
        SUM(IFNULL(b.record_amount, 0)) / 100 AS ky_refund_amount
    FROM
        settle_order_refund AS a
        INNER JOIN cash_withdrawal_record AS b ON EXISTS (
            SELECT 1
            FROM settle_order_refund AS c
            WHERE c.third_refund_id = b.order_no
                AND c.is_del = 0
                AND c.order_type IN (70, 75)
                AND c.apply_time >= '2023-05-28 00:00:00'
                AND c.apply_time < '2023-05-29 00:00:00'
                AND c.supplier_id IN (78, 63, 58, 57, 64, 72, 71, 74, 83, 77, 70, 69, 67, 82, 65, 87, 73, 59, 66, 60, 86, 85, 79, 80, 84, 90)
                AND c.channel_code = 61
        )
    WHERE
        b.con_bank_account_no IN (247, 325)
        AND b.record_status = 5
        AND b.record_time > '2023-05-01 00:00:00'
    GROUP BY
        a.supplier_id
) AS t
ORDER BY
    t.supplier_id ASC

3. 使用索引优化查询

在子查询中，涉及到多个表的连表查询，需要使用到合适的索引才能提高查询效率。具体来说，可以考虑在以下字段上建立索引：

- settle_order.is_del
- settle_order.order_time
- settle_order.order_type
- settle_order.supplier_id
- settle_order_receipt.order_id
- settle_order_receipt.channel_code
- settle_order_receipt.third_trade_no
- cash_withdrawal_record.order_no
- cash_withdrawal_record.con_bank_account_no
- cash_withdrawal_record.record_status
- cash_withdrawal_record.record_time
- settle_order_refund.is_del
- settle_order_refund.order_type
- settle_order_refund.apply_time
- settle_order_refund.supplier_id
- settle_order_refund.channel_code
- cash_withdrawal_record.order_no
- cash_withdrawal_record.con_bank_account_no
- cash_withdrawal_record.record_status
- cash_withdrawal_record.record_time

以上是一些可能的优化建议，具体的优化方案需要根据实际情况进行调整。