$order_no $apple_receipt $orderid $productid PHP判断这4个参数不能为空

可以使用以下代码来判断这四个参数是否为空：

if (empty($order_no) || empty($apple_receipt) || empty($orderid) || empty($productid)) {
    // 有参数为空，执行相应的操作
} else {
    // 所有参数均不为空，执行相应的操作
}

其中，`empty()`函数可以判断变量是否为空，如果为空返回`true`，否则返回`false`。

相关问题

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

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

优化以下SQL select pao.id, pao.order_no, pao.apply_time, pao.purchase_user_id, pao.purchase_user_name, pao.apply_user_id, pao.apply_user_name, pao.apply_department_id, pao.apply_department_name, pao.apply_end_time, pao.create_user_id, pao.create_user_name, pao.approve_type, pao.approve_user_id, pao.approve_user_name, pao.approve_time, pao.description, pao.order_type, pao.purchase_type, pao.storage_type, pao.compose_order_no, pao.company_id, pao.delete, pao.create_time, pao.update_time, pao.supplier_id, pao.image_path, pao.contract_id, pao.status, pao.invoice_signer_name, pao.total_amount, pao.total_amount_tax, pao.purchase_status, pao.cancel_reason, pao.print_status, pao.demand_id, pao.arrival_status, pao.supervise_num, pao.supervise_date, pao.merge_apply_id, pao.deadline, pao.remind , s.name as supplierName, paod.amount, cm.return_status as returnStatus, cm.inventory_status as inventoryStatus, cm.stock_remark, cm.merge_flag, cm.signature_file, cm.department_pass, cm.receipt_file, cm.amount_paid, cm.amount_unpaid, cm.contract_name, cm.status as contractStatus, cm.contract_no, cm.contract_amount, paod.product_name, cm.advance_payment, cm.advance_ratio, cm.currency_unit from purchase_apply_order pao left join supplier s on pao.supplier_id = s.id left join ( SELECT GROUP_CONCAT(distinct p.product_name) product_name, sum(IFNULL(amount_tax, 0)) amount, apply_order_no from purchase_apply_order_details pa left join product p on p.pn_code = pa.product_code where p.company_id = 29 GROUP BY apply_order_no ) paod on paod.apply_order_no = pao.order_no left join contract_management cm on pao.contract_id = cm.id where pao.delete = 0 and pao.company_id = 29 and deadline <= '2023-05-25 15:34:00.01' and remind = 0 and arrival_status in( 0 , 1 ) order by pao.create_time desc;

首先，我们可以对该 SQL 查询语句进行简化，只选择需要的字段而不是全部选择。其次，我们可以尝试添加索引以加快查询速度。

以下是优化后的 SQL 查询语句：

SELECT pao.id, pao.order_no, pao.apply_time, pao.purchase_user_id, pao.purchase_user_name, pao.apply_user_id, 
       pao.apply_user_name, pao.apply_department_id, pao.apply_department_name, pao.apply_end_time, pao.create_user_id, 
       pao.create_user_name, pao.approve_type, pao.approve_user_id, pao.approve_user_name, pao.approve_time, 
       pao.description, pao.order_type, pao.purchase_type, pao.storage_type, pao.compose_order_no, pao.company_id, 
       pao.delete, pao.create_time, pao.update_time, pao.supplier_id, pao.image_path, pao.contract_id, pao.status, 
       pao.invoice_signer_name, pao.total_amount, pao.total_amount_tax, pao.purchase_status, pao.cancel_reason, 
       pao.print_status, pao.demand_id, pao.arrival_status, pao.supervise_num, pao.supervise_date, pao.merge_apply_id, 
       pao.deadline, pao.remind, s.name AS supplierName, paod.amount, cm.return_status AS returnStatus, 
       cm.inventory_status AS inventoryStatus, cm.stock_remark, cm.merge_flag, cm.signature_file, cm.department_pass, 
       cm.receipt_file, cm.amount_paid, cm.amount_unpaid, cm.contract_name, cm.status AS contractStatus, 
       cm.contract_no, cm.contract_amount, paod.product_name, cm.advance_payment, cm.advance_ratio, cm.currency_unit
FROM purchase_apply_order pao 
LEFT JOIN supplier s ON pao.supplier_id = s.id 
LEFT JOIN (
    SELECT GROUP_CONCAT(DISTINCT p.product_name) product_name, SUM(IFNULL(amount_tax, 0)) amount, apply_order_no 
    FROM purchase_apply_order_details pa 
    LEFT JOIN product p ON p.pn_code = pa.product_code 
    WHERE p.company_id = 29 
    GROUP BY apply_order_no 
) paod ON paod.apply_order_no = pao.order_no 
LEFT JOIN contract_management cm ON pao.contract_id = cm.id 
WHERE pao.delete = 0 
  AND pao.company_id = 29 
  AND deadline <= '2023-05-25 15:34:00.01' 
  AND remind = 0 
  AND arrival_status IN (0, 1) 
ORDER BY pao.create_time DESC;

我们可以为 `purchase_apply_order` 表的 `company_id` 字段和 `deadline` 字段以及 `remind` 字段创建索引。同时，也可以为 `arrival_status` 字段和 `delete` 字段创建索引。这可以大大加快查询速度。

CREATE INDEX idx_purchase_apply_order_company_id_deadline_remind ON purchase_apply_order (company_id, deadline, remind);

CREATE INDEX idx_purchase_apply_order_arrival_status_delete ON purchase_apply_order (arrival_status, delete);