mysql - 我如何过滤掉最新的运行并将行交叉制表到列中？

Question

| product | tests | runs | results |
|---------|-------|------|---------|
| A       | AD    | 1    | 12      |
| A       | AD    | 2    | 13      |
| A       | AD    | 3    | 14      |
| A       | SS    | 1    | 12      |
| A       | TD    | 1    | 12      |
| A       | TD    | 2    | 12      |
| B       | AD    | 1    | 11      |
| B       | SS    | 1    | 12      |
| c       | AD    | 1    | 12      |
| c       | AD    | 2    | 10      |
| D       | AD    | 1    | 16      |
| D       | SS    | 1    | 12      |

我使用了这个查询:

select DISTINCT Poduct,
    SUM (case param_name when 'AD' then results ELSE 0 END) AS AD,
    SUM (case param_name when 'SS' then results ELSE 0 END) AS SS,
    SUM (case param_name when 'TD' then results ELSE 0 END) AS TD
FROM [product]
GROUP BY product
ORDER BY product

要以这种格式获取它:

| PRODUCT | AD | SS | TD |
|---------|----|----|----|
| A       | 39 | 12 | 24 |
| B       | 11 | 12 | 0  |
| C       | 22 | 0  | 0  |
| D       | 16 | 12 | 0  |

我需要这种格式，但问题是它会将 AD、SS 和 TD 上的所有测试运行相加。

我正在寻找的是这样的:

| PRODUCT | AD | SS | TD |
|---------|----|----|----|
| A       | 14 | 12 | 12 |
| B       | 11 | 12 | 0  |
| C       | 10 | 0  | 0  |
| D       | 16 | 12 | 0  |

这只是从更大规模的测试中提取结果。有人可以帮忙吗？

Answer 1

试试这个:

select product, max(ad) ad, max(ss) ss, max(td) td
from (
  select Product,
      MAX(case tests when 'AD' then results ELSE 0 END) AS AD,
      MAX(case tests when 'SS' then results ELSE 0 END) AS SS,
      MAX(case tests when 'TD' then results ELSE 0 END) AS TD
  FROM product
  GROUP BY product, tests
) test_reports
group by product
ORDER BY product;

演示 @ SQL Fiddle