因为3.5.5因为不支持:=形式
新建函数
DROP FUNCTION IF EXISTS `getAllRate`;
DELIMITER ;;
CREATE FUNCTION `getAllRate`(batchNo varchar(200) , opId varchar(200), sName varchar(200)) RETURNS varchar(200)
BEGIN
DECLARE allRateReturn decimal(6,3);
set allRateReturn = (
SELECT
allRate
FROM
(
SELECT
max(d.opName) AS opName,
max(d.scrapQty) AS scrapQty,
max(d.scrapName) AS scrapName,
max(d.finishQty) AS finishQty,
max(d.ngRate) AS ngRate,
max(d.batchNo) AS batchNo,
ROUND(sum(e.scrapQty) / max(d.totalScrapQty), 4) * 100 AS allRate
FROM
(
SELECT
@rownum :=@rownum + 1 AS rownum,
a.*, c.*
FROM
(
SELECT
op.OP_NAME AS opName,
sum(s.SCRAP_CELL_QTY) AS scrapQty,
s.SCRAP_NAME AS scrapName,
round(nvl(SUM(o.FINISH_QTY), 0), 0) AS finishQty,
round(sum(s.SCRAP_CELL_QTY) /(sum(s.SCRAP_CELL_QTY) + nvl(SUM(o.FINISH_QTY), 0)), 4) * 100 AS ngRate,
s.BATCH_NO AS batchNo
FROM
tt_wo_batch_scrap s
LEFT JOIN tm_operation op ON op.PK_ID = s.OP_ID
LEFT JOIN tt_wo_order o ON o.BATCH_NO = s.BATCH_NO
AND o.OP_ID = s.OP_ID
WHERE
s.DEL_FLAG = '0'
AND s.BATCH_NO = batchNo
AND s.OP_ID = opId
GROUP BY
s.SCRAP_NAME
ORDER BY
s.SCRAP_CELL_QTY DESC
) a,
(SELECT @rownum := 0) t,
(
SELECT
sum(s.SCRAP_CELL_QTY) AS totalScrapQty
FROM
tt_wo_batch_scrap s
WHERE
s.DEL_FLAG = '0'
AND s.BATCH_NO = batchNo
AND s.OP_ID = opId
) c
) d
LEFT JOIN (
SELECT
@rownum2 :=@rownum2 + 1 AS rownum,
a.*
FROM
(
SELECT
sum(s.SCRAP_CELL_QTY) AS scrapQty
FROM
tt_wo_batch_scrap s
WHERE
s.DEL_FLAG = '0'
AND s.BATCH_NO = batchNo
AND s.OP_ID = opId
GROUP BY
s.SCRAP_NAME
ORDER BY
s.SCRAP_CELL_QTY DESC
) a,
(SELECT @rownum2 := 0) t
) e ON d.rownum >= e.rownum
GROUP BY
d.rownum
ORDER BY
d.rownum ASC
) rt
WHERE
rt.scrapName = sName
);
RETURN ROUND(allRateReturn,4);
END
;;
