sql-server - 联合查询删除空白行

Question

我正在尝试合并两个不同的结果，但我不确定如何删除空白值。paid_date 和 check_date 有时是不同的。我需要做什么来修改代码？我试过做MAX，但似乎没有用。谢谢。

结果：

我想要的是：

WITH PAID AS
(
SELECT PIDATE
,SUM(PAY) AS PAID
,ROW_NUMBER() OVER(ORDER BY PAYDATE DESC) AS ROW_NUM
FROM CLAIM
WHERE PAY<>0
GROUP BY PAYDATE
)
,TOTAL_PAID AS
(
SELECT CONVERT(DATE,CONVERT(VARCHAR(10),PAYDATE)) AS PAID_DATE
,FORMAT(PAID,'C','EN-US') AS PAID
FROM PAID
WHERE ROW_NUM <= 10
)

,CHECKS AS
(
SELECT DISTINCT CHECK_DATE
,SUM(PAYMENT) AS PAYMENT
,ROW_NUMBER() OVER(ORDER BY CHECK_DATE DESC) AS ROW_NUM
FROM CR 
GROUP BY CHECK_DATE
)
,TOTAL_CHECKS AS
(
SELECT CONVERT(DATE,CONVERT(VARCHAR(10),CHECK_DATE)) AS CHECK_DATE
,FORMAT(PAYMENT,'C','EN-US') AS PAYMENT
FROM CHECKS
WHERE ROW_NUM <= 10
)

,FINAL AS
(
SELECT PAID_DATE
,PAID
,'' AS CHECK_DATE
,'' AS PAYMENT
FROM TOTAL_PAID

UNION ALL

SELECT '' AS PAID_DATE
,'' AS PAID
,CHECK_DATE 
,PAYMENT 
FROM TOTAL_CHECKS
)

SELECT *
FROM FINAL

标签： sql-server

Answer 1

在这种情况下，您不得使用union. 你必须join改用。

尝试加入两个序列paid_date = check_date并从每个集合中选择适当的字段。