如何在sql列中查找每个值的计数

问题描述

这是我的查询的示例示例.

我的表格中有一列包含这样的价位
[专栏]
AS，BS
AS，CS
CS，ES
DE，AS
IR，CS，DE.

我需要找到每个值的计数，然后输出就会像这样
AS 5
BS 3
CS 4.

谁能帮我写程序


谢谢，
Anusha

Hi,

Here is a sample example for my query.

I have a column in my Table which contains valies like this
[Column]
AS,BS
AS,CS
CS,ES
DE,AS
IR,CS,DE.

I need to find the Count of each value and output shuld come like this
AS 5
BS 3
CS 4 .

can anyone help me to write a Procedure


Thanks,
Anusha

推荐答案

create table alphabet
(testword varchar(10)
)

insert into alphabet values('AS,BS')
insert into alphabet values('AS,CS')
insert into alphabet values('CS,ES')
insert into alphabet values('DE,AS')


alter Procedure [dbo].[asp_CommaSeparatedStringToTable]
AS
BEGIN
  declare @rownum as int
  DECLARE @String VARCHAR(10)
  DECLARE @StringInput VARCHAR(100)
  set @rownum =1
CREATE TABLE #temp1
(
   COL1 varchar(50),  
   rownum int, 
)
CREATE TABLE #temp2
(
   COL1 varchar(50),   
)
Insert into #temp1
  select testword,ROW_NUMBER() over (order by testword) as rowno from alphabet
  
  while (@rownum <=(select MAX(rownum) from #temp1))
  BEGIN
 
select @StringInput=COL1 from #temp1 where rownum=@rownum

    WHILE LEN(@StringInput) > 0
    BEGIN
        SET @String      = LEFT(@StringInput, 
                                ISNULL(NULLIF(CHARINDEX(',', @StringInput) - 1, -1),
                                LEN(@StringInput)))
        SET @StringInput = SUBSTRING(@StringInput,
                                     ISNULL(NULLIF(CHARINDEX(',', @StringInput), 0),
                                     LEN(@StringInput)) + 1, LEN(@StringInput))

        INSERT INTO  #temp2
        VALUES ( @String )
    END
    set @rownum=@rownum+1
    END
    
    select COUNT(1),col1 from #temp2 group by col1
     drop table #temp2
     drop table #temp1
END
GO

exec [asp_CommaSeparatedStringToTable]

我已经在存储过程中对表和列进行了硬编码.

I have hardcoded table and column inside stored procedure..

ColumnName
------
aa
aa
bb
bb
cc

那你会写:

Then you would write:

select ColumnName,Count(ColumnName) from tablename group by columnname

这就是为什么将多个数据项放在同一行(即"aa，bb")是一个坏主意的原因

That is why it is a bad idea to put multiple data items in a single row (i.e. "aa,bb")

这篇关于如何在sql列中查找每个值的计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！