如果我运行这样的东西:
select [agent_name], [agent_department], count(*) as [row_count]
from [table_name]
where [agent_name] IS NOT NULL
group by [agent_name] [agent_department];
如果没有要返回的记录(即表为空),则不会返回任何内容。
如果我运行这个
select count(*) as [row_count]
from [table_name]
where [agent_name] IS NOT NULL
我会得到 0 row_count。
有没有办法运行第一个查询,如果没有记录,它是否返回 row_count 0?
这可能不是很漂亮,但它应该带回你想要的:
我从一个小模型开始:
DECLARE @mockup TABLE(agent_name varchar(100),agent_department varchar(100));
--查询将在CTE
内读取您的SELECT
。
WITH cte AS
(
select [agent_name], [agent_department], count(*) as [row_count]
from @mockup
where [agent_name] IS NOT NULL
group by [agent_name],[agent_department]
)
SELECT agent_name,agent_department,row_count FROM cte
UNION ALL SELECT NULL,NULL,0 WHERE (SELECT COUNT(*) FROM cte)=0;
结果
agent_name agent_department row_count
NULL NULL 0
您会看到,结果集按原样调用,而有一个UNION ALL SELECT
查询,该查询仅在 cte 没有行的情况下才会提供。
现在我们在表中插入一些数据
INSERT INTO @mockup VALUES('blah','blub');
WITH cte AS
(
select [agent_name], [agent_department], count(*) as [row_count]
from @mockup
where [agent_name] IS NOT NULL
group by [agent_name],[agent_department]
)
SELECT agent_name,agent_department,row_count FROM cte
UNION ALL SELECT NULL,NULL,0 WHERE (SELECT COUNT(*) FROM cte)=0;
新结果现在是
agent_name agent_department row_count
blah blub 1