InviteRewardDetailMapper.sql

    1.  <select id="listRewardStatistic" resultType="cn.jmfen.sport.activitycenter.invite.dto.RewardStatisticDto"
    2.             parameterType="cn.jmfen.sport.activitycenter.invite.bo.InviteRewardSearchBo">
    3.         SELECT
    4.             temp.from_uid as uid,
    5.             temp.from_nickname as nickname,
    6.             -- 注意这里的count
    7.             count(reward_type = 2 OR reward_type = 0 OR NULL) AS invite_num,
    8.             sum(if(temp.from_uid = temp.reward_uid,reward_num,0)) AS reward_amount
    9.         FROM
    10.             (
    11.                 SELECT
    12.                 b.uid as reward_uid,
    13.                 a.from_uid,
    14.                 a.from_nickname,
    15.                 IF (b.create_time IS NULL,a.create_time,b.create_time) AS reward_time,
    16.                 IF (b.reward_type IS NULL,0,b.reward_type) AS reward_type,
    17.                 IF (b.reward_num IS NULL,0,b.reward_num) AS reward_num
    18.                 FROM jmf_invite_attend_detail a
    19.                 LEFT JOIN jmf_invite_reward_detail b ON a.id = b.aid
    20.             ) AS temp
    21.         <where>
    22.             temp.reward_time between #{searchBo.startTime} and #{searchBo.endTime}
    23.             <if test="searchBo.user != null and searchBo.user!=''">
    24.                 and temp.from_uid like concat('%',#{searchBo.user},'%') or temp.from_nickname like concat('%',#{searchBo.user},'%')
    25.             </if>
    26.         </where>
    27.         GROUP BY temp.from_uid ORDER BY invite_num DESC
    28.     </select>
    30. -- 如count(release_year = '2006' or NULL) 这部分为什么要加上or NULL,
    31. -- 直接count(release_year='2006')有什么问题吗，不就是要找release_year = '2006'的数据吗，为什么要计算NULL的数据
    32. -- 因为当 release_year不是 2006时 ，release_year='2006' 结果false 不是 NULL，Count在 值是NULL是 不统计数, 
    33. -- （count('任意内容')都会统计出所有记录数，因为count只有在遇见null时不计数,
    34. -- 即count(null)==0，因此前者单引号内不管输入什么值都会统计出所有记录数）至于加上or NULL,
    35. -- 很像其他编程里的or运算符，第一个表达式是true就是不执行or后面的表达式，第一个表达式是false 执行or后面的表达式 
    36. -- 当release_year不为2006时release_year = '2006' or NULL 的结果是NULL，Count才不会统计上这条记录数