mysql - 想在查询中添加if条件

Question

我正在创建一个查询来获取每个季度的数据。

我在 user_target 表 quarter1、quarter2、quarter3 中有 4 个字段和 quarter4每个季度有3个月，

quater1 - Jan Feb March
quater2 - Apr May June
quater3 - July Aug Sept
quater4 - Oct Nov Dec

现在，当我将 $P{month} 作为 Jan、Feb 或 March 传递时，我应该获得第 1 季度的数据。同样，当我将 $P{month} 作为 Apr、May 或 June 传递时，我应该获取 quarter2 等的数据。我不想为不同的月份编写 4 个不同的查询。可以在单个查询本身中实现吗？

我对 quater1 的查询如下:

select    sum(t.quarter1) as quarter ,
          s.name as sname, f.name as fname, m.name as pname 
from user_target t, res_salesteam s, 
     account_fiscalyear f,res_users u, res_users ru, account_month m
where t.salesteam_id = s.id and 
      t.fiscal_year = f.id and  
      f.id = $P{fiscalyear} and 
      p.fiscalyear_id = f.id and 
      m.id = $P{month} and 
      u.parent_id = ru.id and 
      ru.id = $P{name} and (u.tri_salesteam = s.id or ru.tri_salesteam = u.id)
group by s.name, f.name, m.name order by s.name

Answer 1

您可以使用 CASE ... END逻辑构造

SELECT sum(CASE
    WHEN $P{month} IN ('Jan','Feb','March') THEN t.quarter1
    WHEN $P{month} IN ('Apr','May','June') THEN t.quarter2
    WHEN $P{month} IN ('July','Aug','Sept') THEN t.quarter3
    WHEN $P{month} IN ('Oct','Nov','Dec') THEN t.quarter4
    ELSE 0 END) AS quarter,
    s.name as sname, f.name as fname, m.name as pname
  FROM ...