从列中获取子字符串并执行groupBy和count [英] Get a substring from a column and perform a groupBy and count

问题描述

我有一个表格，用于存储大量文件的数据，例如他们的语言，唯一ID，文件路径等。我希望能够从唯一ID中获取子字符串，该ID赋予我资产类型，这始终是ID的前两个字母。然后，我想按语言对这些资产类型进行分组，并计算每种语言每种类型的数量。所以最后我会理想地喜欢有一个语言列，然后是每个子字符串（资产类型）的列的表。

我试图创建一个大型开关声明，但这不是很可靠，我被告知可能linq会更好。我对linq或sql没有多少经验，并且我尝试过一些sql查询，这些查询让我获得了期望结果的一部分，但我希望也许有更多经验的人可能知道如何将这些函数分组成为一个声明。

  SELECT 
 LCID，
 SUBSTRING（AssetID，1,2）
 FROM [table] 
  

这为我提供了正确的子字符串，但是每种语言都有多行。有没有办法将相同的语言分组到一列中，然后统计每种类型的数量？谢谢

解决方案

听起来像是你想要一个 COUNT 和一个 GROUP BY ：

 选择
 SUBSTRING（AssetID，1,2），
 COUNT（*）总计
 FROM [table] 
 GROUP BY SUBSTRING（AssetID，1,2）
  

您没有指定数据库，但是，如果您使用SQL Server和 LCID 存在于 SELECT 语句中，那么您需要将它包含在 GROUP BY 子句。

如果 LCID 值对每行都是唯一的，那么您将为每个 AssetID ，因为它会尝试将唯一值分组在一起。因此，我删除了 LCID 。

如果它不是唯一的，那么您可以使用：

 选择LCID，
 SUBSTRING（AssetID，1,2），
 COUNT（*）总计
 FROM [table] 
 GROUP BY LCID，SUBSTRING（AssetID，1,2）
  

根据您所做的编辑，您需要一个 PIVOT ，它将行中的数据转换为列。对于 PIVOT ，您将使用：

 选择LCID，HA，HT ，惠普，FH，外汇
从
（
选择LCID，
 SUBSTRING（AssetID，1,2）AssetID 
从[表] 
）src 
 pivot 
（
 count（AssetID）
用于（HA，HT，HP，FH，FX）中的AssetID  - 在此放置更多值
）piv 
  

如果数值未知，您想要转换为列，那么您将需要使用类似于这：

  DECLARE @cols AS NVARCHAR（MAX），
 @query AS NVARCHAR（MAX）
 
从[table] 
中选择@cols = STUFF（（SELECT distinct'，'+ QUOTENAME（SUBSTRING（AssetID，1,2））
 FOR XML PATH（''），TYPE 
）.value（'。'，'NVARCHAR（MAX）'）
，1,1，''）
 
 set @query ='SELECT LCID，'+ @cols +'from 
（
 SELECT LCID，
 SUBSTRING（AssetID，1,2）A ssetID 
 FROM [table] 
）x 
 pivot 
（
 count（AssetID）
 for（'+ @cols +'）
）p'
 
执行（@query）
  

I have a table that stores data about a large number of files, such as their language, unique ID, file path etc. I want to be able to get the sub-string from the unique ID which gives me the asset type, this is always the first 2 letters of the ID. I then want to group these asset types by language and have a count for how many of each type every language has. So at the end I would ideally like a table that has a language column and then a column for each substring (asset type).

I have tried to create a large switch statement but this isn't very reliable and I was told maybe linq would be better. I don't have much experience with linq or sql and I have a couple of sql queries I've tried that gets me one part of the desired results, but I was hoping maybe someone who has more experience might know how to group these functions into one statement.

SELECT 
  LCID,
  SUBSTRING(AssetID,1,2)  
FROM [table]

this gets me the correct substrings, but I have multiple rows for each language. Is there any way to group the same languages into one column and then count how many of each type there are? Thanks

解决方案

Sounds like you want a COUNT and a GROUP BY:

SELECT 
  SUBSTRING(AssetID,1,2), 
  COUNT(*) Total
FROM [table]
GROUP BY SUBSTRING(AssetID,1,2)

You did not specify what database but, if you are using SQL Server and LCID is in your SELECT statement, then you will need to include it in your GROUP BY clause.

If the LCID value is unique for each row then you will get multiple records for each AssetID because it will try to group the unique values together. As a result, I removed the LCID.

If it is not unique, then you can use:

SELECT LCID, 
  SUBSTRING(AssetID,1,2), 
  COUNT(*) Total
FROM [table]
GROUP BY LCID, SUBSTRING(AssetID,1,2)

Based on the edits that you made, you want a PIVOT which transforms the data from rows into columns. For a PIVOT you will use:

select LCID, HA, HT, HP, FH, FX
from
(
  SELECT LCID, 
    SUBSTRING(AssetID,1,2) AssetID
  FROM [table]
) src
pivot
(
  count(AssetID)
  for AssetID in (HA, HT, HP, FH, FX) -- place more values here
) piv

If the values are unknown that you want to transform into columns, then you will need to use dynamic SQL similar to this:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(SUBSTRING(AssetID,1,2)) 
                    from [table]
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT LCID, ' + @cols + ' from 
             (
                SELECT LCID, 
                  SUBSTRING(AssetID,1,2) AssetID
                FROM [table]
            ) x
            pivot 
            (
                count(AssetID)
                for AssetID in (' + @cols + ')
            ) p '

execute(@query)

这篇关于从列中获取子字符串并执行groupBy和count的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！