求一SQL语句,高手请进,急!!!!!!!!!!!,谢谢

[yaopeng117]

编号 数据
1 1,2,3,4,5,6,7,8,9,10
2 3,6,7,8,10,12,13,14,16,18
3 6,7,10,11,12,13,14,15,16,20
. .
. .
. .
以上是模拟数据,其中数据这列是1到20之间的10个数字,中间以","分开,现在需要统计出1到20分别为多少,如上模拟数据,1有1次,6有3次,万分感谢!

[yaopeng117]

没有人么?

[liangCK]

create table tb
(
   id int,
   col varchar(500)
)

insert tb select 1,                               '1,2,3,4,5,6,7,8,9,10' 
insert tb select 2 ,                              '3,6,7,8,10,12,13,14,16,18 '
insert tb select 3  ,                             '6,7,10,11,12,13,14,15,16,20' 

select top 500 id=identity(int,1,1) into # from syscolumns a,syscolumns b

select s,count(1) as 出现次数
from
(
    select s=substring(a.col,b.id,charindex(',',a.col+',',b.id)-b.id)
    from tb a,# b
    where substring(','+a.col,b.id,1)=','
) a
group by s
order by count(1) desc

drop table tb,#

/*
s      出现次数
---- ----------
10	3
6	3
7	3
8	2
3	2
12	2
13	2
14	2
16	2
18 	1
2	1
20	1
15	1
4	1
5	1
11	1
9	1
1	1

*/

[OracleRoob]

--统计各个数字的次数：

create table T(编号 int,数据 varchar(100))

insert into T select 1,'1,2,3,4,5,6,7,8,9,10'
insert into T select 2,'3,6,7,8,10,12,13,14,16,18'
insert into T select 3,'6,7,10,11,12,13,14,15,16,20'

--select * from T

select '1' as [1的次数],sum(len(数据)+2-len(replace(',' + 数据 + ',',',1,',',,'))) as 次数 from T
union all
select '2' as [1的次数],sum(len(数据)+2-len(replace(',' + 数据 + ',',',2,',',,'))) as 次数 from T
union all
select '3' as [1的次数],sum(len(数据)+2-len(replace(',' + 数据 + ',',',3,',',,'))) as 次数 from T
union all
select '4' as [1的次数],sum(len(数据)+2-len(replace(',' + 数据 + ',',',4,',',,'))) as 次数 from T
union all
select '5' as [1的次数],sum(len(数据)+2-len(replace(',' + 数据 + ',',',5,',',,'))) as 次数 from T
union all
select '6' as [1的次数],sum(len(数据)+2-len(replace(',' + 数据 + ',',',6,',',,'))) as 次数 from T

drop table T

[yaopeng117]

TO楼上两位,由于类似'1,2,3,4,5,6,7,8,9,10'的数据有几10万条,我不可能这么一条条的插入吧,有没有什么办法啊?

[yaopeng117]

明白了,谢谢两位