字段中的SQL计数ID [英] SQL count ids in fields

问题描述

我有一个表，其中包含ID。看起来像：

I have a table contains IDs in field. It looks like:

FieldName
-------------------------
1,8,2,3,4,10,5,9,6,7
-------------------------
1,8
-------------------------
1,8

我需要对这些ID进行计数才能得出结果：

I need to count these IDs to get result:

ID | Count
---|------
1 | 3
8 | 3
2 | 1
3 | 1

有什么想法吗？

谢谢！

推荐答案

尝试一下：

Declare  @demo table(FieldName varchar(100))

insert into @demo values('1,8,2,3,4,10,5,9,6,7')
insert into @demo values('1,8')
insert into @demo values('1,8')


select ID, COUNT(id) ID_count from 
(SELECT 
     CAST(Split.a.value('.', 'VARCHAR(100)') AS INT) AS ID 
FROM  
(
SELECT CAST ('<M>' + REPLACE(FieldName, ',', '</M><M>') + '</M>' AS XML) AS ID  
    FROM  @demo
) AS A CROSS APPLY ID.nodes ('/M') AS Split(a)) q1
group by ID

我喜欢Devart的答案，因为执行速度更快。这是修改后的早期答案满足您的需求：

I like Devart's answer because of the faster execution. Here is a modified earlier answer to suite your need :

Declare @col varchar(200)

SELECT
@col=(
        SELECT FieldName + ','
        FROM @demo c

        FOR XML PATH('')
      );


;with demo as(


select cast(substring(@col,1,charindex(',',@col,1)-1) AS INT) cou,charindex(',',@col,1) pos

  union all 
  select cast(substring(@col,pos+1,charindex(',',@col,pos+1)-pos-1)AS INT) cou,charindex(',',@col,pos+1) pos
  from demo where pos<LEN(@col))
select  cou ID, COUNT(cou) id_count from demo    
group by cou

这篇关于字段中的SQL计数ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！