MySQL 获取当年的季度 (QTD) 1、2、3、4

Question

我想知道是否可以使用第 1、2、3 和 4 季度的结果进行查询。例如，今年(现在是 2017 年):

╔═══════════╦════════╗════════╗════════╗
║     Q1    ║   Q2   ║   Q3   ║   Q4   ║
╠═══════════╬════════╣════════║════════║
║   € K54   ║ € K14  ║ € K0   ║  € K0  ║
╚═══════════╩════════╝════════╝════════╝ 

这就是我现在做的:(这个查询中的 Q1 是当前的，所以当它是 Q2 时它会更改为 Q2。我不想像上面的例子那样单独显示它们。)

╔══════════════╦
║     Q1       ║
╠══════════════╬
║   € K54      ║
╚══════════════╩

这是我提出的查询。如果没有找到结果，它会自动显示 € K0。

SELECT IF(SUM(totalExcl) IS NULL,0,
          CONCAT('€ K',
                FORMAT((SUM(totalExcl) / 1000),
                    0,'nl_NL'))) AS Q1
FROM
    ex.ps_order o LEFT JOIN
    ex.ps_oxo_quotation q ON o.id_order = q.idOrder LEFT JOIN
    ex.ps_employee e ON q.idEmployee = e.id_employee
WHERE
    QUARTER(q.date_add) = QUARTER(UTC_TIMESTAMP())
       AND e.lastname IN ('Ver')

我知道如何进行上一季度查询和当前季度查询。但不完全像当年的Q1,Q2,Q3,Q4。

---

我差不多满意了我现在得到这个结果:

╔═════════╦════════╗════════╗════════╗
║    Q1   ║   Q2   ║   Q3   ║   Q4   ║
╠═════════╬════════╣════════║════════║
║€96086,03║ € 0,00 ║ € 0,00 ║ € 0,00 ║
╚═════════╩════════╝════════╝════════╝ 

有了这个查询:(感谢Bennjoe Mordeno)

     SELECT 
 CONCAT('€ ',FORMAT(+ CAST(SUM(CASE WHEN QUARTER(q.date_add) = 1 THEN totalExcl ELSE 0 END) AS CHAR(100)), 2, 'nl_NL')) as Q1,
 CONCAT('€ ',FORMAT(+ CAST(SUM(CASE WHEN QUARTER(q.date_add) = 2 THEN totalExcl ELSE 0 END) AS CHAR(100)), 2, 'nl_NL')) as Q2,
 CONCAT('€ ',FORMAT(+ CAST(SUM(CASE WHEN QUARTER(q.date_add) = 3 THEN totalExcl ELSE 0 END) AS CHAR(100)), 2, 'nl_NL')) as Q3,
 CONCAT('€ ',FORMAT(+ CAST(SUM(CASE WHEN QUARTER(q.date_add) = 4 THEN totalExcl ELSE 0 END) as CHAR(100)), 2, 'nl_NL')) as Q4
     FROM

要格式化使用这个:

 SELECT 
 CONCAT('€ K',FORMAT(+ CAST(SUM(CASE WHEN QUARTER(q.date_add) = 1 THEN totalexcl ELSE 0 END)/1000 AS CHAR(100)), 0, 'nl_NL')) as Q1,
 CONCAT('€ K',FORMAT(+ CAST(SUM(CASE WHEN QUARTER(q.date_add) = 2 THEN totalexcl ELSE 0 END)/1000 AS CHAR(100)), 0, 'nl_NL')) as Q2,
 CONCAT('€ K',FORMAT(+ CAST(SUM(CASE WHEN QUARTER(q.date_add) = 3 THEN totalexcl ELSE 0 END)/1000 AS CHAR(100)), 0, 'nl_NL')) as Q3,
 CONCAT('€ K',FORMAT(+ CAST(SUM(CASE WHEN QUARTER(q.date_add) = 4 THEN totalexcl ELSE 0 END)/1000 AS CHAR(100)), 0, 'nl_NL')) as Q4
 FROM

结果:

╔═════════╦════════╗════════╗════════╗
║    Q1   ║   Q2   ║   Q3   ║   Q4   ║
╠═════════╬════════╣════════║════════║
║ € K96   ║  € 0   ║  € 0   ║  € 0   ║
╚═════════╩════════╝════════╝════════╝ 

Answer 1

您可以在以下情况下使用

 SELECT 
 '€ K' + CAST(SUM(CASE WHEN QUARTER(q.date_add)= 1 THEN totalexcl ELSE 0 END) AS VARCHAR(100)) as Q1,
 '€ K' + CAST(SUM(CASE WHEN QUARTER(q.date_add) = 2 THEN totalexcl ELSE 0 END) AS VARCHAR(100)) as Q2,
 '€ K' + CAST(SUM(CASE WHEN QUARTER(q.date_add)= 3 THEN totalexcl ELSE 0 END) AS VARCHAR(100)) as Q3,
 '€ K' + CAST(SUM(CASE WHEN QUARTER(q.date_add)= 4 THEN totalexcl ELSE 0 END) as VARCHAR(100)) as Q4
 FROM
  ex.ps_order o LEFT JOIN
  ex.ps_oxo_quotation q ON o.id_order = q.idOrder LEFT JOIN
  ex.ps_employee e ON q.idEmployee = e.id_employee
  WHERE e.lastname IN ('Ver')