Public Shared Function GetPurchaseNumber(Optional ByVal isOverLimit As Boolean = False) As DataTable
Dim strWhere As String = If(isOverLimit, " WHERE (X.OverP1 > 0 Or X.OverP2 > 0)", String.Empty)
Dim x As String = <xsql>
;
WITH cte
AS (
SELECT *
,ROW_NUMBER() OVER (
ORDER BY InputType DESC
,StringNum ASC
) AS rn
FROM (
SELECT InputType
,StringNum
,LimitPrice1
,LimitPrice2
,Y.Price1
,Y.Price2
,ISNULL(LimitPrice1, 0) + ISNULL(Y.Price1, 0) AS OverP1
,ISNULL(LimitPrice2, 0) + ISNULL(Y.Price2, 0) AS OverP2
FROM GoodLuckConfigSpread AS Z
CROSS APPLY (
SELECT SUM(Price1) AS Price1
,SUM(Price2) AS Price2
FROM GoodLuck a
WHERE a.InputType = Z.InputType
AND a.StringNum = Z.StringNum
) Y
) X <%= strWhere %> --{0}
--WHERE X.OverP1 > 0
--OR X.OverP2 > 0
)
,cte0
AS (
SELECT InputType, MIN(rn) AS rn
FROM cte
GROUP BY InputType
)
,cte1
AS (
SELECT cte.InputType
,StringNum
,OverP1
,OverP2
,cte.rn
,Price1
,Price2
FROM cte0
CROSS JOIN cte WHERE cte0.InputType = cte.InputType AND (cte.rn -cte0.rn ) % 5 = 0
)
SELECT cte1.InputType
,cte1.StringNum AS StringNum_1
,cte1.Price1 AS Price1_1
,cte1.Price2 AS Price2_1
,cte1.OverP1 AS OverP1_1
,cte1.OverP2 AS OverP2_1
,cte2.StringNum AS StringNum_2
,cte2.Price1 AS Price1_2
,cte2.Price2 AS Price2_2
,cte2.OverP1 AS OverP1_2
,cte2.OverP2 AS OverP2_2
,cte3.StringNum AS StringNum_3
,cte3.Price1 AS Price1_3
,cte3.Price2 AS Price2_3
,cte3.OverP1 AS OverP1_3
,cte3.OverP2 AS OverP2_3
,cte4.StringNum AS StringNum_4
,cte4.Price1 AS Price1_4
,cte4.Price2 AS Price2_4
,cte4.OverP1 AS OverP1_4
,cte4.OverP2 AS OverP2_4
,cte5.StringNum AS StringNum_5
,cte5.Price1 AS Price1_5
,cte5.Price2 AS Price2_5
,cte5.OverP1 AS OverP1_5
,cte5.OverP2 AS OverP2_5
FROM cte1
LEFT JOIN cte AS cte2 ON cte1.InputType = cte2.InputType
AND cte2.rn - cte1.rn = 1
LEFT JOIN cte AS cte3 ON cte1.InputType = cte3.InputType
AND cte3.rn - cte1.rn = 2
LEFT JOIN cte AS cte4 ON cte1.InputType = cte4.InputType
AND cte4.rn - cte1.rn = 3
LEFT JOIN cte AS cte5 ON cte1.InputType = cte5.InputType
AND cte5.rn - cte1.rn = 4
</xsql>
Return SQLHelperExt.Query(x.ToString())
End Function
[x] สังเกตุว่าผมจะหลีกเลี่ยงการใช้งาน Store Procedure เสมอ (ไม่ใช่ว่าผมเขียนไม่เป็น แต่ผมมีเหตุผลของผม)
Load balance : Server 05
Quote:
