图的MySQL查询

Question

我得到了以下查询：

SELECT GVA12.FECHA_EMIS, GVA12.COD_VENDED, sum(GVA12.IMPORTE)
FROM GVA12
WHERE Month(GVA12.FECHA_EMIS)=Month(curDate())
  AND Year(GVA12.FECHA_EMIS)=Year(curDate())
  AND GVA12.COD_VENDED="EX"
  AND GVA12.T_COMP="FAC"
GROUP BY GVA12.FECHA_EMIS

这是一个月度图表。我有两个问题。第一，如何将月份的所有日期显示为零(那些没有任何销售的月份)；第二，有没有办法使这些值相加，所以最后一个值是所有值的总和。

编辑：@Bluefeet根据您的查询，我创建了以下内容：

SELECT Month(Days.DMY), Year(Days.DMY), GVA12.COD_VENDED, sum(GVA12.IMPORTE)
FROM Days
left join GVA12
    on Month(Days.DMY) = Month(GVA12.FECHA_EMIS)
    and Year(Days.DMY) = Year(GVA12.FECHA_EMIS)
WHERE Month(GVA12.FECHA_EMIS)=Month(curDate())
  AND Year(GVA12.FECHA_EMIS)=Year(curDate())
  AND GVA12.COD_VENDED="EX"
  AND GVA12.T_COMP="FAC"
GROUP BY Month(Days.DMY), Year(Days.DMY) WITH ROLLUP

我得到了附加的结果(截图)。

​

它没有像我想要的那样显示一个月中的所有日子。我能做什么?

编辑#3

它现在可以工作了，但我想添加另一个过滤器。此筛选器在此处添加http://sqlfiddle.com/#!2/9d46c/1

Answer 1

要获得总数，您可以使用GROUP BY WITH ROLLUP，它将为您提供所有日期的总计：

SELECT GVA12.FECHA_EMIS, GVA12.COD_VENDED, sum(GVA12.IMPORTE)
FROM GVA12
WHERE Month(GVA12.FECHA_EMIS)=Month(curDate())
  AND Year(GVA12.FECHA_EMIS)=Year(curDate())
  AND GVA12.COD_VENDED="EX"
  AND GVA12.T_COMP="FAC"
GROUP BY GVA12.FECHA_EMIS WITH ROLLUP

至于返回不存在的日期，有许多问题的答案，包括以下。有时，在MySQL中创建要连接的表会更容易：

generate days from date range

Get a list of dates between two dates

编辑#1:如果你有一个带有日期的表，那么你可以使用类似于下面这样的东西：

SELECT Month(d.yourDateCol), Year(d.yourDateCol), g.COD_VENDED, sum(g.IMPORTE)
FROM dates d
left join GVA12 g
    on Month(d.yourDateCol) = Month(GVA12.FECHA_EMIS)
    and Year(d.yourDateCol) = Year(GVA12.FECHA_EMIS)
WHERE Month(g.FECHA_EMIS)=Month(curDate())
  AND Year(g.FECHA_EMIS)=Year(curDate())
  AND g.COD_VENDED="EX"
  AND g.T_COMP="FAC"
GROUP BY Month(d.yourDateCol), Year(d.yourDateCol) WITH ROLLUP

编辑#2:在没有看到完整的表结构或一些示例数据的情况下，以下是正在运行的查询的一个版本：

select month(d.dmy) Month, 
  year(d.dmy) Year, 
  coalesce(sum(g.Importe), 0) TotalImporte
from dates d
left join GVA12 g
  on month(d.dmy) = month(g.FECHA_EMIS)
  and year(d.dmy) = year(g.FECHA_EMIS)
group by month(d.dmy), year(d.dmy)  WITH ROLLUP

参见SQL Fiddle with Demo。这将返回dates表中每个月/年的月/年，即使它在GVA12表中不存在，

编辑#3:如果你想要运行总数，而不仅仅是最终总数，那么你应该能够使用以下方法：

SET @running_total := 0;

SELECT month(Days.DMY) Month, 
  Year(Days.DMY) Year, 
  Date(Days.DMY) Date,
  g.COD_VENDED,
  @running_total := @running_total + Coalesce(TotalImport, 0) as TotalImport
FROM Days
left join
(
  select FECHA_EMIS,
    COD_VENDED,
    sum(IMPORTE) TotalImport
  from GVA12
  group by Date(FECHA_EMIS), Year(FECHA_EMIS)
) g
  on date(Days.DMY) = date(g.FECHA_EMIS)
  and g.COD_VENDED='EX'
  and Month(g.FECHA_EMIS)=Month(curDate())
  and Year(g.FECHA_EMIS)=Year(curDate())
WHERE month(days.dmy)=Month(curDate())

请参阅SQL Fiddle with Demo

结果是：

| MONTH | YEAR |                           DATE | COD_VENDED | TOTALIMPORT |
----------------------------------------------------------------------------
|     1 | 2013 | January, 01 2013 00:00:00+0000 |     (null) |           0 |
|     1 | 2013 | January, 02 2013 00:00:00+0000 |         EX |        1000 |
|     1 | 2013 | January, 03 2013 00:00:00+0000 |         EX |        4000 |
|     1 | 2013 | January, 04 2013 00:00:00+0000 |     (null) |        4000 |
|     1 | 2013 | January, 05 2013 00:00:00+0000 |     (null) |        4000 |
|     1 | 2013 | January, 06 2013 00:00:00+0000 |     (null) |        4000 |
|     1 | 2013 | January, 07 2013 00:00:00+0000 |     (null) |        4000 |

Answer 2

以下是几个想法：

第一，您需要一些数据值进行比较，因此您可以构建一个新的表来静态地保存所有日期，然后对其进行外连接，以确保您得到的是零。

第二-我不确定mysql -但在Oracle中这是一个滞后函数。也许这对进一步的研究是一个有用的指针。

Answer 3

看看这个链接，这里解释了如何列出2个日期之间的所有日期Get a list of dates between two dates

您可以使用以下链接列出，但我认为在上面的链接中有更好的解决方案：

select a.Date 
from (
    select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY 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
where a.Date between '2012-12-01' and '2012-12-31';