SELECT
CASE
WHEN @mid = t.PAY_TIME THEN
ELSE
END SEQ,
@mid := t.PAY_TIME,
t.AMOUNTS,
t.CHARGE_PRICE,
t.MONEY,
t.PAY_TIME AS CHARGE_TIME,
t.PRODUCT_ID,
t.PRODUCT_NAME,
t.CANRETURNCOUNT,
t.ID
FROM
(
SELECT
bmb.ID,
bmb.PRODUCT_ID,
bmb.PRODUCT_NAME,
bmb.AMOUNTS,
bmb.MONEY,
bmbc.CHARGE_PRICE,
bmpr.PAY_TIME,
(
bmb.AMOUNTS - IFNULL(
(
SELECT
SUM(bmb1.AMOUNTS)
FROM
B_MYGAS_BILLS bmb1
WHERE
bmb1.ID IN (
SELECT
bmbrr.BILLS_RETURN_ID
FROM
B_MYGAS_BILLS_RETURN_RELATION bmbrr
WHERE
bmbrr.BILLS_ID = bmb.ID
)
),
)
) AS CANRETURNCOUNT
FROM
B_MYGAS_BILLS bmb
LEFT JOIN B_MYGAS_BILLS_PAYMENTDETAILS bmbp ON bmbp.BILLS_ID = bmb.ID
LEFT JOIN B_MYGAS_BILLS_CHARGEDETAILS bmbc ON bmbc.BILLS_ID = bmb.ID
LEFT JOIN B_MYGAS_PAY_RECORD bmpr ON bmpr.ID = bmbp.BILLS_PAYMENTID
WHERE
AND bmb.PRODUCT_ID NOT IN (
',
',
',
',
',
',
'
)
AND bmbp.BILLS_PAYMENTID IN (
SELECT
bmpr.ID
FROM
B_MYGAS_PAY_RECORD bmpr
LEFT JOIN B_MYGAS_BIZ_REC bmbr ON bmbr.ID = bmpr.BIZ_REC_ID
WHERE
AND bmpr.SERVICEPOINT_ID = '0220020cf1dc4535b4273fbe60d48503'
'
'
)
) t
) f
ORDER BY
t.PAY_TIME DESC,
SEQ ASC
