mysql - 优化 SQL 查询以获取 count(*)

Question

我有以下查询，它获取一些我想按属性“状态”分组的结果。我尝试了不同的子查询，但它们不起作用，我有点受阻。SQL 是:

    SELECT state, id_candidate_basic, MAX( DATE )  FROM `candidate_state` 
    WHERE `date` <= '2013-09-06 00:00:00' GROUP BY id_candidate_basic
    ORDER BY `candidate_state`.`id_candidate_basic` DESC

当前返回:

我会得到每个州的计数(*)。例子:女，14我，10哦，9

Answer 1

SELECT  state, 
        id_candidate_basic, 
        MAX( DATE ),
        COALESCE(totalCount, 0) totalCount
FROM    `candidate_state` 
        LEFT JOIN 
        (
            SELECT  state, COUNT(*) totalCount
            FROM    candidate_state
            WHERE   `date` <= '2013-09-06 00:00:00' 
            GROUP   BY state
        )   ON candidate_state.state = b.state
WHERE   `date` <= '2013-09-06 00:00:00' 
GROUP   BY id_candidate_basic
ORDER   BY `candidate_state`.`id_candidate_basic` DESC