MySQL 多时间类型查询数据，没有数据也要显示0

柚几哥哥

已于 2022-05-10 17:17:05 修改

阅读量1.2k

点赞数 3

分类专栏： Work笔记 MySQL 文章标签： mariadb linq 数据库

于 2022-05-10 11:54:48 首次发布

本文链接：https://blog.csdn.net/Zyw907155124/article/details/124683137

版权

Work笔记同时被 2 个专栏收录

31 篇文章 2 订阅

订阅专栏

MySQL

10 篇文章 1 订阅

订阅专栏

思路：

建立时间节点表，根据业务需求，将所要查询的表右连接时间表，在时间表的子查询中确定时间范围，再使用ifull（）若无数据返回0。

ifnull(count(a.id),0)

1、建立时间节点表

时间表sql文件https://download.csdn.net/download/Zyw907155124/85335465

2、分析所需要查询的表结构

3、根据年龄段分组查询不同时间类型的数据

type为时间类型，3表示月，4表示年

public List<ViewForAge> getAccommodationNumforAge(@Param("areaId")String areaId,
                                                                        @Param("startTime") String startTime,
                                                                        @Param("endTime") String endTime,
                                                                        @Param("type") String type);

SELECT
        t.time as time,
        ifnull(sum(case when a.nl &gt;= '0' and a.nl &lt;= '10' then 1 else 0 end),0) as zero,
        ifnull(sum(case when a.nl &gt;= '11' and a.nl &lt;= '20' then 1 else 0 end),0) as one,
        ifnull(sum(case when a.nl &gt;= '21' and a.nl &lt;= '30' then 1 else 0 end),0) as two,
        ifnull(sum(case when a.nl &gt;= '31' and a.nl &lt;= '40' then 1 else 0 end),0) as three,
        ifnull(sum(case when a.nl &gt;= '41' and a.nl &lt;= '50' then 1 else 0 end),0) as four,
        ifnull(sum(case when a.nl &gt;= '51' and a.nl &lt;= '60' then 1 else 0 end),0) as five,
        ifnull(sum(case when a.nl &gt;= '61' and a.nl &lt;= '70' then 1 else 0 end),0) as six,
        ifnull(sum(case when a.nl &gt;= '71' and a.nl &lt;= '80' then 1 else 0 end),0) as seven,
        ifnull(sum(case when a.nl &gt;= '81' and a.nl &lt;= '90' then 1 else 0 end),0) as eight,
        ifnull(sum(case when a.nl &gt;= '91' and a.nl &lt;= '100' then 1 else 0 end),0) as nine
        from zhoushan.y_share_lodging_guset_source_monitor a
        RIGHT join (
        SELECT
        <if test="type==3">
        SUBSTR(t.DMIS_DATE_NO,1,7)
        </if>
        <if test="type==4">
        SUBSTR(t.DMIS_DATE_NO,1,4)
        </if>
        as time FROM zhoushan.tbl_date_ntty t
        where
        <if test="type==3">
             SUBSTR(t.DMIS_DATE_NO,1,7) &gt;= #{startTime} and SUBSTR(t.DMIS_DATE_NO,1,7) &lt;= #{endTime}
        </if>
        <if test="type==4">
             SUBSTR(t.DMIS_DATE_NO,1,4) &gt;= #{startTime} and SUBSTR(t.DMIS_DATE_NO,1,4) &lt;= #{endTime}
        </if>
        GROUP BY  time )t
        on
        <if test="type==3">
        SUBSTR(a.rdate,1,7)
        </if>
        <if test="type==4">
        SUBSTR(a.rdate,1,4)
        </if>
        =  t.time
        and a.area_id = #{areaId}
        GROUP BY time ORDER BY time desc

结果：

{
  "年龄分类游客量": [
    {
      "time": "2022-04",
      "zero": "9",
      "one": "402",
      "two": "850",
      "three": "894",
      "four": "826",
      "five": "770",
      "six": "653",
      "seven": "359",
      "eight": "79",
      "nine": "0"
    },
    {
      "time": "2022-03",
      "zero": "2",
      "one": "417",
      "two": "877",
      "three": "943",
      "four": "879",
      "five": "779",
      "six": "705",
      "seven": "461",
      "eight": "85",
      "nine": "0"
    }
  ],
  "state": "200",
  "msg": "获取成功"
}

4、根据性别分组查询不同时间类型的数据

type为时间类型，3表示月，4表示年

    public  List<ViewForSex> getAccommodationForSex(@Param("areaId")String areaId,
                                                      @Param("startTime")String startTime,
                                                      @Param("endTime") String endTime,
                                                      @Param("type")String type);

SELECT
        t.time as time,
        ifnull(sum(case when a.xb = '男' then 1 else 0 end),0) as man,
        ifnull(sum(case when a.xb = '女' then 1 else 0 end),0) as woman
        from zhoushan.y_share_lodging_guset_source_monitor a
        RIGHT join (
        SELECT
        <if test="type==3">
            SUBSTR(t.DMIS_DATE_NO,1,7)
        </if>
        <if test="type==4">
            SUBSTR(t.DMIS_DATE_NO,1,4)
        </if>
        as time FROM zhoushan.tbl_date_ntty t
        where
        <if test="type==3">
            SUBSTR(t.DMIS_DATE_NO,1,7) &gt;= #{startTime} and SUBSTR(t.DMIS_DATE_NO,1,7) &lt;= #{endTime}
        </if>
        <if test="type==4">
            SUBSTR(t.DMIS_DATE_NO,1,4) &gt;= #{startTime} and SUBSTR(t.DMIS_DATE_NO,1,4) &lt;= #{endTime}
        </if>
        GROUP BY  time )t
        on
        <if test="type==3">
            SUBSTR(a.rdate,1,7)
        </if>
        <if test="type==4">
            SUBSTR(a.rdate,1,4)
        </if>
        =  t.time
        and a.area_id =  #{areaId}
        GROUP BY time ORDER BY time desc

结果：

{
  "性别分类游客量": [
    {
      "time": "2022-05",
      "man": 871,
      "woman": 623
    },
    {
      "time": "2022-04",
      "man": 2937,
      "woman": 1941
    },
    {
      "time": "2022-03",
      "man": 3162,
      "woman": 2045
    },
    {
      "time": "2022-02",
      "man": 2841,
      "woman": 2021
    },
    {
      "time": "2022-01",
      "man": 3138,
      "woman": 2114
    }
  ],
  "state": "200",
  "msg": "获取成功"
}