如何计算所有记录中列的单个文本计数 [英] How do I count individual text count from a column from all records
问题描述
Hai,
我需要在特定列的所有行上显示每个文本出现次数。
见下面的结果:
---------------- ---------------------
标签|总计|
-------------------- | --------------- |
keyword.io | 34 |
------------------------------------ |
keywordtool.io,| 45 |
------------------------------------ |
seobook.com,| 56 |
------------------------------------ |
adwords.google.com | 123 |
-------------------------------------
我需要与上面完全一样。
这里我有一组标签,我需要显示每个标签的计数名称为'总计'
如果想了解我发布的相同@ C#Corner ,请访问它。
请建议您的查询以获得我想要的结果。
提前感谢。
我尝试过:
DECLARE @ tags VARCHAR ( 8000 )
DECLARE @ tot INT
选择 @ tags = 合并( @ tags + ' ,',' ')+标签来自 addNew
选择 @ tot = count( @tags )
选择 a.Labels as 标签, @tot 总来自 addNew a
inner join addNew n
on a.Labels = n.Labels
group by a.Labels
我的结果是:
----------------- --------------------
标签|总计|
-------------------- | --------------- |
keyword.io ,, | |
keywordtool.io,| 1 |
seobook.com,| |
Asp.Net | |
adwords.google.com | |
------------------------------------ |
HtmlAgilityPack | |
库,| |
解析网页,| 1 |
Asp.Net,| |
Meta Descrition | |
------------------------------------ |
seobook.com,| |
aptitude,| |
代数,| 1 |
------------------------------------ |
adwords.google.com | 1 |
-------------------------------------
SELECT AN.Labels,
Tag,
Total FROM AddNew AN
CROSS APPLY
(
SELECT VALUE as Tag,
count(*)as Total FROM string_split(AN.Labels,',')
GROUP BY VALUE
)AS ff
检查:
DECLARE @ tmp TABLE (标签 NVARCHAR ( 255 ))
INSERT INTO @ tmp (标签)
VALUES (' keyword.io,keywordtool.io,seobook.com,Asp.Net,adwords .google.com'),
(' HtmlAgilityPack库,Parse网页,Asp.Net,Meta Descrition'),
(' seobook.com,aptitude,algebra'),
(' adwords.google.com')
; WITH CTE AS
(
SELECT LEFT (标签,CHARINDEX(' ,',标签) -1) AS 标签, RIGHT (标签,LEN(标签)-CHARINDEX(' ,',Tags)) AS 剩余
FROM @ tmp
WHERE CHARINDEX(' ,',标签)> 0
UNION ALL
SELECT LEFT (剩余,CHARINDEX(' ,',剩余)-1) AS 标记, RIGHT (剩余,LEN(剩余)-CHARINDEX(' < span class =code-string>,',Remainder)) AS 剩余
FROM CTE
WHERE CHARINDEX(' ,',剩余)> 0
UNION 所有
SELECT 剩余作为标记, NULL AS 剩余
FROM CTE
WHERE CHARINDEX(' ,',Remainder)= 0
)
SELECT 标签,COUNT(标签)作为 CountOfTag
FROM CTE
GROUP BY 标记
DataTable dt = new DataTable();
dt.Columns.Add( new DataColumn( 标签, typeof ( string )));
dt.Rows.Add( new object [] { keyword.io,keywordtool.io,seobook.com,Asp.Net,adwords.google.com});
dt.Rows.Add( new object [] { HtmlAgilityPack库,Parse Web Page,Asp.Net,Meta Descrition});
dt.Rows.Add( new object [] { seobook.com,aptitude,algebra});
dt.Rows.Add( new object [] { adwords.google.com});
var data = dt.AsEnumerable()
.SelectMany(v => v.Field< string> ;( 标签)。拆分( new string [] { ,},StringSplitOptions.RemoveEmptyEntries))
.GroupBy(x => x)
。选择(grp = > new
{
Tag = grp.Key,
Count = grp.Count()
});
foreach ( var t in data)
{
Console.WriteLine( {0} \ t {1},t.Tag,t.Count);
}
在这两种情况下,结果都是一样的:
< pre lang =text> keyword.io 1
keywordtool.io 1
seobook.com 2
Asp.Net 2
adwords.google.com 2
HtmlAgilityPack library 1
Parse Web Page 1
Meta Descrition 1
aptitude 1
代数1
祝你好运!
DECLARE @ tmp 表(标签 NVARCHAR ( 255 ))
INSERT INTO @ tmp (标签)
VALUES (' keyword.io,keywordtool.io,seobook.com,Asp.Net,adwords.google.com'),
(' HtmlAgilityPack库,Parse Web Page,Asp.Net,Meta Descrition'),
(' seobook.com,aptitude,代数'),
(' adwords.google.com' )
选择标记,count(*)为 cnt 来自 @ tmp a
cross apply (
选择 VALUE as tag < span class =code-keyword> from string_split(a.Tags,' ,')
) as ff
group by 标记;我的过去查询属于每一行,因此此查询列中的所有行。
adwords.google.com 2
代数1
资质1
Asp.Net 2
HtmlAgilityPack库1
keyword.io 1
keywordtool.io 1
Meta Descrition 1
Parse网页1
seobook.com 2
Hai,
I need to display count of each text occurrence on all rows from a particular column.
See result below:
------------------------------------- Tags | Total | --------------------|---------------| keyword.io | 34 | ------------------------------------| keywordtool.io , | 45 | ------------------------------------| seobook.com , | 56 | ------------------------------------| adwords.google.com | 123 | -------------------------------------
I need exactly like above.
Here I have a set of Tags, I need to display count each tag as column in the name 'Total'
If want to understand I have posted the same @ C# Corner with source code file, please make a visit to it.
Please suggest your queries to get my desired result.
Thank in Advance.
What I have tried:
DECLARE @tags VARCHAR(8000)
DECLARE @tot INT
select @tags = coalesce(@tags + ',' , ' ') + Labels from addNew
select @tot = count(@tags)
select a.Labels as Tags,@tot as Total from addNew a
inner join addNew n
on a.Labels = n.Labels
group by a.Labels
I Got the result as:
------------------------------------- Tags | Total | --------------------|---------------| keyword.io,, | | keywordtool.io, | 1 | seobook.com, | | Asp.Net | | adwords.google.com | | ------------------------------------| HtmlAgilityPack | | library, | | Parse Web Page, | 1 | Asp.Net, | | Meta Descrition | | ------------------------------------| seobook.com, | | aptitude, | | algebra, | 1 | ------------------------------------| adwords.google.com | 1 | -------------------------------------
SELECT AN.Labels, Tag , Total FROM AddNew AN CROSS APPLY ( SELECT VALUE as Tag, count(*)as Total FROM string_split(AN.Labels,',') GROUP BY VALUE )AS ff
Check this:
DECLARE @tmp TABLE(Tags NVARCHAR(255)) INSERT INTO @tmp (Tags) VALUES('keyword.io,keywordtool.io,seobook.com,Asp.Net,adwords.google.com'), ('HtmlAgilityPack library,Parse Web Page,Asp.Net,Meta Descrition'), ('seobook.com,aptitude,algebra'), ('adwords.google.com') ;WITH CTE AS ( SELECT LEFT(Tags, CHARINDEX(',', Tags)-1) AS Tag, RIGHT(Tags, LEN(Tags)-CHARINDEX(',', Tags)) AS Remainder FROM @tmp WHERE CHARINDEX(',', Tags) >0 UNION ALL SELECT LEFT(Remainder, CHARINDEX(',', Remainder)-1) AS Tag, RIGHT(Remainder, LEN(Remainder)-CHARINDEX(',', Remainder)) AS Remainder FROM CTE WHERE CHARINDEX(',', Remainder)>0 UNION ALL SELECT Remainder As Tag, NULL AS Remainder FROM CTE WHERE CHARINDEX(',', Remainder)=0 ) SELECT Tag, COUNT(Tag) As CountOfTag FROM CTE GROUP BY Tag
DataTable dt = new DataTable(); dt.Columns.Add(new DataColumn("Tags", typeof(string))); dt.Rows.Add(new object[]{"keyword.io,keywordtool.io,seobook.com,Asp.Net,adwords.google.com"}); dt.Rows.Add(new object[]{"HtmlAgilityPack library,Parse Web Page,Asp.Net,Meta Descrition"}); dt.Rows.Add(new object[]{"seobook.com,aptitude,algebra"}); dt.Rows.Add(new object[]{"adwords.google.com"}); var data = dt.AsEnumerable() .SelectMany(v=>v.Field<string>("Tags").Split(new string[]{","}, StringSplitOptions.RemoveEmptyEntries)) .GroupBy(x=>x) .Select(grp=> new { Tag = grp.Key, Count = grp.Count() }); foreach(var t in data) { Console.WriteLine("{0}\t{1}", t.Tag, t.Count); }
In both cases the result is the same:
keyword.io 1 keywordtool.io 1 seobook.com 2 Asp.Net 2 adwords.google.com 2 HtmlAgilityPack library 1 Parse Web Page 1 Meta Descrition 1 aptitude 1 algebra 1
Good luck!
DECLARE @tmp TABLE(Tags NVARCHAR(255)) INSERT INTO @tmp (Tags) VALUES('keyword.io,keywordtool.io,seobook.com,Asp.Net,adwords.google.com'), ('HtmlAgilityPack library,Parse Web Page,Asp.Net,Meta Descrition'), ('seobook.com,aptitude,algebra'), ('adwords.google.com') select tag,count(*) as cnt from @tmp a cross apply ( select VALUE as tag from string_split(a.Tags,',') )as ff group by tag;My past query belongs to each row so this query for all rows in column.
adwords.google.com 2 algebra 1 aptitude 1 Asp.Net 2 HtmlAgilityPack library 1 keyword.io 1 keywordtool.io 1 Meta Descrition 1 Parse Web Page 1 seobook.com 2
这篇关于如何计算所有记录中列的单个文本计数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!