| 背景： 1、使用了道讯的分销系统 2、在道讯系统中有合同，入库，销售数据 3、需要统计某季节产品SKU消化率 4、数据库中存放了近10年零售数据 遇到的问题： 使用下面的查询语句能够满足我的要求，但速度太慢，原来刚设计的时候需要1-2分钟，现在就直接不出来结果了。 查询的思路： 1、选出某时间段的合同中产品明细 2、选出某时间段的到货产品明细 3、合并上述产品明细，作为要查询消化率的产品 4、根据入库情况，统计上述产品的第一批到货时间 5、统计汇总商品到货后某区间段的销售数据，退货数据等 6、再从产品汇总到SKU（取消产品的尺寸等信息）的合同数量、到货数量、销售数量、退货数量 7、返回结果通过报表展示。 下面是FR的查询语句 SELECT MIN(cp.PROD_PROP) AS prop, cp.sku, MIN(cp.PROD_NAME) AS name, MIN(mx.cgsj) AS rktime, MIN(cp.STD_UNIT_PRICE) AS price, MIN(cp.MODEL) AS MD, MIN(cp.SEQ_NUM) AS p_price, SUM(ISNULL(mx.phtsl, 0)) AS ht_qty, SUM( ISNULL(kc.kc, 0)-ISNULL(mx.zl, 0)) AS qc, SUM(ISNULL(kc.kc, 0)) AS jskc, SUM(ISNULL(mx.cgl, 0)) AS cg_qty, SUM(ISNULL(mx.thl, 0)) AS th_qty, SUM(ISNULL(mx.xsl, 0)) AS xs_qty FROM (SELECT CASE WHEN ls.PROD_ID IS NULL THEN [order].PROD_ID ELSE ls.prod_id END AS prod_id, [order].phtsl, ls.zl, ls.cgl, ls.thl, ls.xsl, ls.cgsj FROM (SELECT t1.PROD_ID, SUM(t1.TRAN_QTY) AS zl, SUM(CASE WHEN (r1.rcv_mode = 'purc' AND t1.tran_time BETWEEN CONVERT(DATETIME, '${c_rq1} 00:00:00', 102) AND CONVERT(DATETIME, '${c_rq2} 23:59:50', 102)) THEN t1.TRAN_QTY ELSE 0 END) AS cgl, SUM(CASE WHEN (d1.deliv_mode = 'pucr' AND t1.tran_time BETWEEN CONVERT(DATETIME, '${x_rq1} 00:00:00', 102) AND CONVERT(DATETIME, '${x_rq2} 23:59:50', 102)) THEN t1.TRAN_QTY ELSE 0 END) AS thl, SUM(CASE WHEN ((d1.deliv_mode = 'retl' OR d1.deliv_mode = 'retl') AND t1.tran_time BETWEEN CONVERT(DATETIME, '${x_rq1} 00:00:00', 102) AND CONVERT(DATETIME, '${x_rq2} 23:59:50', 102)) THEN t1.TRAN_QTY ELSE 0 END) AS xsl, MIN(cg.FST_time) AS cgsj FROM WAREH_TRAN AS t1 INNER JOIN (SELECT CGCP.PROD_ID, CGCP.FST_time FROM (SELECT PROD_ID, MIN(RCV_TIME) AS FST_time FROM (SELECT w1.WAREH_ID, w1.PROD_ID, w1.TRAN_QTY, w1.DOC_TYPE, grn1.RCV_MODE, grn1.RCV_TIME FROM WAREH_TRAN AS w1 INNER JOIN GRN AS grn1 ON w1.DOC_NUM = grn1.GRN_NUM AND w1.DOC_UNIT_ID = grn1.UNIT_ID WHERE (w1.DOC_TYPE = 'grn') AND (grn1.RCV_MODE = 'purc') AND (grn1.RCV_TIME BETWEEN CONVERT(DATETIME, '${c_rq1} 00:00:00', 102) AND CONVERT(DATETIME, '${c_rq2} 23:59:50', 102))) AS cgmx GROUP BY PROD_ID) AS CGCP INNER JOIN cp AS CPFL ON CGCP.PROD_ID = CPFL.PROD_ID WHERE (CPFL.PROD_PROP < '05')) AS cg ON t1.PROD_ID = cg.PROD_ID AND t1.TRAN_TIME >= cg.FST_time LEFT OUTER JOIN GRN AS r1 ON t1.DOC_NUM = r1.GRN_NUM LEFT OUTER JOIN GDN AS d1 ON t1.DOC_NUM = d1.GDN_NUM WHERE (t1.doc_type='grn' and ((r1.RCV_MODE = 'purc') OR (r1.RCV_MODE = 'adjs') OR (r1.RCV_MODE = 'othr'))) OR (t1.doc_type='gdn'and ( (d1.DELIV_MODE = 'pucr') OR (d1.DELIV_MODE = 'retl') OR (d1.DELIV_MODE = 'disl') OR (d1.DELIV_MODE = 'adjs') OR (d1.DELIV_MODE = 'othr'))) GROUP BY t1.PROD_ID) AS ls FULL OUTER JOIN (SELECT PUC_DTL.PROD_ID, SUM(PUC_DTL.CNTR_QTY) AS phtsl FROM PUC_DTL INNER JOIN (SELECT DISTINCT PUC_NUM FROM PUO WHERE (DOC_DATE BETWEEN CONVERT(DATETIME, '${o_rq1} 00:00:00', 102) AND CONVERT(DATETIME, '${o_rq2} 23:59:50', 102)) AND (PUC_NUM IS NOT NULL)) AS ht ON PUC_DTL.PUC_NUM = ht.PUC_NUM GROUP BY PUC_DTL.PROD_ID) AS [order] ON ls.PROD_ID = [order].PROD_ID) AS mx LEFT OUTER JOIN (SELECT PROD_ID, SUM(STK_ON_HAND + QTY_IN_TRAN) AS kc FROM WAREH_STK GROUP BY PROD_ID) AS kc ON mx.prod_id = kc.PROD_ID LEFT OUTER JOIN cp ON mx.prod_id = cp.PROD_ID GROUP BY cp.sku ORDER BY prop, ht_qty DESC |
