mysql - 计算与连接表不匹配

Question

我的查询有问题，通过连接多个表使用求和或计数数据，查询结果不应该匹配，结果相乘，使用连接多个表计算数据时我应该考虑什么，它就足以让我困惑了

像这样:

我有 4 个互连的表

select T3.series,  
sum(If(Month = 1 and Year = 2013,jumlah,0)) as 'JAN 13', 
sum(If(Month = 2 and Year = 2013,jumlah,0)) as 'FEB 13', 
sum(If(Month = 3 and Year = 2013,jumlah,0)) as 'MAR 13'
From Typemotor T3
LEFT JOIN (
select T.Series,Month(C.Date) as Month,Year(C.Date) as Year,
Count(T.Series) as Jumlah
from 
(select a.Type, a.Date,a.MachineNumber
from 
selling_cash a
left join lokasi b
on a.Showr = b.KodeLokasi
where b.PT = 'PT MERDEKA'
group by a.MachineNumber)
as C
Left Join Typeconvert T2
on C.Type = T2.TypeSystem
Left Join Typemotor T
on T2.TypeConv = T.Type
Group by T.Series,Month,Year ) as R
On R.Series = T3.Series
Group by T3.Series order by T3.Series asc;

结果是:

+--------------------+--------+--------+--------+  
| series             | JAN 13 | FEB 13 | MAR 13 |
+--------------------+--------+--------+--------+
| AUTOMATIC LPM      |  13068 |   9416 |  10648 |
| AUTOMATIC PREMIUM  |    187 |    506 |    330 |
| AUTOMATIC STANDART |  11502 |  12690 |  10206 |
| JUPITER SERIES     |   3584 |   3360 |   3360 |
| LEXAM              |      0 |      0 |      0 |
| MX SERIES          |   3456 |   1664 |   3200 |
| SPORT              |   9620 |   7852 |   7826 |
| VEGA SERIES        |   2808 |   1728 |   1080 |
+--------------------+--------+--------+--------+

结果应该是

+--------------------+--------+--------+--------+
| series             | JAN 13 | FEB 13 | MAR 13 |
+--------------------+--------+--------+--------+
| AUTOMATIC LPM      |     33 |     28 |     38 |
| AUTOMATIC PREMIUM  |      3 |      9 |      6 |
| AUTOMATIC STANDART |     35 |     41 |     32 |
| JUPITER SERIES     |     22 |     20 |     20 |
| LEXAM              |      0 |      0 |      0 |
| MX SERIES          |     27 |     13 |     25 |
| SPORT              |     80 |     64 |     64 |
| VEGA SERIES        |     13 |      8 |      5 |
+--------------------+--------+--------+--------+

是否有任何建议，指出需要考虑什么来执行连接表的计算/求和，以便结果正确？

Answer 1

我不是 mysql 专家，但我会对您的查询进行一些观察。

首先，我看不出“group by a.MachineNumber”子句中没有任何意义，因为该查询不包含聚合函数。

为了清楚起见，我在这里重复您的查询:

select T3.series,  
  sum(If(Month = 1 and Year = 2013,jumlah,0)) as 'JAN 13', 
  sum(If(Month = 2 and Year = 2013,jumlah,0)) as 'FEB 13', 
  sum(If(Month = 3 and Year = 2013,jumlah,0)) as 'MAR 13'
From Typemotor T3
  LEFT JOIN (select T.Series, Month(C.Date) as Month, Year(C.Date) as Year, Count(T.Series) as Jumlah
        from (
          select a.Type, a.Date, a.MachineNumber 
          from selling_cash a left join lokasi b on a.Showr = b.KodeLokasi
          where b.PT = 'PT MERDEKA'
          group by a.MachineNumber
        ) as C
          Left Join Typeconvert T2 on C.Type = T2.TypeSystem
          Left Join Typemotor T on T2.TypeConv = T.Type
        Group by T.Series, Month, Year
  ) as R On R.Series = T3.Series
Group by T3.Series 
order by T3.Series asc;

在我看来，Typeconvert 和 Typemotor 的左连接都可以移到 C 的定义中，并且由于您只对 2013 年第 1-3 个月的数据感兴趣，因此可以将这些条件添加到 where C 中的子句，此时 C 需要提供给父查询的数据减少为 Series、MonthNumber 和 Jumlah:

select T3.series,  
  sum(If(Month = 1 and Year = 2013,jumlah,0)) as 'JAN 13', 
  sum(If(Month = 2 and Year = 2013,jumlah,0)) as 'FEB 13', 
  sum(If(Month = 3 and Year = 2013,jumlah,0)) as 'MAR 13'
From Typemotor T3
  LEFT JOIN (
    select Series, MonthNumber, Count(*) as Jumlah
    from (
      select T.Series, Month(a.Date) as MonthNumber 
      from selling_cash a 
        left join lokasi b on a.Showr = b.KodeLokasi
        left Join Typeconvert T2 on C.Type = T2.TypeSystem
        left Join Typemotor T on T2.TypeConv = T.Type
      where b.PT = 'PT MERDEKA' and year(a.Date) = 2013 and month(a.date) <= 3
    ) as C
  Group by Series, MonthNumber
  ) as R On R.Series = T3.Series
Group by T3.Series 
order by T3.Series asc;

但是现在内部查询 (R) 没有生成您想要的数据吗？还需要总结吗？

由于您尚未提供您所拥有的表定义以及其中包含的示例数据，因此我无法回答这个问题。但我认为您需要考虑为什么您期望的答案不是您的查询给出的答案。

我认为更有可能的是逻辑不太正确，而不是有一些未知。

因此我认为最终的查询应该是:

select T3.series,  
  If(MonthNumber = 1,jumlah,0) as 'JAN 13', 
  If(MonthNumber = 2,jumlah,0) as 'FEB 13', 
  If(MonthNumber = 3,jumlah,0) as 'MAR 13'
From Typemotor T3
  LEFT JOIN (
    select Series, MonthNumber, Count(*) as Jumlah
    from (
      select T.Series, Month(a.Date) as MonthNumber 
      from selling_cash a 
        left join lokasi b on a.Showr = b.KodeLokasi
        left Join Typeconvert T2 on C.Type = T2.TypeSystem
        left Join Typemotor T on T2.TypeConv = T.Type
      where b.PT = 'PT MERDEKA' and year(a.Date) = 2013 and month(a.date) <= 3
    ) as C
  Group by Series, MonthNumber
  ) as R On R.Series = T3.Series
order by T3.Series asc;