一、 编写一条查找语句实现优惠券查找,根据该用户获取不同状态下的个数:
返回一个map集合
Map<String, Long> getCouponCountByStatus(@Param("userId") String userId);
xml文件代码
<select id="getCouponCountByStatus" resultType="map">
SELECT
COUNT(CASE WHEN cu.`status` = 0 THEN 1 ELSE NULL END) AS 'expiredCount' ,-- 失效
COUNT(CASE WHEN cu.`status` = 1 THEN 1 ELSE NULL END) AS 'unUseCount', -- 有效
COUNT(CASE WHEN cu.`status` = 2 THEN 1 ELSE NULL END) AS 'useCount' -- 过期
FROM tz_coupon_user cu
INNER JOIN tz_coupon c
ON c.`coupon_id` = cu.`coupon_id`
WHERE cu.`user_id` =#{userId}
AND cu.`is_delete` = 0
AND c.`puton_status` =1
</select>
执行后返回的数据:
{“unUseCount”:11,“expiredCount”:21,“useCount”:5}