58.rar_5173_
时间: 2023-08-27 11:02:30 浏览: 52
58.rar_5173_是一个文件名,其中的58.rar表示这是一个以.rar格式压缩的文件,而5173可能是文件的特定标识或命名规则。.rar是一种常见的压缩文件格式,可以将多个文件或文件夹压缩成一个单独的文件,以便于传输、存储或备份。压缩文件能够减小文件的大小,便于在网络上传输,也方便用户在本地保存多个文件。5173可能是一个特定的编号,用来标识该压缩文件的用途、来源或其他相关信息。通过解压缩软件,我们可以解压并访问压缩文件中的内容。压缩文件的具体使用方法和内容,需要根据具体情况进行判断和操作。总之,58.rar_5173_是一个带有特定命名规则的压缩文件,具体的用途和内容需要进一步分析和了解。
相关问题
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
以上是一些可能的优化建议,具体的优化方案需要根据实际情况进行调整。
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;
```
以上是三个简单的优化建议,具体优化方案需要根据实际情况进行调整。