mysql - IFNULL 查询给出 2 行相同的结果

Question

我想在我的项目中做一份年度报告。我很难让这个查询返回 1 行中的计数。如果没有不同年份的交易(如本例中的 2016 年)，结果将显示 1 行。

当我在执行此代码时插入与“今年”不同的年份的交易时，结果显示 2 行具有相同的结果:

select
  ifnull((SELECT COUNT(id) FROM (transaction)WHERE((Month(date)=1)AND (YEAR(date)='2017'))),0) AS `Jan`,
  ifnull((SELECT COUNT(id) FROM (transaction)WHERE((Month(date)=2)AND (YEAR(date)='2017'))),0) AS `Feb`,
  ifnull((SELECT COUNT(id) FROM (transaction)WHERE((Month(date)=3)AND (YEAR(date)='2017'))),0) AS `Mar`,
  ifnull((SELECT COUNT(id) FROM (transaction)WHERE((Month(date)=4)AND (YEAR(date)='2017'))),0) AS `Apr`,
  ifnull((SELECT COUNT(id) FROM (transaction)WHERE((Month(date)=5)AND (YEAR(date)='2017'))),0) AS `May`,
  ifnull((SELECT COUNT(id) FROM (transaction)WHERE((Month(date)=6)AND (YEAR(date)='2017'))),0) AS `Jun`,
  ifnull((SELECT COUNT(id) FROM (transaction)WHERE((Month(date)=7)AND (YEAR(date)='2017'))),0) AS `Jul`,
  ifnull((SELECT COUNT(id) FROM (transaction)WHERE((Month(date)=8)AND (YEAR(date)='2017'))),0) AS `Aug`,
  ifnull((SELECT COUNT(id) FROM (transaction)WHERE((Month(date)=9)AND (YEAR(date)='2017'))),0) AS `Sep`,
  ifnull((SELECT COUNT(id) FROM (transaction)WHERE((Month(date)=10)AND (YEAR(date)='2017'))),0) AS `Oct`,
  ifnull((SELECT COUNT(id) FROM (transaction)WHERE((Month(date)=11)AND (YEAR(date)='2017'))),0) AS `Nov`,
  ifnull((SELECT COUNT(id) FROM (transaction)WHERE((Month(date)=12)AND (YEAR(date)='2017'))),0) AS `Dec`
from transaction GROUP BY YEAR(date) 

有什么方法可以修复这个或其他可以使其正常工作的查询吗？

Answer 1

如果你只想要 2017 年的数据，你应该在 where 中过滤

select
      ifnull((SELECT COUNT(id) FROM (transaction)WHERE((Month(date)=1)AND (YEAR(date)='2017'))),0) AS `Jan`,
      ifnull((SELECT COUNT(id) FROM (transaction)WHERE((Month(date)=2)AND (YEAR(date)='2017'))),0) AS `Feb`,
      ifnull((SELECT COUNT(id) FROM (transaction)WHERE((Month(date)=3)AND (YEAR(date)='2017'))),0) AS `Mar`,
      ifnull((SELECT COUNT(id) FROM (transaction)WHERE((Month(date)=4)AND (YEAR(date)='2017'))),0) AS `Apr`,
      ifnull((SELECT COUNT(id) FROM (transaction)WHERE((Month(date)=5)AND (YEAR(date)='2017'))),0) AS `May`,
      ifnull((SELECT COUNT(id) FROM (transaction)WHERE((Month(date)=6)AND (YEAR(date)='2017'))),0) AS `Jun`,
      ifnull((SELECT COUNT(id) FROM (transaction)WHERE((Month(date)=7)AND (YEAR(date)='2017'))),0) AS `Jul`,
      ifnull((SELECT COUNT(id) FROM (transaction)WHERE((Month(date)=8)AND (YEAR(date)='2017'))),0) AS `Aug`,
      ifnull((SELECT COUNT(id) FROM (transaction)WHERE((Month(date)=9)AND (YEAR(date)='2017'))),0) AS `Sep`,
      ifnull((SELECT COUNT(id) FROM (transaction)WHERE((Month(date)=10)AND (YEAR(date)='2017'))),0) AS `Oct`,
      ifnull((SELECT COUNT(id) FROM (transaction)WHERE((Month(date)=11)AND (YEAR(date)='2017'))),0) AS `Nov`,
      ifnull((SELECT COUNT(id) FROM (transaction)WHERE((Month(date)=12)AND (YEAR(date)='2017'))),0) AS `Dec`
     from transaction 
     where YEAR(date)='2017'
    GROUP BY YEAR(date) 

否则，对于您在数据中拥有的每一个，都会执行 ifnull 中的相同选择(与 2017 相关)并返回相同的值

您可以避免无用的 () 和子选择，例如:

select
      sum(case when month(date)=1 and YEAR(date) = '2017' then 1 else 0 end ) as 'Jan'
      sum(case when month(date)=2 and YEAR(date) = '2017' then 1 else 0 end ) as 'Feb'
      ......
      sum(case when month(date)=12 and YEAR(date) = '2017' then 1 else 0 end ) as 'Dec'
 from transaction
  where YEAR(date)='2017'
  GROUP BY YEAR(date)