如何为SQL分组查询结果的每行添加overall_count标签？

这事儿很容易解决，你只需要在SELECT语句里新增一个固定的字符串常量列，直接输出'overall_count'就行！

修改后的SQL（保持列结构清晰版） #

这种方式会输出三列，第一列就是你要的固定文本，后续导出报表时也更灵活：

select  
  'overall_count' as prefix,
  count(distinct p.orderid) as overall_count, 
  format(datefulfilled, 'yyyy/MM') as month
from Product p 
where datefulfilled <= dateadd(year,-1,getdate()) 
  and typeId in ('Spec') 
group by format(datefulfilled,'yyyy/MM') 
order by format(datefulfilled,'yyyy/MM')

纯文本拼接版（完全匹配你要的输出格式） #

如果需要直接输出无列名的整行文本，就把所有内容拼接成一个字符串列：

select  
  'overall_count ' + cast(count(distinct p.orderid) as varchar) + '  ' + format(datefulfilled, 'yyyy/MM') as result_line
from Product p 
where datefulfilled <= dateadd(year,-1,getdate()) 
  and typeId in ('Spec') 
group by format(datefulfilled,'yyyy/MM') 
order by format(datefulfilled,'yyyy/MM')

执行后就能得到你想要的结果：

overall_count 2  2022/12
overall_count 1  2022/01

内容的提问来源于stack exchange，提问作者Michele