时间戳

首页 > 解决方案 > 这是我对季度报告的查询，但没有出现所有季度，只出现 1 列

mysql - 这是我对季度报告的查询，但没有出现所有季度，只出现 1 列

问题描述

我有两张表格，一张是 Stations 表格，另一张是表格结果，现在我从我的表格中创建一个季度报告。它显示只有一列出现在季度报告中。

SELECT tbl1.record_no
     , tbl1.station_id AS ID
     , tbl2.name AS `Station Name`
     , tbl1.param_code AS Parameter
     , (CASE WHEN QUARTER(date)=1 THEN IF(param_code="FC" || param_code="TC",  EXP( SUM( LOG(res_value) )/count(`res_value`)), AVG(`res_value`)) ELSE 0 END) AS `Q1`
     , (CASE WHEN QUARTER(date)=2 THEN IF(param_code="FC" || param_code="TC",  EXP( SUM( LOG(res_value) )/count(`res_value`)), AVG(`res_value`)) ELSE 0 END) AS `Q2`
     , (CASE WHEN QUARTER(date)=3 THEN IF(param_code="FC" || param_code="TC",  EXP( SUM( LOG(res_value) )/count(`res_value`)), AVG(`res_value`)) ELSE 0 END) AS `Q3`
     , (CASE WHEN QUARTER(date)=4 THEN IF(param_code="FC" || param_code="TC",  EXP( SUM( LOG(res_value) )/count(`res_value`)), AVG(`res_value`)) ELSE 0 END) AS `Q4`
  FROM tbl_wq_results AS tbl1
  LEFT 
  JOIN tbl_wq_station AS tbl2
    ON tbl1.station_id = tbl2.id
 WHERE station_cat = "Inland River Stations"
 GROUP 
    BY param_code
     , station_id  
 ORDER 
    BY `Station Name` ASC

这是我的查询的结果，没有出现 Q1、Q2 的值，即使它有我的表 tbl_wq_result 的值。 这是结果

这是我的名为 tbl_wq_result 的表

这是我的表 tbl_wq_results

标签： mysqlsqldatetimepivot

解决方案

您的代码不是有效的标准聚合查询，因为selectandgroup by子句不一致。

我怀疑你想要的逻辑是：

SELECT 
    r.station_id AS ID,
    s.name AS `Station Name`,
    r.param_code AS Parameter,
    CASE WHEN r.param_code IN ('FC', 'TC')
        THEN EXP(SUM(CASE WHEN QUARTER(r.date) = 1 THEN LOG(res_value) END))
            / COUNT(CASE WHEN QUARTER(r.date) = 1 THEN r.res_value END))
        ELSE AVG(CASE WHN QUARTER(r.date) = 1 THEN res_value END) 
    END as Q1,
    CASE WHEN r.param_code IN ('FC', 'TC')
        THEN EXP(SUM(CASE WHEN QUARTER(r.date) = 2 THEN LOG(res_value) END))
            / COUNT(CASE WHEN QUARTER(r.date) = 2 THEN r.res_value END))
        ELSE AVG(CASE WHN QUARTER(r.date) = 2 THEN res_value END) 
    END as Q2,
    CASE WHEN r.param_code IN ('FC', 'TC')
        THEN EXP(SUM(CASE WHEN QUARTER(r.date) = 3 THEN LOG(res_value) END))
            / COUNT(CASE WHEN QUARTER(r.date) = 3 THEN r.res_value END))
        ELSE AVG(CASE WHN QUARTER(r.date) = 3 THEN res_value END) 
    END as Q3,
    CASE WHEN r.param_code IN ('FC', 'TC')
        THEN EXP(SUM(CASE WHEN QUARTER(r.date) = 4 THEN LOG(res_value) END))
            / COUNT(CASE WHEN QUARTER(r.date) = 4 THEN r.res_value END))
        ELSE AVG(CASE WHN QUARTER(r.date) = 4 THEN res_value END) 
    END as Q4
FROM tbl_wq_results AS r
INNER JOIN tbl_wq_station AS s ON r.station_id = s.id
WHERE station_cat = "Inland River Stations"
GROUP BY r.param_code, s.station_id  
ORDER BY `Station Name` ASC

这里的要点是，只有出现在GROUP BY子句中的列才允许出现在聚合函数之外。

我还更改了别名，使它们更有意义（并且更短），并且（尝试）将所有列与它们所属的表别名。

推荐阅读