题解 | #未完成试卷数大于1的有效用户#

1.统计2021年各用户完成试卷数，未完成试卷数；

然后用GROUP_CONCAT函数拼接出完成试卷的详情。

SELECT
        er.uid,
        CAST( count( submit_time ) AS SIGNED ) AS complete_cnt,
        CAST( ( count( start_time ) - count( submit_time ) ) AS SIGNED ) AS incomplete_cnt,
        GROUP_CONCAT( DISTINCT CONCAT_WS( ':', DATE_FORMAT( start_time, '%Y-%m-%d' ), tag ) SEPARATOR ';' ) AS detail 
    FROM
        exam_record er
        INNER JOIN examination_info ei ON er.exam_id = ei.exam_id 
    WHERE
        DATE_FORMAT( start_time, '%Y' ) = 2021 
    GROUP BY

2.去掉完成试卷数小于1 ，未完成试卷数小于等于1，未完成试卷数大等于5的用户；结果按未完成试卷数降序排序。

WHERE
    complete_cnt >= 1 
    AND incomplete_cnt <5
    And incomplete_cnt > 1
ORDER BY
    incomplete_cnt DESC;