sql - 从选择中的选择中删除空值
问题描述
我正在使用一个查询来查询员工每周的总小时数。我在一个选择中使用一个选择来将几周分成几列。我收到一些员工的 Null。如何删除空值
select distinct
EM.EMPLOYEE,
RTRIM(LTRIM(LAST_NAME))+', '+RTRIM(LTRIM(FIRST_NAME)) as [FULL NAME],
(Select SUM(ISNULL(HOURS,'0')) from TimeTbl PT2 WHERE DATEPART(WK, Date) = DATEPART(WK, DATEADD(WK, -1, getdate())) and PAYROLL_YEAR='2020'and PT2.EMPLOYEE=PT.EMPLOYEE) AS [WEEK 1 HOURS],
(Select SUM(ISNULL(HOURS,'0')) from TimeTbl PT2 WHERE DATEPART(WK, Date) = DATEPART(WK, DATEADD(WK, -2, getdate())) and PAYROLL_YEAR='2020'and PT2.EMPLOYEE=PT.EMPLOYEE) AS [WEEK 2 HOURS],
(Select SUM(ISNULL(HOURS,'0')) from TimeTbl PT2 WHERE DATEPART(WK, Date) = DATEPART(WK, DATEADD(WK, -3, getdate())) and PAYROLL_YEAR='2020'and PT2.EMPLOYEE=PT.EMPLOYEE) AS [WEEK 3 HOURS],
(Select SUM(ISNULL(HOURS,'0')) from TimeTbl PT2 WHERE DATEPART(WK, Date) = DATEPART(WK, DATEADD(WK, -4, getdate())) and PAYROLL_YEAR='2020' and PT2.EMPLOYEE=PT.EMPLOYEE)AS [WEEK 4 HOURS],
(Select SUM(ISNULL(HOURS,'0')) from TimeTbl PT2 WHERE DATEPART(WK, TR_DATE) = DATEPART(WK, DATEADD(WK, -5, getdate())) and PAYROLL_YEAR='2020' and PT2.EMPLOYEE=PT.EMPLOYEE)AS [WEEK 5 HOURS]
From TimeTbl PT
right join EMPLYTBL EM on PT.EMPLOYEE=EM.EMPLOYEE
解决方案
使用条件聚合:
SELECT EM.EMPLOYEE, RTRIM(LTRIM(LAST_NAME))+', '+RTRIM(LTRIM(FIRST_NAME)) as [FULL NAME],
SUM(CASE WHEN DATEPART(WK, Date) = DATEPART(WK, DATEADD(WK, -1, getdate())) and PAYROLL_YEAR = 2020) THEN HOURS END) AS [WEEK 1 HOURS],
SUM(CASE WHEN DATEPART(WK, Date) = DATEPART(WK, DATEADD(WK, -2, getdate())) and PAYROLL_YEAR = 2020) THEN HOURS END) AS [WEEK 2 HOURS],
. . .
From EMPLYTBL EM left join
TimeTbl PT
ON PT.EMPLOYEE = E.EMPLOYEE
GROUP BY EM.EMPLOYEE, RTRIM(LTRIM(LAST_NAME))+', '+RTRIM(LTRIM(FIRST_NAME))
推荐阅读
- node.js - Instagram api 如果帐户是企业帐户 nodejs
- python - 使用 Python 从图像的背景中提取相同颜色的前景
- javascript - Javascript测验,点击时显示正确或错误答案颜色
- google-apps-script - 当在谷歌表上进行编辑时,有没有办法推送消息(LINE 应用程序)?
- c# - 是否可以以编程方式使用 Outlook 的“语言”设置?
- rrdtool - RRDTool:数据库和图表中出现奇怪的“飞溅”
- google-authentication - Google 登录 - “空白页” - 请稍等
- c++ - 为什么除非我声明一个全局实例,否则我的结构符号不会加载到 VS 调试器中?
- mysql - 选择更新以跳过锁定不起作用
- azure - 在 Azure API 管理中查看已发布的无隐私浏览模式的开发者门户