SQL Server：如何按子字符串分组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20329293/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server: How to group by substring
提问by user2571510
I have the following stored procedure to fetch data from a table. The table has a column "region" that contains value like "APAC: China" etc. for which I am using the substring function in order to remove the : and everything after it.
我有以下存储过程来从表中获取数据。该表有一个“地区”列，其中包含“亚太地区： CN ”等值，我正在使用子字符串函数删除 : 及其后的所有内容。
The below works except that it lists all records separately instead of grouping them by my substring. So I have e.g. several items with region "APAC" instead of just one with all of them appearing below.
下面的工作除了它单独列出所有记录而不是按我的子字符串对它们进行分组之外。所以我有几个区域为“APAC”的项目，而不是一个，所有项目都出现在下面。
My stored procedure:
我的存储过程：
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
My result:
我的结果：
<ranks>
<regions>
<region>APAC</region>
<groupCount>1</groupCount>
</regions>
<regions>
<region>EMEA</region>
<groupCount>1</groupCount>
</regions>
<regions>
<region>APAC</region>
<groupCount>1</groupCount>
</regions>
// ...
</ranks>
Expected result:
预期结果：
<ranks>
<regions>
<region>APAC</region>
<groupCount>2</groupCount>
</regions>
<regions>
<region>EMEA</region>
<groupCount>1</groupCount>
</regions>
// ...
</ranks>
Can anyhone here help me with this ?
任何人都可以帮我解决这个问题吗？
Thanks for any help, Tim.
感谢您的帮助，蒂姆。
回答by dav1dsm1th
Your group bywould not know whether you are referring to the underlying column, or the output of your function code (it would assume the underlying column), so you need to repeat the code into the group by:-
您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
