在select中从select中删除null

jutyujz0 于 2021-07-29 发布在 Java

关注(0)|答案(1)|浏览(627)

我正在使用一个查询来查询员工每周的总工时。我在select中使用select将周分隔为列。我收到一些雇员的空票。如何删除空值

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

sql sql-server

来源：https://stackoverflow.com/questions/62285325/remove-nulls-from-select-in-a-select

1条答案

按热度按时间

guykilcj1#

使用条件聚合：

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))

赞(0）回复(0）举报 2021-07-29

我来回答

在select中从select中删除null

1条答案

相关问题

热门标签

最新问答