优化以下代码:select a.goodsid, b.goodsname, b.goodsunit, b.goodstype, k.factoryname, h.placepointid, h.placepointname, --b.accflag, sum(a.goodsqty)sl, sum(g.unitprice*a.goodsqty) cb from bms_st_qty_lst a, pub_goods b, gpcs_placepoint h, bms_batch_def g, pub_factory k where a.goodsid=b.goodsid and a.storageid=h.storageid and b.factoryid=k.factoryid and a.batchid=g.batchid and b.accflag=1 and h.retailcenterid=34 and not exists(select 1 from gresa_sa_dtl , gresa_sa_doc where gresa_sa_dtl.rsaid=gresa_sa_doc.rsaid and a.goodsid=gresa_sa_dtl.goodsid and h.placepointid=gresa_sa_doc.placepointid and to_char(gresa_sa_doc.credate,'yyyy-mm-dd')>=${条件.前推日期}) and (h.placepointid in (${条件.门店ID}) or decode('${条件.门店ID}','to_number(null)',null,'-') is null) and (a.goodsid in (${条件.货品ID}) or decode('${条件.货品ID}','to_number(null)',null,'-') is null) group by a.goodsid, b.goodsname, b.goodsunit, b.goodstype, k.factoryname, h.placepointid, h.placepointname --b.accflag order by h.placepointid,a.goodsid
时间: 2024-02-14 12:16:08 浏览: 34
请注意,由于我不知道你的数据结构和表关系,因此我无法完全优化你的代码。但是,以下是一些潜在的优化建议:
1.使用 ANSI SQL join 语法代替旧的 where 子句连接。这将使代码更清晰,易于维护。
2.尝试使用索引来优化查询性能。根据你的表结构和使用情况,可能需要对某些列添加索引。
3.考虑将一些 where 子句移动到 join 条件中。这可能会改善查询性能。
4.使用 with 子句创建临时表,以便在查询中重复使用某些数据。这可以减少查询时间并提高性能。
下面是重写后的代码,但请注意,这只是一个示例,你需要根据你的实际情况进行调整和修改。
```
WITH sales AS (
SELECT DISTINCT
rsaid,
goodsid,
placepointid
FROM gresa_sa_dtl
JOIN gresa_sa_doc ON gresa_sa_dtl.rsaid = gresa_sa_doc.rsaid
WHERE to_char(gresa_sa_doc.credate, 'yyyy-mm-dd') >= ${条件.前推日期}
),
filtered AS (
SELECT
a.goodsid,
b.goodsname,
b.goodsunit,
b.goodstype,
k.factoryname,
h.placepointid,
h.placepointname,
SUM(a.goodsqty) AS sl,
SUM(g.unitprice * a.goodsqty) AS cb
FROM bms_st_qty_lst a
JOIN pub_goods b ON a.goodsid = b.goodsid
JOIN gpcs_placepoint h ON a.storageid = h.storageid
JOIN bms_batch_def g ON a.batchid = g.batchid
JOIN pub_factory k ON b.factoryid = k.factoryid
LEFT JOIN sales s ON a.goodsid = s.goodsid AND h.placepointid = s.placepointid
WHERE b.accflag = 1
AND h.retailcenterid = 34
AND (h.placepointid IN (${条件.门店ID}) OR DECODE('${条件.门店ID}', 'to_number(null)', NULL, '-') IS NULL)
AND (a.goodsid IN (${条件.货品ID}) OR DECODE('${条件.货品ID}', 'to_number(null)', NULL, '-') IS NULL)
AND s.rsaid IS NULL
GROUP BY
a.goodsid,
b.goodsname,
b.goodsunit,
b.goodstype,
k.factoryname,
h.placepointid,
h.placepointname
ORDER BY
h.placepointid,
a.goodsid
)
SELECT *
FROM filtered;
```
相关推荐
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)