<tfoot id='lqHpu'></tfoot>
  1. <small id='lqHpu'></small><noframes id='lqHpu'>

  2. <legend id='lqHpu'><style id='lqHpu'><dir id='lqHpu'><q id='lqHpu'></q></dir></style></legend>

        <bdo id='lqHpu'></bdo><ul id='lqHpu'></ul>
    1. <i id='lqHpu'><tr id='lqHpu'><dt id='lqHpu'><q id='lqHpu'><span id='lqHpu'><b id='lqHpu'><form id='lqHpu'><ins id='lqHpu'></ins><ul id='lqHpu'></ul><sub id='lqHpu'></sub></form><legend id='lqHpu'></legend><bdo id='lqHpu'><pre id='lqHpu'><center id='lqHpu'></center></pre></bdo></b><th id='lqHpu'></th></span></q></dt></tr></i><div id='lqHpu'><tfoot id='lqHpu'></tfoot><dl id='lqHpu'><fieldset id='lqHpu'></fieldset></dl></div>
    2. 如何填补 MySQL 中的日期空白?

      时间:2023-06-01
      <tfoot id='eLGQe'></tfoot>
      <i id='eLGQe'><tr id='eLGQe'><dt id='eLGQe'><q id='eLGQe'><span id='eLGQe'><b id='eLGQe'><form id='eLGQe'><ins id='eLGQe'></ins><ul id='eLGQe'></ul><sub id='eLGQe'></sub></form><legend id='eLGQe'></legend><bdo id='eLGQe'><pre id='eLGQe'><center id='eLGQe'></center></pre></bdo></b><th id='eLGQe'></th></span></q></dt></tr></i><div id='eLGQe'><tfoot id='eLGQe'></tfoot><dl id='eLGQe'><fieldset id='eLGQe'></fieldset></dl></div>

          <tbody id='eLGQe'></tbody>

              <bdo id='eLGQe'></bdo><ul id='eLGQe'></ul>

              <small id='eLGQe'></small><noframes id='eLGQe'>

            • <legend id='eLGQe'><style id='eLGQe'><dir id='eLGQe'><q id='eLGQe'></q></dir></style></legend>

              • 本文介绍了如何填补 MySQL 中的日期空白?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

                问题描述

                如何填补 MySQL 中的日期空白?这是我的查询:

                <前>SELECT DATE(posted_at) AS 日期,COUNT(*) AS 总数,SUM(attitude = 'positive') 为正,SUM(attitude = 'neutral') 作为中立,SUM(attitude = 'negative') AS 负数从`消息`WHERE (`messages`.brand_id = 1)AND (`messages`.`spam` = 0AND `messages`.`duplicate` = 0AND `messages`.`ignore` = 0)GROUP BY 日期 ORDER BY 日期

                它返回正确的结果集 - 但我想用零填充日期开始和结束之间的空白.我该怎么做?

                解决方案

                您需要创建一个辅助表并用从 startend 的所有日期填充它,然后只需 LEFT JOIN 与该表:

                SELECT d.dt AS 日期,COUNT(*) AS 总数,SUM(attitude = 'positive') 为正,SUM(attitude = 'neutral') 作为中立,SUM(attitude = 'negative') AS 负数FROM 日期 d左加入留言ON m.posted_at >= d.dtAND m.posted_at 

                基本上,您在这里需要的是一个虚拟行源.

                MySQL 是唯一缺乏生成它的方法的主要系统.

                PostgreSQL 实现了一个特殊的函数 generate_series 来做到这一点,而 OracleSQL Server 可以使用递归(CONNECT BY 和递归CTE,相应地).

                How i can fill date gaps in MySQL? Here is my query:

                SELECT DATE(posted_at) AS date,
                    COUNT(*) AS total,
                    SUM(attitude = 'positive') AS positive,
                    SUM(attitude = 'neutral') AS neutral,
                    SUM(attitude = 'negative') AS negative
                    FROM `messages`
                    WHERE (`messages`.brand_id = 1)
                    AND (`messages`.`spam` = 0
                    AND `messages`.`duplicate` = 0
                    AND `messages`.`ignore` = 0)
                    GROUP BY date ORDER BY date
                

                It returns proper result set - but i want to fill gaps between dates start and end by zeros. How i can do this?

                解决方案

                You'll need to create a helper table and fill it with all dates from start to end, then just LEFT JOIN with that table:

                SELECT  d.dt AS date,
                        COUNT(*) AS total,
                        SUM(attitude = 'positive') AS positive,
                        SUM(attitude = 'neutral') AS neutral,
                        SUM(attitude = 'negative') AS negative
                FROM    dates d
                LEFT JOIN
                        messages m
                ON      m.posted_at >= d.dt
                        AND m.posted_at < d.dt + INTERVAL 1 DAYS
                        AND spam = 0
                        AND duplicate = 0
                        AND ignore = 0
                GROUP BY
                        d.dt
                ORDER BY
                        d.dt
                

                Basically, what you need here is a dummy rowsource.

                MySQL is the only major system which lacks a way to generate it.

                PostgreSQL implements a special function generate_series to do that, while Oracle and SQL Server can use recursion (CONNECT BY and recursive CTEs, accordingly).

                这篇关于如何填补 MySQL 中的日期空白?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

                <tfoot id='2UIkq'></tfoot>
                <i id='2UIkq'><tr id='2UIkq'><dt id='2UIkq'><q id='2UIkq'><span id='2UIkq'><b id='2UIkq'><form id='2UIkq'><ins id='2UIkq'></ins><ul id='2UIkq'></ul><sub id='2UIkq'></sub></form><legend id='2UIkq'></legend><bdo id='2UIkq'><pre id='2UIkq'><center id='2UIkq'></center></pre></bdo></b><th id='2UIkq'></th></span></q></dt></tr></i><div id='2UIkq'><tfoot id='2UIkq'></tfoot><dl id='2UIkq'><fieldset id='2UIkq'></fieldset></dl></div>

                  <legend id='2UIkq'><style id='2UIkq'><dir id='2UIkq'><q id='2UIkq'></q></dir></style></legend>
                      <bdo id='2UIkq'></bdo><ul id='2UIkq'></ul>

                        <tbody id='2UIkq'></tbody>

                        <small id='2UIkq'></small><noframes id='2UIkq'>