SELECT
`sc_customerservice`.`customerbill`.`id` AS `id`,
7 AS `7`,
'请假退费' AS `请假退费`,
IFNULL((`sc_customerservice`.`customerbill`.`refundServiceCost` + `sc_customerservice`.`customerbill`.`refundMealCost`),
0) AS `integralPunchCost`,
CONCAT('请、销假日期:',
( SELECT
CONCAT( GROUP_CONCAT(DATE_FORMAT(cl.StartTime, '%Y年%m月%d日'),
'--',
DATE_FORMAT(cl.ResumptionTime, '%Y年%m月%d日')
ORDER BY cl.ResumptionTime ASC
SEPARATOR '/'),
CONCAT('合计:',
CAST(SUM(cl.leaveAmount) AS CHAR),
'天'))
FROM
sc_customerservice.customerleave cl
LEFT JOIN
sc_customerservice.customeraccount cua ON cua.id = cl.customerAccount_Id
LEFT JOIN
sc_customerservice.customer cus ON cus.id = cua.customer_Id
where cl.ResumptionTime >=sc_customerservice.customerbill.startDate and cl.ResumptionTime <= sc_customerservice.customerbill.endDate
and cua.ProjectId= sc_customerservice.customerbill.project_Id
and sc_customerservice.customerbill.customer_Id= cus.id
GROUP BY cua.id
LIMIT 1
),'退餐费:',
IFNULL(`sc_customerservice`.`customerbill`.`refundMealCost`,
0),
'退服务费:',
IFNULL(`sc_customerservice`.`customerbill`.`refundServiceCost`,
0),
'合计:',
IFNULL((`sc_customerservice`.`customerbill`.`refundServiceCost` + `sc_customerservice`.`customerbill`.`refundMealCost`),
0)) AS discountDescription
FROM
sc_customerservice.customerbill
mysql字符串拼接 CONCAT 和GROUP_CONCAT
最新推荐文章于 2024-06-06 12:44:47 发布