相同sql，报表渲染出现确实情况 #4560
Description
报表设置
数据集是
sql 语句
SELECT
base.日期 日期,
'钱包支付' AS 分组标题,
COALESCE(cug.group_name, '未分组') 分组名,
COALESCE(
SUM(
SUM(COALESCE(a.微信支付, 0) - COALESCE(b.微信支付退款, 0))
) OVER (PARTITION BY base.日期),
0
) / 100 AS 微信支付,
COALESCE(SUM(COALESCE(a.钱包支付, 0) - COALESCE(b.钱包支付退款, 0)), 0) / 100 AS 钱包支付,
COALESCE(SUM(SUM(COALESCE(a.钱包支付, 0) - COALESCE(b.钱包支付退款, 0))) OVER (PARTITION BY base.日期), 0) / 100 AS 总钱包支付,
COALESCE(SUM(SUM(COALESCE(a.餐补, 0))) OVER (PARTITION BY base.日期), 0) / 100 AS 餐补,
COALESCE(
SUM(SUM(
(COALESCE(a.微信支付, 0) - COALESCE(b.微信支付退款, 0)) +
(COALESCE(a.钱包支付, 0) - COALESCE(b.钱包支付退款, 0)) +
COALESCE(a.餐补, 0)
)) OVER (PARTITION BY base.日期),
0
) / 100 AS 总计,
COALESCE(SUM(SUM(COALESCE(c.充值金额, 0) - COALESCE(b.总充值退款, 0))) OVER (PARTITION BY base.日期), 0) / 100 AS 预存伙食费
FROM
(
SELECT
CASE
WHEN o1.payment_order_type = 'reservation' THEN ov.reservation_date
WHEN o1.payment_order_type = 'report_meal' THEN orm.report_date
ELSE DATE(o1.deduction_time)
END AS 日期,
COALESCE(o1.payer_group_id, 0) AS 分组ID
FROM order_payment o1
LEFT JOIN order_reservation ov ON o1.id = ov.order_payment_id
LEFT JOIN order_report_meal orm ON o1.id = orm.order_payment_id
WHERE o1.order_status = 'ORDER_SUCCESS'
AND o1.company_id = 1557 AND o1.organization_id IN (${DaoFormat.inNumber('${organization_ids}')})
AND (
(o1.payment_order_type = 'reservation' AND ov.reservation_date BETWEEN '${start_pay_time}' AND '${end_pay_time}')
OR (o1.payment_order_type = 'report_meal' AND orm.report_date BETWEEN '${start_pay_time}' AND '${end_pay_time}')
OR (o1.payment_order_type NOT IN ('reservation', 'report_meal') AND o1.deduction_time BETWEEN STR_TO_DATE('${start_pay_time}', '%Y-%m-%d') AND CONCAT('${end_pay_time}', ' 23:59:59'))
)
UNION
SELECT DATE(finish_time) AS 日期, COALESCE(payer_group_id, 0) AS 分组ID
FROM `order_refund`
WHERE `order_status` = 'ORDER_SUCCESS' AND `company_id` = 1557 AND `organization_id` IN (${DaoFormat.inNumber('${organization_ids}')})
AND finish_time BETWEEN STR_TO_DATE('${start_pay_time}', '%Y-%m-%d') AND CONCAT('${end_pay_time}', ' 23:59:59')
UNION
SELECT DATE(finish_time) AS 日期, COALESCE(payer_group_id, 0) AS 分组ID
FROM `order_charge`
WHERE `order_status` = 'ORDER_SUCCESS' AND `company_id` = 1557 AND `organization_id` IN (${DaoFormat.inNumber('${organization_ids}')})
AND finish_time BETWEEN STR_TO_DATE('${start_pay_time}', '%Y-%m-%d') AND CONCAT('${end_pay_time}', ' 23:59:59')
) base
LEFT JOIN (
SELECT
CASE
WHEN o1.payment_order_type = 'reservation' THEN ov.reservation_date
WHEN o1.payment_order_type = 'report_meal' THEN orm.report_date
ELSE DATE(o1.deduction_time)
END AS 日期,
SUM(IF(p.payway = 'WechatPay', o1.pay_fee, 0)) AS 微信支付,
SUM(IF(p.payway = 'PushiPay', o1.pay_fee, 0)) AS 钱包支付,
SUM(o1.pay_fee) AS 总消费,
SUM(CASE WHEN o1.refund_fee = o1.pay_fee AND o1.pay_fee != 0 THEN 0 ELSE COALESCE(o1.food_subsidy_fee, 0) END) AS 餐补,
COALESCE(o1.payer_group_id, 0) AS 分组ID
FROM
order_payment o1
LEFT JOIN payinfo p ON o1.payinfo_id = p.id
LEFT JOIN order_reservation ov ON o1.id = ov.order_payment_id
LEFT JOIN order_report_meal orm ON o1.id = orm.order_payment_id
WHERE
o1.order_status = 'ORDER_SUCCESS'
AND o1.company_id = 1557
AND o1.organization_id IN (${DaoFormat.inNumber('${organization_ids}')})
AND (
(o1.payment_order_type = 'reservation' AND ov.reservation_date BETWEEN '${start_pay_time}' AND '${end_pay_time}')
OR (o1.payment_order_type = 'report_meal' AND orm.report_date BETWEEN '${start_pay_time}' AND '${end_pay_time}')
OR (o1.payment_order_type NOT IN ('reservation', 'report_meal') AND o1.deduction_time BETWEEN STR_TO_DATE('${start_pay_time}', '%Y-%m-%d') AND CONCAT('${end_pay_time}', ' 23:59:59'))
)
AND (ov.take_meal_status IS NULL OR ov.take_meal_status != 'cancel')
GROUP BY 日期, 分组ID
) a ON base.日期 = a.日期 AND base.分组ID = a.分组ID
LEFT JOIN (
SELECT
DATE(o1.finish_time) AS 日期,
SUM(IF(p.payway = 'WechatPay' AND o.order_type = 0, o1.refund_fee, 0)) AS 微信支付退款,
SUM(IF(p.payway = 'PushiPay' AND o.order_type = 0, o1.refund_fee, 0)) AS 钱包支付退款,
SUM(IF(o.order_type = 0, o1.refund_fee, 0)) AS 总消费退款,
SUM(IF(o.order_type = 3, o1.refund_fee, 0)) AS 总充值退款,
SUM(IF(o.order_type = 0, o1.refund_fuwu_fee, 0)) AS 退款服务费,
COALESCE(o1.payer_group_id, 0) AS 分组ID
FROM
order_refund o1
LEFT JOIN payinfo p ON o1.payinfo_id = p.id
LEFT JOIN order o ON o1.origin_order_id = o.id
WHERE
o1.order_status = 'ORDER_SUCCESS'
AND o1.company_id = 1557
AND o1.organization_id IN (${DaoFormat.inNumber('${organization_ids}')})
AND o1.finish_time BETWEEN STR_TO_DATE('${start_pay_time}', '%Y-%m-%d') AND CONCAT('${end_pay_time}', ' 23:59:59')
GROUP BY 日期, 分组ID
) b ON base.日期 = b.日期 AND base.分组ID = b.分组ID
LEFT JOIN (
SELECT
DATE(o1.finish_time) AS 日期,
SUM(o1.pay_fee) AS 充值金额,
COALESCE(o1.payer_group_id, 0) AS 分组ID
FROM
order_charge o1
LEFT JOIN payinfo p ON o1.payinfo_id = p.id
WHERE
o1.order_status = 'ORDER_SUCCESS'
AND o1.company_id = 1557
AND o1.organization_id IN (${DaoFormat.inNumber('${organization_ids}')})
AND o1.finish_time BETWEEN STR_TO_DATE('${start_pay_time}', '%Y-%m-%d') AND CONCAT('${end_pay_time}', ' 23:59:59')
GROUP BY 日期, 分组ID
) C ON base.日期 = c.日期 AND base.分组ID = c.分组ID
LEFT JOIN card_user_group cug ON base.分组ID = cug.id
GROUP BY
base.日期,
base.分组ID
ORDER BY
base.日期
报表字段明细
报表参数
有问题预览
没有问题现象
有问题和没有问题得请求和参数在以下文件内
预览地址
https://jmreport.packertec.com/jmreport/view/1189714667356893184?token=GGhDga30tPGQPf1lGMvD-eBb0Lq8nOVbtsVuSXXTX24&tenantId=1