一、需求

image.png
完成上面表的构建，当前月份所有数据查询。

二、构建SQL

思路：

  • 对数据进行group by 分组 ```sql
    1.                   SELECT
    2.         COUNT( 1 ) AS countNumber,
    3.         DATE_FORMAT( init_time, '%Y-%m-%d' ) AS dateTime
    4.         FROM
    5.         camera
    6.         WHERE
    7.         del_flag = 0
    8.         AND init_time  <![CDATA[>=]]> date_add( curdate(), INTERVAL - DAY ( curdate())+ 1 DAY )
    9.         AND last_day(
    10.         curdate())
    11.                   GROUP BY
    12.       # 根据天数分组，但是如果某天没有数据，那么这天的数据就不存在，但是显示需要为空
    13.                   DATE_FORMAT( init_time, '%Y-%m-%d' )
  2. - 和当前月天数进行连接
  3. ```sql
  4.     # 单独获得本月所有天数，用于表的连结
  5.           SELECT
  6.                         ADDDATE( y.FIRST, x.date - 1 ) AS d
  7.                         FROM
  8.                         (
  9.               SELECT
  10.               1 AS date UNION ALL
  11.               SELECT
  12.               2 UNION ALL
  13.               SELECT
  14.               3 UNION ALL
  15.               SELECT
  16.               4 UNION ALL
  17.               SELECT
  18.               5 UNION ALL
  19.               SELECT
  20.               6 UNION ALL
  21.               SELECT
  22.               7 UNION ALL
  23.               SELECT
  24.               8 UNION ALL
  25.               SELECT
  26.               9 UNION ALL
  27.               SELECT
  28.               10 UNION ALL
  29.               SELECT
  30.               11 UNION ALL
  31.               SELECT
  32.               12 UNION ALL
  33.               SELECT
  34.               13 UNION ALL
  35.               SELECT
  36.               14 UNION ALL
  37.               SELECT
  38.               15 UNION ALL
  39.               SELECT
  40.               16 UNION ALL
  41.               SELECT
  42.               17 UNION ALL
  43.               SELECT
  44.               18 UNION ALL
  45.               SELECT
  46.               19 UNION ALL
  47.               SELECT
  48.               20 UNION ALL
  49.               SELECT
  50.               21 UNION ALL
  51.               SELECT
  52.               22 UNION ALL
  53.               SELECT
  54.               23 UNION ALL
  55.               SELECT
  56.               24 UNION ALL
  57.               SELECT
  58.               25 UNION ALL
  59.               SELECT
  60.               26 UNION ALL
  61.               SELECT
  62.               27 UNION ALL
  63.               SELECT
  64.               28 UNION ALL
  65.               SELECT
  66.               29 UNION ALL
  67.               SELECT
  68.               30 UNION ALL
  69.               SELECT
  70.               31
  71.             ) x,
  72.                         ( SELECT CURDATE() - INTERVAL DAY ( CURDATE()) - 1 DAY AS FIRST, DAY ( LAST_DAY( CURDATE())) AS last ) y
  73.                         WHERE
  74.                         x.date  <=y.last
  75.                         ) AS tempDate

整合起来

  1. SELECT
  2.         IFNULL(countNumber,0) as counts,d
  3.         FROM
  4.         (
  5.                         SELECT
  6.                         COUNT( 1 ) AS countNumber,
  7.                         DATE_FORMAT( init_time, '%Y-%m-%d' ) AS dateTime
  8.                         FROM
  9.                         camera
  10.                         WHERE
  11.                         del_flag = 0
  12.                         AND init_time  <![CDATA[>=]]> date_add( curdate(), INTERVAL - DAY ( curdate())+ 1 DAY )
  13.                         AND last_day(
  14.                         curdate())
  15.                         GROUP BY
  16.                         DATE_FORMAT( init_time, '%Y-%m-%d' )
  17.         ) AS temp1
  19.         RIGHT JOIN (
  20.                         SELECT
  21.                         ADDDATE( y.FIRST, x.date - 1 ) AS d
  22.                         FROM
  23.                         (
  24.                         SELECT
  25.                         1 AS date UNION ALL
  26.                         SELECT
  27.                         2 UNION ALL
  28.                         SELECT
  29.                         3 UNION ALL
  30.                         SELECT
  31.                         4 UNION ALL
  32.                         SELECT
  33.                         5 UNION ALL
  34.                         SELECT
  35.                         6 UNION ALL
  36.                         SELECT
  37.                         7 UNION ALL
  38.                         SELECT
  39.                         8 UNION ALL
  40.                         SELECT
  41.                         9 UNION ALL
  42.                         SELECT
  43.                         10 UNION ALL
  44.                         SELECT
  45.                         11 UNION ALL
  46.                         SELECT
  47.                         12 UNION ALL
  48.                         SELECT
  49.                         13 UNION ALL
  50.                         SELECT
  51.                         14 UNION ALL
  52.                         SELECT
  53.                         15 UNION ALL
  54.                         SELECT
  55.                         16 UNION ALL
  56.                         SELECT
  57.                         17 UNION ALL
  58.                         SELECT
  59.                         18 UNION ALL
  60.                         SELECT
  61.                         19 UNION ALL
  62.                         SELECT
  63.                         20 UNION ALL
  64.                         SELECT
  65.                         21 UNION ALL
  66.                         SELECT
  67.                         22 UNION ALL
  68.                         SELECT
  69.                         23 UNION ALL
  70.                         SELECT
  71.                         24 UNION ALL
  72.                         SELECT
  73.                         25 UNION ALL
  74.                         SELECT
  75.                         26 UNION ALL
  76.                         SELECT
  77.                         27 UNION ALL
  78.                         SELECT
  79.                         28 UNION ALL
  80.                         SELECT
  81.                         29 UNION ALL
  82.                         SELECT
  83.                         30 UNION ALL
  84.                         SELECT
  85.                         31
  86.                         ) x,
  87.                         ( SELECT CURDATE() - INTERVAL DAY ( CURDATE()) - 1 DAY AS FIRST, DAY ( LAST_DAY( CURDATE())) AS last ) y
  88.                         WHERE
  89.                         x.date  <=y.last
  90.                         ) AS tempDate
  91.             ON tempDate.d = temp1.dateTime
  92.             ORDER BY  tempDate.d

结果如下：
image.png

三、总结分析

  • 在百度过程中，发现一种写法：@data:….只是定义了变量，方便在其他地方引用，在sql中也可以直接使用。 ~~这难道不是函数 ？并不是。。 ~~