是否有一种方法来返回一个值而不是空白的SQL?

Question

我试图做一个HTML表，但我有麻烦，它不返回一个值。这个例子显示了我的结果。样品图片

是否有可能编写脚本，如果没有任何结果，它将返回0?

我尝试了ISNULL方法，它没有返回任何值。

我期望通过使用ISNULL方法，它将在结果中返回0，但它没有这样做。

SELECT
ISNULL(body, 0) 
FROM
[EMAIL_MATRIX] 
WHERE
body NOT IN (SELECT c.Body
FROM [dbo].[EMAIL_DETAILS] a
INNER JOIN [Email_matrix] c ON SUBSTRING(a.Subject, 5, LEN(a.Subject) - 4) = c.Subject
AND a.Body LIKE CONCAT('%', c.body, '%') 
WHERE CAST(a.Send AS DAte) = '2022-10-04')

Answer 1

试试这个:

WITH DataSource AS
(
Select ISNULL(body,0) AS body
from [EMAIL_MATRIX] 
where body NOT IN
(
SELECT c.Body
FROM [dbo].[EMAIL_DETAILS] a
INNER JOIN [Email_matrix] c 
ON SUBSTRING(a.Subject,5,LEN(a.Subject)-4) = c.Subject
and a.Body LIKE CONCAT('%', c.body, '%') 
where Cast(a.Send as DAte) = '2022-10-04'
)
)
SELECT body
FROM DataSource
WHERE EXISTS(SELECT 1 FROM DataSource)
UNION ALL
SELECT NULL
WHERE NOT EXISTS(SELECT 1 FROM DataSource)

Answer 2

试试这个，

Select REPLACE(ISNULL(body,0),'',0) from [EMAIL_MATRIX] 
where body NOT IN(
SELECT c.Body
FROM [dbo].[EMAIL_DETAILS] a
INNER JOIN [Email_matrix] c 
ON SUBSTRING(a.Subject,5,LEN(a.Subject)-4) = c.Subject
and a.Body LIKE CONCAT('%', c.body, '%') 
where Cast(a.Send as DAte) = '2022-10-04')