以不同方式显示查询结果

Question

我有一个疑问：

SELECT pr_products.product AS PRODUCT, pr_varieties.variety AS VARIETY, pr_grades.GRADE, SUM(pf_harvest.quantity) AS QUANTITY
FROM pf_harvest
INNER JOIN pf_performance ON pf_performance.id = pf_harvest.id_performance
INNER JOIN pr_products ON pr_products.id = pf_harvest.id_product
INNER JOIN pr_varieties ON pr_varieties.id = pf_harvest.id_variety
INNER JOIN pr_grades ON pr_grades.id = pf_harvest.id_grade
WHERE pf_performance.status = 100
AND pf_harvest.id_tenant = 1
AND pf_harvest.date = '2017-03-22'
GROUP BY pf_harvest.id_product, pf_harvest.id_variety, pf_harvest.id_grade
ORDER BY pf_harvest.id_product, pr_varieties.variety, pf_harvest.id_grade;

它显示了以下结果：

-------------------------------------------------------------------
   PRODUCT     |     VARIETY     |     GRADE      |     QUANTITY  |
-------------------------------------------------------------------
   ROSE        |     ROSEV1      |     GRADE1     |     1000      |
-------------------------------------------------------------------
   ROSE        |     ROSEV1      |     GRADE2     |     5000      |
-------------------------------------------------------------------
   ROSE        |     ROSEV2      |     GRADE1     |     2000      |
-------------------------------------------------------------------
   ROSE1       |     ROSE1V1     |     GRADE1     |     3500      |
-------------------------------------------------------------------

是否可以按以下方式显示查询结果？

-------------------------------------------------------------------
  PRODUCT     |   VARIETY   |  GRADE1  |  GRADE2 |  TOTAL         |  
-------------------------------------------------------------------
   ROSE       |   ROSEV1    |  1000    |  5000   |  6000          |
-------------------------------------------------------------------
   ROSE       |   ROSEV2    |  2000    |    0    |  2000          |
-------------------------------------------------------------------
   ROSE1      |   ROSE1V1   |  3500    |    0    |  3500          |
-------------------------------------------------------------------

我试图更改查询，但我不能，我想知道是否可能，我希望有人能帮助我。

更新的注释:查询结果中可能有更多的分数(GRADE1、GRADE2、GRADE3.)。

谢谢!

Answer 1

修改当前的查询，不要在GRADE列上分组，而是在该列上支点。然后，使用条件聚合计算GRADE1和GRADE2列。

SELECT t3.product AS PRODUCT,
       t4.variety AS VARIETY,
       SUM(CASE WHEN t5.GRADE = 'GRADE1' THEN t1.quantity ELSE 0 END) AS GRADE1,
       SUM(CASE WHEN t5.GRADE = 'GRADE2' THEN t1.quantity ELSE 0 END) AS GRADE2,
       SUM(CASE WHEN t5.GRADE = 'GRADE3' THEN t1.quantity ELSE 0 END) AS GRADE3,
       -- hopefully it is clear how to add more grades
       SUM(t1.quantity) AS TOTAL
FROM pf_harvest t1
INNER JOIN pf_performance t2 
    ON t2.id = t1.id_performance
INNER JOIN pr_products t3
    ON t3.id = pf_harvest.id_product
INNER JOIN pr_varieties t4
    ON t4.id = t1.id_variety
INNER JOIN pr_grades t5
    ON t5.id = t1.id_grade
WHERE t2.status = 100 AND
      t1.id_tenant = 1 AND
      t1.date = '2017-03-22'
GROUP BY t1.id_product,
         t1.id_variety
ORDER BY t1.id_product,
         t4.variety,
         t1.id_grade;

Answer 2

尝尝这个

添加了以下代码

sum(case when pr_grades.Grade='Grade1' then pf_harvest.quantity else 0 end)) [Grade1],
      sum(case when pr_grades.Grade='Grade2' then pf_harvest.quantity else 0 end)) [Grade2]

SELECT
  pr_products.product AS PRODUCT,
  pr_varieties.variety AS VARIETY,
  sum(case when pr_grades.Grade='Grade1' then pf_harvest.quantity else 0 end)) [Grade1],
  sum(case when pr_grades.Grade='Grade2' then pf_harvest.quantity else 0 end)) [Grade2],
  SUM(pf_harvest.quantity) AS TOTAL
FROM pf_harvest
INNER JOIN pf_performance
  ON pf_performance.id = pf_harvest.id_performance
INNER JOIN pr_products
  ON pr_products.id = pf_harvest.id_product
INNER JOIN pr_varieties
  ON pr_varieties.id = pf_harvest.id_variety
INNER JOIN pr_grades
  ON pr_grades.id = pf_harvest.id_grade
WHERE pf_performance.status = 100
AND pf_harvest.id_tenant = 1
AND pf_harvest.date = '2017-03-22'
GROUP BY pf_harvest.id_product,
         pf_harvest.id_variety
ORDER BY pf_harvest.id_product, pr_varieties.variety, pf_harvest.id_grade;