最近需要实现一个统计优惠券明细的功能，大致需求是选择优惠券，来查询领取了该优惠券的导购员，领取的总数量，发放的数量，剩余量，以及领取了该优惠券的会员，领取的数量和使用状态。
这里涉及了到了5张表，优惠券表，优惠券和导购关联表，导购信息表，优惠券和会员关联表，会员信息表。他们的关系大致为下图：
会员信息表VID(主键)=优惠券和会员关联表VIPID
优惠券和会员关联表MID=优惠券表.ID(主键)
优惠券表ID(主键)=优惠券和导购关联表.ECID
优惠券和导购关联表.uid=导购信息表.ACC(主键)
然后查询优惠券相关联的导购，以及导购领取该优惠券的剩余量，发放量和总量
sql语句如下：
SELECT USERINFO.UNAME,ENTINFO.ENAME,TTL.UID,TTL.TTLCOUNT AS uidtotalnumber,RC.RCOUNT AS uidremain
FROM (SELECT UID,COUNT(ID) AS TTLCOUNT FROM ls_bs_ecoupond WHERE ECID = #{p.id} AND STATU = 0 GROUP BY UID) TTL
LEFT JOIN (SELECT UID,COUNT(ID) AS RCOUNT FROM ls_bs_ecoupond WHERE ECID =#{p.id} AND STATU !=0 GROUP BY UID) RC
ON TTL.UID = RC.UID
INNER JOIN LS_BS_USERINFO USERINFO ON USERINFO.ACC = TTL.UID
LEFT JOIN LS_MD_USERENT USERENT ON USERINFO.ACC = USERENT.UID
LEFT JOIN LS_BS_ENTINFO ENTINFO ON ENTINFO.ID = USERENT.EID
WHERE ENTINFO.ETYPE IN ('S','M','Z')
另外查询会员的领取信息(领取会员，领取数量，使用状态(使用，未使用))的sql:
SELECT VIP.VIPNAME,TTL.EID,TTL.VIPID,TTL.TTLCOUNT AS viptotalcoupons,UC.VRCOUNT AS vipalreadycoupons
FROM (SELECT COU.VIPID,COU.EID,COUNT(COU.ID) AS TTLCOUNT FROM ls_bs_coupon cou
WHERE cou.MID = #{p.id} GROUP BY COU.VIPID,COU.EID) TTL
LEFT JOIN
(SELECT COU.VIPID,COU.EID,COUNT(COU.ID) AS VRCOUNT FROM ls_bs_coupon cou
WHERE cou.MID = #{p.id} AND COU.CTYPE=2 GROUP BY COU.VIPID,COU.EID) UC
ON TTL.EID = UC.EID AND TTL.VIPID = UC.VIPID
LEFT JOIN ls_bs_vipinfo VIP ON VIP.VID = TTL.VIPID AND VIP.EID = TTL.EID
GROUP BY VIP.vid
下面这条sql则是根据uid（导购的id）来查询该导购下有那些会员领取他所发放的优惠券信息：
SELECT VIP.VIPNAME,TTL.EID,TTL.VIPID,TTL.TTLCOUNT AS viptotalcoupons,UC.VRCOUNT AS vipalreadycoupons
FROM (SELECT coud.uid, COU.VIPID,COU.EID,COUNT(COU.ID) AS TTLCOUNT FROM ls_bs_coupon cou LEFT JOIN ls_bs_ecoupond coud ON cou.mid= coud.ecid AND coud.id=cou.cid
WHERE cou.MID = #{p.id} AND cou.ctype='0' AND coud.uid=#{p.uid} GROUP BY COU.VIPID,COU.EID,coud.ecid) TTL
LEFT JOIN
(SELECT COU.VIPID,COU.EID,COUNT(COU.ID) AS VRCOUNT FROM ls_bs_coupon cou LEFT JOIN ls_bs_ecoupond coud ON cou.mid= coud.ecid AND coud.id=cou.cid
WHERE cou.MID = #{p.id} AND COU.CTYPE='2' AND coud.uid=#{p.uid} GROUP BY COU.VIPID,COU.EID,coud.ecid) UC
ON TTL.EID = UC.EID AND TTL.VIPID = UC.VIPID
LEFT JOIN ls_bs_vipinfo VIP ON VIP.VID = TTL.VIPID AND VIP.EID = TTL.EID
GROUP BY VIP.vid
以下附上5张表的截图
1.优惠券表
2.优惠券和会员关系表
3.会员信息表
4.优惠券和导购关系表
5.导购表
