分组去重
SELECT
id,
plan_person_name,
plan_org_name,
finish_survey_process,
plan_theme,
plan_ed_date,
plan_category,
plan_direction,
MAX(plan_ed_date) date,
COUNT(1) num
FROM
bus_survey_record
WHERE approve_status = 'SHZT-006'
<if test="areaCode != null and areaCode != ''">
and plan_area_code like CONCAT (#{areaCode},'%')
</if>
<if test="option != null and option == 1 ">
and plan_category = #{value}
</if>
<if test="option != null and option == 2 ">
and plan_direction = #{value}
</if>
GROUP BY
plan_theme
ORDER by plan_ed_date DESC
较昨日今日的数据展示
SELECT T.on_line param1 , (T.today - T.yesterday) param2, (T.today - T.weeks) param3 FROM
(
SELECT
(SELECT COUNT(1) num FROM gz_service WHERE h5_status = 1) as on_line,
(SELECT COUNT(1) num FROM gz_service s INNER JOIN gz_h5_version_manage v ON s.h5_id = v.h5_id WHERE s.h5_status = 1 and v.is_online = 1 and DATE_FORMAT(publish_time,'%Y%m%d') = CURDATE()) as today,
(SELECT COUNT(1) num FROM gz_service s INNER JOIN gz_h5_version_manage v ON s.h5_id = v.h5_id WHERE s.h5_status = 1 and v.is_online = 1 and DATE_FORMAT(publish_time,'%Y%m%d') = DATE_FORMAT( date_add( CURDATE(), INTERVAL -1 day ), '%Y%m%d' )) as yesterday,
(SELECT COUNT(1) num FROM gz_service s INNER JOIN gz_h5_version_manage v ON s.h5_id = v.h5_id WHERE s.h5_status = 1 and v.is_online = 1 and DATE_FORMAT(publish_time,'%Y%m%d') = DATE_FORMAT( date_add( CURDATE(), INTERVAL -7 day ), '%Y%m%d' )) as weeks) AS T