时间戳

首页 > 解决方案 > 如何在 SQL Server 派生表中使用@@ROWCOUNT？

sql-server - 如何在 SQL Server 派生表中使用@@ROWCOUNT？

问题描述

我的查询中有多个 join ( left join) 派生表，其中一个返回一个空结果集，这给我的WHERE子句带来了问题。

WHERE 条款：

WHERE (ISNULL(BalanceThis.Balance,0) + ISNULL(RL.Balance,0) + ISNULL(OtherPayThis.Balance,0) + ISNULL(RUPBalThis.Balance,0)) <> 0

询问：

LEFT JOIN (
            SELECT AE3.IDNo, SUM(ISNULL(AE3.Debit,0)) AS Debit, SUM(ISNULL(AE3.Credit,0)) AS Credit, 
            ABS(SUM(ISNULL(AE3.Debit,0))-SUM(ISNULL(AE3.Credit,0))) AS Balance
            FROM AccountingEntries AE3 WITH (NOLOCK)                
            WHERE AE3.BookName NOT IN ('BFSL')
                AND AE3.GLAccount = '1.03.01' AND ISNULL(AE3.TC,'') = 'LAC'
                AND ((Datepart(MM,AE3.DateEntry)=Datepart(MM,@Date)) 
                AND Datepart(YEAR,AE3.DateEntry)=Datepart(YEAR,@Date))
                GROUP BY AE3.IDNo
            ) RL ON RL.IDNo = Loans.LoanID

结果集：

IDNo    Debit   Credit  Balance

有没有办法在派生表为空时使用@@ROWCOUNT 返回静态结果集？

像这样：

IDNo    Debit   Credit  Balance
EMP12    0        0       0

谢谢。

标签： sql-server

解决方案

这是做你想做的事情的一种方法：

WITH MyQuery As
(
-- This is your original query
-- This returns no rows
SELECT 'I have no rows' As OriginalQuery WHERE 0=1
)

-- Select from your query
SELECT * FROM MyQuery
UNION ALL
-- If there's nothing in your query, UNION ALL this part
SELECT 'I only appear when there are no rows' WHERE NOT EXISTS (SELECT * FROM MyQuery)

推荐阅读