mysql - 在mysql中显示相同数据的行

Question

我有sql查询

SET @cumulative_sum := 0;
SELECT fct_sales.datDate, @cumulative_sum := @cumulative_sum + fct_sales.dblTotal AS     
cumulative_sum
FROM fct_sales
where
fct_sales.intProductID=40
and
fct_sales.datDate
between
'2011-01-01'
and
'2011-01-10'
 ORDER BY fct_sales.datDate ASC 

这很好，给我结果表

 _______________________________
 |     datDate|  Cumulative_sum|
 |____________|________________|          
 | 2011-01-02 |   5005         |
 |____________|________________|
 |2011-01-04  |   7007         |
 |____________|________________|

但是，我需要的是:

  _____________________________
 |     datDate|  Cumulative_sum|
 |____________|________________| 
 |  2011-01-01|  5005          |
 |____________|________________| 
 |  2011-01-02|  5005          |
 |____________|________________| 
 |  2011-01-03|  5005          |
 |____________|________________| 
 |  2011-01-04|  7007          |
 |____________|________________| 
 |  2011-01-05|  7007          |
 |____________|________________| 
 |  2011-01-06|  7007          |
 |____________|________________| 
 |  2011-01-07|  7007          |
 |____________|________________| 
 |  2011-01-08|  7007          |
 |____________|________________| 
 |  2011-01-09|  7007          |
 |____________|________________| 
 |  2011-01-10|  7007          |
 |____________|________________|

我该如何实现？？

Answer 1

您好，对于这个查询，您基本上需要 3 样东西:

1. 一组日期
2. 将 fct_sales 加入同一张表得到 nextDate
3. 确定日期并加入 fct_sales。

如果您希望 5005 和的日期为 2011-01-01，我认为这是不可能的，因为如果列表中的 Cumulative_sums 超过 2 个，它们就会相互重叠...

示例不漂亮，因为您需要日期范围。有时在数据库中是时间表，这将有助于减少查询代码。如果这对您没有帮助，那么至少一点点会有用:)

SQLFIDDLE

所以这是我的例子:

 SELECT
    a.Date
    ,b.Cumulative_sum
    FROM
      (SELECT DATE_FORMAT(curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY, '%Y-%m-%d') AS Date
       FROM
         (SELECT 0 AS a
          UNION ALL SELECT 1
          UNION ALL SELECT 2
          UNION ALL SELECT 3
          UNION ALL SELECT 4
          UNION ALL SELECT 5
          UNION ALL SELECT 6
          UNION ALL SELECT 7
          UNION ALL SELECT 8
          UNION ALL SELECT 9) AS a CROSS
       JOIN
         (SELECT 0 AS a
          UNION ALL SELECT 1
          UNION ALL SELECT 2
          UNION ALL SELECT 3
          UNION ALL SELECT 4
          UNION ALL SELECT 5
          UNION ALL SELECT 6
          UNION ALL SELECT 7
          UNION ALL SELECT 8
          UNION ALL SELECT 9) AS b CROSS
       JOIN
         (SELECT 0 AS a
          UNION ALL SELECT 1
          UNION ALL SELECT 2
          UNION ALL SELECT 3
          UNION ALL SELECT 4
          UNION ALL SELECT 5
          UNION ALL SELECT 6
          UNION ALL SELECT 7
          UNION ALL SELECT 8
          UNION ALL SELECT 9) AS c) a,
       (SELECT 
        fc1.datDate, 
        fc1.Cumulative_sum, 
         (SELECT MIN(fc2.datDate)  
          from fc fc2 
          WHERE fc1.datDate < fc2.datDate ) AS nextDate
    from fc fc1) b
      where (a.Date between '2011-01-01' and '2011-01-10')
        AND a.Date>=b.datDate 
        AND a.Date < COALESCE(b.nextDate, '2011-01-11')
      ORDER BY a.Date, b.Cumulative_sum

结果:

DATE    CUMULATIVE_SUM
2011-01-02  5005
2011-01-03  5005
2011-01-04  7007
2011-01-05  7007
2011-01-06  7007
2011-01-07  7007
2011-01-08  7007
2011-01-09  7007
2011-01-10  7007

使用您的查询数据查询示例:

SET @cumulative_sum := 0;
SELECT
    a.Date
    ,b.Cumulative_sum
    FROM
      (SELECT DATE_FORMAT(curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY, '%Y-%m-%d') AS Date
       FROM
         (SELECT 0 AS a
          UNION ALL SELECT 1
          UNION ALL SELECT 2
          UNION ALL SELECT 3
          UNION ALL SELECT 4
          UNION ALL SELECT 5
          UNION ALL SELECT 6
          UNION ALL SELECT 7
          UNION ALL SELECT 8
          UNION ALL SELECT 9) AS a CROSS
       JOIN
         (SELECT 0 AS a
          UNION ALL SELECT 1
          UNION ALL SELECT 2
          UNION ALL SELECT 3
          UNION ALL SELECT 4
          UNION ALL SELECT 5
          UNION ALL SELECT 6
          UNION ALL SELECT 7
          UNION ALL SELECT 8
          UNION ALL SELECT 9) AS b CROSS
       JOIN
         (SELECT 0 AS a
          UNION ALL SELECT 1
          UNION ALL SELECT 2
          UNION ALL SELECT 3
          UNION ALL SELECT 4
          UNION ALL SELECT 5
          UNION ALL SELECT 6
          UNION ALL SELECT 7
          UNION ALL SELECT 8
          UNION ALL SELECT 9) AS c) a,
       (SELECT
    fc1.datDate,
    fc1.Cumulative_sum,
         (SELECT MIN(fc2.datDate) AS datDate
            FROM (SELECT fct_sales.datDate, @cumulative_sum := @cumulative_sum + fct_sales.dblTotal AS     
                    cumulative_sum
                  FROM fct_sales
                 where
                fct_sales.intProductID=40
                and fct_sales.datDate between '2011-01-01' and '2011-01-10') fc2
          WHERE fc1.datDate < fc2.datDate ) AS nextDate
        FROM (SELECT fct_sales.datDate, @cumulative_sum := @cumulative_sum + fct_sales.dblTotal AS     
              cumulative_sum
             FROM fct_sales
             where
             fct_sales.intProductID=40
            and fct_sales.datDate between '2011-01-01' and '2011-01-10' ) fc1) b
      where (a.Date between '2011-01-01' and '2011-01-10')
        AND a.Date>=b.datDate 
        AND a.Date < COALESCE(b.nextDate, '2011-01-11')
      ORDER BY a.Date, b.Cumulative_sum