求一条SQL，有难度

Question

表1有字段
 id_list varchar(50) 用于记录ID，若保存多个ID，每个ID之间用逗号分隔实际数据如下
id_list
1,2
1,3
2
3,4
4,1
4,2
5
5,6现在想要统计每个ID出现的次数
希望生成数据id    出现次数
1      3
2      3
3      2
4      3
5      2
6      1

Answer 1

SELECT
    id,COUNT(*) AS 出现次数
FROM (
    SELECT
        SUBSTRING(A.id_list,number,CHARINDEX(',',A.id_list+',',number)-number) AS id
    FROM tb1 AS A
       JOIN master.dbo.spt_values AS B
          ON B.number BETWEEN 1 AND LEN(A.id_list)
              AND SUBSTRING(','+A.id_list,number,1)=','
) AS A
GROUP BY id

Answer 2

如果有ID表，很好办：
select 
    a.ID,count(b.id_list) as 出现次数 
from 
    T_ID a,T_LIST b 
where 
    charindex(','+rtrim(a.ID)+',' , ','+b.id_list+',')>0 
group by 
    a.ID

Answer 3

create table tb(id int, id_list varchar(10))
insert into tb values(1,'1,2') 
insert into tb values(2,'1,3') 
insert into tb values(3,'2') 
insert into tb values(4,'3,4') 
insert into tb values(5,'4,1') 
insert into tb values(6,'4,2') 
insert into tb values(7,'5') 
insert into tb values(8,'5,6')
goselect id , count(*) 出现次数 from
(
select left(id_list, charindex(',',id_list)-1) id from tb where  charindex(',',id_list) > 0
union all
select substring(id_list , charindex(',',id_list)+1 , len(id_list) ) id from tb where  charindex(',',id_list) > 0
union all
select id_list from tb  where charindex(',',id_list) = 0
) t
group by id drop table tb/*
id         出现次数        
---------- ----------- 
1          3
2          3
3          2
4          3
5          2
6          1（所影响的行数为 6 行）
*/

Answer 4

--如果每行最多一个逗号.则用4楼.--如果有多个
--2000create table tb(id int, id_list varchar(10))
insert into tb values(1,'1,2') 
insert into tb values(2,'1,3') 
insert into tb values(3,'2') 
insert into tb values(4,'3,4') 
insert into tb values(5,'4,1') 
insert into tb values(6,'4,2') 
insert into tb values(7,'5') 
insert into tb values(8,'5,6')
goSELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b select id_list , count(*) 出现次数 from
(
SELECT A.id, id_list = SUBSTRING(A.[id_list], B.id, CHARINDEX(',', A.[id_list] + ',', B.id) - B.id)
FROM tb A, # B
WHERE SUBSTRING(',' + A.[id_list], B.id, 1) = ','
) m
group by id_list
order by id_listDROP TABLE #drop table tb/*
id         出现次数        
---------- ----------- 
1          3
2          3
3          2
4          3
5          2
6          1（所影响的行数为 6 行）
*/
--2005
create table tb(id int, id_list varchar(10))
insert into tb values(1,'1,2') 
insert into tb values(2,'1,3') 
insert into tb values(3,'2') 
insert into tb values(4,'3,4') 
insert into tb values(5,'4,1') 
insert into tb values(6,'4,2') 
insert into tb values(7,'5') 
insert into tb values(8,'5,6')
go
select id_list , count(*) 出现次数 from
(
SELECT A.id, B.id_list
FROM(
    SELECT id, [id_list] = CONVERT(xml,'<root><v>' + REPLACE([id_list], ',', '</v><v>') + '</v></root>') FROM tb
)A
OUTER APPLY(
    SELECT id_list = N.v.value('.', 'varchar(100)') FROM A.[id_list].nodes('/root/v') N(v)
)B
) m
group by id_list
order by id_listdrop table tb/*
id_list                                                                                              出现次数
---------------------------------------------------------------------------------------------------- -----------
1                                                                                                    3
2                                                                                                    3
3                                                                                                    2
4                                                                                                    3
5                                                                                                    2
6                                                                                                    1(6 行受影响)
*/

Answer 5

DECLARE @T TABLE(id_list VARCHAR(20))
INSERT @T SELECT '1,2' 
INSERT @T SELECT '1,3' 
INSERT @T SELECT '2' 
INSERT @T SELECT '3,4' 
INSERT @T SELECT '4,1' 
INSERT @T SELECT '4,2' 
INSERT @T SELECT '5' 
INSERT @T SELECT '5,6' SELECT
    id,COUNT(*) AS 出现次数
FROM (
    SELECT
        SUBSTRING(A.id_list,number,CHARINDEX(',',A.id_list+',',number)-number) AS id
    FROM @T AS A
       JOIN master.dbo.spt_values AS B
          ON B.number BETWEEN 1 AND LEN(A.id_list)
              AND B.type='p'
              AND SUBSTRING(','+A.id_list,number,1)=','
) AS A
GROUP BY id
/*
id                   出现次数
-------------------- -----------
1                    3
2                    3
3                    2
4                    3
5                    2
6                    1(6 行受影响)
*/