java 统计sql_统计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