需求:根据用户名或者邀请码查询users表中邀请奖励(reward),username(username),邀请码(myinvitecode),邀请人数(自己算出来的)以及邀请历史,邀请历史是多条记录,就是根据我的邀请码(myinvitecode)查找出用户字段中邀请人的邀请码(invitecode)。如果存在邀请历史,就查出来List并计算邀请人数返回,如果不存在邀请历史只需要查出对应的邀请奖励,邀请码,用户名
原sql
select count(*) as countm,
u1.user_id,
u1.username,
u1.my_invitation_code,
u1.invited_code,
u1.award_has_given,
u1.created_at,
u1.is_invited
from users u1,users u2
where u1.my_invitation_code = u2.invited_code
<if test="userInviteInfoRq != null">
<if test="userInviteInfoRq.myInvitationCode != null and userInviteInfoRq.myInvitationCode != ''">
and u1.my_invitation_code = #{userInviteInfoRq.myInvitationCode}
</if>
<if test="userInviteInfoRq.username != null and userInviteInfoRq.username != ''">
and u1.username = #{userInviteInfoRq.username}
</if>
</if>
group by
u1.user_id,
u1.username,
u1.my_invitation_code,
u1.invited_code,
u1.award_has_given,
u1.created_at,
u1.is_invited
上面的sql只能查出有邀请记录的数据,如果没有邀请记录就查不出数据,这个问题能一条sql搞定吗,求帮