sql - CASE 语句中关于 WHEN 的 GROUP BY 问题
问题描述
我试图让间接和直接时间都显示在一行上,我知道由于 group by 语句而显示了多行。但是,我尝试删除分组并得到一个错误,即删除时 l.CHG_TYPE 未包含在聚合中。
<html><table><body><tr><th>ID</th><th>Billing Period</th><th>Shop</th><th>Shift Code</th><th>Employee #</th><th>Indirect Hours</th><th>Direct Hours</th></tr><tr class="odd"><td>202001404223396</td><td>202001</td><td>Sanitation</td><td>1st</td><td>23396</td><td> </td><td>12.73</td></tr>
<tr><td>202001404223396</td><td>202001</td><td>Sanitation</td><td>1st</td><td>23396</td><td>6.41</td><td> </td></tr>
<tr class="odd"><td>202001205225499</td><td>202001</td><td>Car Shop</td><td>1st</td><td>25499</td><td> </td><td>12.4</td></tr>
<tr><td>202001205225499</td><td>202001</td><td>Car Shop</td><td>1st</td><td>25499</td><td>6.82</td><td> </td></tr>
</table></body></html>
SELECT
CONCAT(l.FISC_PD, l.LOCATION, l.SHIFT_CODE, e.EmpNo) 'ID',
l.FISC_PD 'Billing Period',
CASE
WHEN l.LOCATION = '202' THEN 'Specialty Equipment'
WHEN l.LOCATION = '205' THEN 'Car Shop'
WHEN l.LOCATION = '206' THEN 'Truck Shop'
WHEN l.LOCATION = '404' THEN 'Sanitation'
WHEN l.LOCATION = '212' THEN 'Tire Shop'
ELSE l.LOCATION
END as 'Shop',
CASE
WHEN l.SHIFT_CODE in ('2','6') THEN '1st'
WHEN l.SHIFT_CODE in ('3','4','5') THEN '2nd'
ELSE NULL
END as 'Shift Code',
e.EmpNo 'Employee #',
CASE
WHEN l.CHG_TYPE = 'I' THEN ROUND((SUM(l.DURATION)/36000000),2)
ELSE NULL
END as 'Indirect Hours',
CASE
WHEN l.CHG_TYPE = 'D' THEN ROUND((SUM(l.DURATION)/36000000),2)
ELSE NULL
END as 'Direct Hours'
FROM m5prod.mfive.VIEW_EMP_LABOR_JNL l
LEFT JOIN m5prod.mfive.VIEW_ALL_EMPLOYEES e on e.EmpID = l.EMP_ID
WHERE
l.location not in ('218', '201')
and l.FISC_PD in ('202001', '202002', '202003')
GROUP BY
l.FISC_PD,
e.EmpNo,
l.LOCATION,
l.SHIFT_CODE,
l.CHG_TYPE
解决方案
您可以按如下方式使用条件聚合:
CHG_TYPE
从GROUP BY
子句中删除- 将
CASE
表达式移到_SUM()
SELECT
CONCAT(l.FISC_PD, l.LOCATION, l.SHIFT_CODE, e.EmpNo) 'ID',
l.FISC_PD 'Billing Period',
CASE
WHEN l.LOCATION = '202' THEN 'Specialty Equipment'
WHEN l.LOCATION = '205' THEN 'Car Shop'
WHEN l.LOCATION = '206' THEN 'Truck Shop'
WHEN l.LOCATION = '404' THEN 'Sanitation'
WHEN l.LOCATION = '212' THEN 'Tire Shop'
ELSE l.LOCATION
END as 'Shop',
CASE
WHEN l.SHIFT_CODE in ('2','6') THEN '1st'
WHEN l.SHIFT_CODE in ('3','4','5') THEN '2nd'
ELSE NULL
END as 'Shift Code',
e.EmpNo 'Employee #',
ROUND(SUM(CASE WHEN l.CHG_TYPE = 'I' THEN l.DURATION END)/36000000),2) as 'Indirect Hours',
ROUND(SUM(CASE WHEN l.CHG_TYPE = 'D' THEN l.DURATION END)/36000000),2) as 'Direct Hours'
FROM m5prod.mfive.VIEW_EMP_LABOR_JNL l
LEFT JOIN m5prod.mfive.VIEW_ALL_EMPLOYEES e on e.EmpID = l.EMP_ID
WHERE
l.location not in ('218', '201')
and l.FISC_PD in ('202001', '202002', '202003')
GROUP BY
l.FISC_PD,
e.EmpNo,
l.LOCATION,
l.SHIFT_CODE
旁注:对列别名使用单引号不是一个好习惯。在标准 SQL 中,单引号保留给字符串字面量。我建议为您的数据库使用相关的引号字符(MySQL:反引号 - SQL Server:方括号 - Postgres 和 Oracle:双引号)。更好的是,您可以使用不包含特殊字符的列别名,因此不需要引用它们。
推荐阅读
- jsf - 当 bean 方法响应时间较长时,CommandButton 操作执行两次
- javascript - 尝试访问 cypress 上的网站时出错“未定义 Vue”
- typescript - 仅将函数参数类型限制为具有特定类型的接口的键
- android - 我想实施 OPI Terminal e 支付系统,我该怎么做?
- rust - 检查提供的字符串是否为单个路径组件
- python - 将时间戳信息转换为会话信息 Python
- javascript - FindIndex() 从 redux 存储中删除项目
- php - Wordpress 更改未显示在我的网站上
- android - OpenGL checkGlError("glDrawArrays") 抛出 UncaughtExceptionHandler
- regex - 正则表达式在第 n 个特殊字符和字符串之间进行选择