统计SQL

候选人列表

SELECT 

  (@rowNum := @rowNum + 1) AS rowNo,

  C.NUMBER,

  C.HEADIMG,

  C.NAME,

  C.SECTION,

  C.DECLARATION,

  C.INTRODUCTION,

  V.NAME VOTENAME,

  IFNULL(T.TICKET, 0) TICKET 

FROM

  TB_CANDIDATE C 

  LEFT JOIN 

    (SELECT 

      COUNT(*) TICKET,

      D.VOTE_ID,

      D.CANDIDATE 

    FROM

      TB_VOTE_DETAIL D 

    GROUP BY D.CANDIDATE,

      D.VOTE_ID) T 

    ON T.CANDIDATE = C.ID 

  LEFT JOIN TB_VOTE V 

    ON C.VOTE_ID = V.VOTE_ID,

  (SELECT 

    (@rowNum := 0)) b 

WHERE C.STATUS = ‘1‘ 

---------------------------

投票人情况

SELECT 

  (@rowNum := @rowNum + 1) AS rowNo,

  C.NAME,

  D.OPENID,

  P.PHONE,

  COUNT(*) AMOUNT

FROM

  TB_VOTE_DETAIL D 

  LEFT JOIN TB_OPENID_PHONE P 

    ON D.OPENID = P.OPENID 

    LEFT JOIN TB_CANDIDATE C 

    ON D.CANDIDATE=C.ID,

    (SELECT 

    (@rowNum := 0)) b 

WHERE D.CANDIDATE = ‘1‘ 

GROUP BY D.OPENID

ORDER BY AMOUNT DESC

本文出自 “Java乐园” 博客，请务必保留此出处http://hebinteng.blog.51cto.com/7967512/1951183

统计SQL

标签：笔记