我有以下存储过程来从表中获取数据.该表有一个"区域"列,其中包含"APAC:China"等值,我正在使用子字符串函数来删除:以及后面的所有内容.
以下工作除了它分别列出所有记录而不是通过我的子串分组.所以我有几个区域为"APAC"的项目,而不是只有一个项目,所有项目都出现在下面.
我的存储过程:
CREATE PROCEDURE [dbo].[CountRegions] AS BEGIN SET NOCOUNT ON; SELECT SUBSTRING(region, 1, CHARINDEX(':', region) - 1) AS region, COUNT(*) AS groupCount, FROM SOPR_LogRequests WHERE logStatus = 'active' GROUP BY region ORDER BY groupCount desc, region FOR XML PATH('regions'), ELEMENTS, TYPE, ROOT('ranks') END
我的结果:
APAC 1 EMEA 1 // ... APAC 1
预期结果:
APAC 2 // ... EMEA 1
这里的任何人都可以帮我吗?
蒂姆,谢谢你的帮助.
你group by
不知道你是在引用基础列,还是函数代码的输出(它会假设基础列),所以你需要重复代码group by
:
CREATE PROCEDURE [dbo].[CountRegions] AS BEGIN SET NOCOUNT ON; SELECT SUBSTRING(region, 1, CHARINDEX(':', region) - 1) AS region, COUNT(*) AS groupCount, FROM SOPR_LogRequests WHERE logStatus = 'active' GROUP BY SUBSTRING(region, 1, CHARINDEX(':', region) - 1) ORDER BY groupCount desc, region FOR XML PATH('regions'), ELEMENTS, TYPE, ROOT('ranks') END