记录几个分组去重sql和较昨日今日的sql

菜鸟ZhAng~

于 2023-07-04 16:58:39 发布

阅读量715

点赞数

文章标签： sql 数据库

本文链接：https://blog.csdn.net/weixin_56227932/article/details/131538814

版权

分组去重

 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