MySQL查询跳转记录

Question

我得到了以下查询。

SELECT month(DMY) Month, 
  Year(DMY) Year, 
  Date(DMY) Date,
  COD_VENDED,
  @running_total := @running_total + Coalesce(TotalImport, 0) as TotalImport
from
(
  SELECT Days.dmy,
    g.COD_VENDED,
    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())
) src, (SELECT @running_total := 0 AS dummy) dummy

问题是它会跳过数据库中的记录。

该问题在此SQL Fiddle中重复出现

Answer 1

看起来问题出在你的内部子查询上：

select FECHA_EMIS,
  COD_VENDED,
  sum(IMPORTE) TotalImport
from GVA12
group by Date(FECHA_EMIS), Year(FECHA_EMIS)

您只对Date()和Year()进行分组，因此COD_VENDED的值将不会是恒定的。

然后，当您进入查询的下一部分时，使用以下命令仅筛选记录：

 g.COD_VENDED='EX'

因此，如果子查询返回的值不是EX，那么它们将被过滤掉。

您可能希望在子查询中考虑按COD_VENDED分组：

SELECT month(DMY) Month, 
  Year(DMY) Year, 
  Date(DMY) Date,
  COD_VENDED,
  @running_total := @running_total + Coalesce(TotalImport, 0) as TotalImport
from
(
  SELECT Days.dmy,
    g.COD_VENDED,
    TotalImport
  FROM Days
  left join
  (
    select FECHA_EMIS,
      COD_VENDED,
      sum(IMPORTE) TotalImport
    from GVA12
    group by Date(FECHA_EMIS), Year(FECHA_EMIS), COD_VENDED
  ) 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())
) src, (SELECT @running_total := 0 AS dummy) dummy

请参阅SQL Fiddle with Demo