SQL Server：基于列的子串的组查询 [英] SQL Server: Group Query Based on Substring of Column

问题描述

我需要从typeTable的'source'列中获取一个子字符串，并且能够从该列中的每个区域获取统计信息。一行看起来像'server001 [en-US]'。然后打印每个国家的统计数据。也就是说，我需要每个国家的countForType和totalForType。

因此，我相信获得所有server001调用并按国家进行分组是我正在寻找的。 / p>

到目前为止，我的查询如下所示：

 使用数据库; 
 
 declare @fromDate datetime ='2016-02-01 00：00：00.000'; 
 declare @toDate datetime ='2016-02-02 23:59：59.999'; 
 declare @source varchar（15）='server001'; 
 
 DECLARE @countForType bigint; 
 DECLARE @totalForType decimal（30,8）; 
 
 DECLARE @country varchar（10）; 
 
 SELECT @countForType = count（*），
 @totalForType = SUM（typeTable.amount），
 @country = 
 case 
 when（charindex （'['，typeTable.source）> 0 and charindex（']'，typeTable.source）> 0）
 then substring（typeTable.source，charindex（'['，typeTable.source）+1 ，（charindex（']'，typeTable.source） -  1） -  charindex（'['，typeTable.source））
 else null 
 end 
 
 FROM theTypeTable typeTable nolock）
 WHERE typeTable.startDate> @fromDate 
 AND typeTable.startDate< @toDate 
 AND typeTable.source，如@source 
 GROUP BY typeTable.source; - 我相信这个问题可能在这里 -  source是整个字符串'server001 [en-US]'。我需要对每个国家进行分组并提供统计数据，这是来源的一个子字符串。 
 
 
  - 打印报告：
 PRINT'countForType：'+ CAST（@countForType AS VARCHAR）; 
 PRINT'totalForType：'+ CAST（@totalForType AS VARCHAR）; 
  - 为每个国家，打印金额/百分比等... 
 PRINT'country：'+ CAST（@country AS VARCHAR）; 
  

报表本身看起来像这样：

< pre $ countForType：104
totalForType：110000.00000000

国家：en-US
countForType：55
totalForType：95000.00000000

国家：de-CH
countForType：25
totalForType：5000.00000000

国家：tr-TR
countForType：30
totalForType ：10000.00000000


如果我在这里的正确轨道上，或者如果这应该是完全重写？任何指针赞赏。

解决方案

您已经拥有了代码。 GROUP BY CASE语句，用于获取国家/地区：

  GROUP BY case 
 when（charindex（'['，' typeTable.source）> 0和charindex（']'，typeTable.source）> 0）
 then substring（typeTable.source，charindex（'['，typeTable.source）+1，（charindex（' ]'，typeTable.source） -  1） -  charindex（'['，typeTable.source））
 else null 
 end 
  

顺便说一句，您的代码不会执行您的意见所要表达的内容：

   - 打印报告：
 PRINT'countForType：'+ CAST（@countForType AS VARCHAR）; 
 PRINT'totalForType：'+ CAST（@totalForType AS VARCHAR）; 
  - 为每个国家，打印金额/百分比等... 
 PRINT'country：'+ CAST（@country AS VARCHAR）; 
  

您将数据存储在标量变量中，这意味着查询的执行只会将一个值存储到每个变量。它不会让你循环每个结果。要以您在问题中指定的确切格式获得输出，您将需要使用WHILE循环或CURSOR。不过，我质疑你是否真的需要SQL来输出这种格式。让SQL返回结果集并在前端应用程序中设置输出格式会更好。

I need to get a substring from a column 'source' from typeTable and be able to get stats on each region from that one column. A row would look something like 'server001[en-US]'. Then print the stats for each country. That is, I need the countForType and totalForType for each country.

So, I believe get all server001 calls and group them by country is what I'm looking for.

My query, so far, looks like this:

        use thedatabase;

        declare @fromDate datetime = '2016-02-01 00:00:00.000';
        declare @toDate datetime = '2016-02-02 23:59:59.999';
        declare @source varchar(15) = 'server001';

        DECLARE @countForType bigint;
        DECLARE @totalForType decimal(30,8);

        DECLARE @country varchar(10);

        SELECT  @countForType = count(*),
                @totalForType = SUM(typeTable.amount),
                @country = 
                    case
                    when (charindex('[', typeTable.source) > 0 and charindex(']', typeTable.source) > 0)
                    then substring(typeTable.source, charindex('[', typeTable.source) +1, (charindex(']', typeTable.source) - 1) - charindex('[', typeTable.source))
                    else null
                    end

        FROM theTypeTable typeTable (nolock)
        WHERE typeTable.startDate > @fromDate
        AND typeTable.startDate < @toDate
        AND typeTable.source like @source 
        GROUP BY typeTable.source;  -- i believe the issue may be here -- source is the entire string 'server001[en-US]'.  I need to group and provide stats per country, which is a substring of source.


    --Print report:
    PRINT 'countForType: ' + CAST(@countForType AS VARCHAR);
    PRINT 'totalForType: ' + CAST(@totalForType AS VARCHAR);
    --for each country, print the amounts/ percentages etc...
    PRINT 'country: ' + CAST (@country AS VARCHAR);

The report itself would look something like:

countForType: 104
totalForType: 110000.00000000

country: en-US
countForType: 55
totalForType: 95000.00000000

country: de-CH
countForType: 25
totalForType: 5000.00000000

country: tr-TR
countForType: 30
totalForType: 10000.00000000

Could someone let me know if I am on the right track here, or if this should be completely rewritten? Any pointers are appreciated.

解决方案

You already had the code. GROUP BY the CASE statement you used to get country:

GROUP BY                     case
                    when (charindex('[', typeTable.source) > 0 and charindex(']', typeTable.source) > 0)
                    then substring(typeTable.source, charindex('[', typeTable.source) +1, (charindex(']', typeTable.source) - 1) - charindex('[', typeTable.source))
                    else null
                    end

And by the way, your code will not do what your comments say you want it to do:

--Print report:
PRINT 'countForType: ' + CAST(@countForType AS VARCHAR);
PRINT 'totalForType: ' + CAST(@totalForType AS VARCHAR);
--for each country, print the amounts/ percentages etc...
PRINT 'country: ' + CAST (@country AS VARCHAR);

You are storing data in scalar variables, which means the execution of your query will only store one value into each of the variables. It will not allow you to loop through each result. To get the output in the exact format you specified in your question, you will need to use either a WHILE loop or a CURSOR.

However, I question whether you really need SQL to output that format. It would be better to let SQL return a result set, and format the output in your front end application.

这篇关于SQL Server：基于列的子串的组查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！