如何统计数目？？？？？？？？？？？？？？？？？？

Question

declare @str varchar(8000)
set @str=''
select @str=@str+','+NO1 from scoredeclare @tb table(ID int)
declare @i int
set @i=1
while @i<=29
begin
      insert @tb select @i
      set @i=@i+1
endselect t.ID
       ,(len(@str)-replace(@str,','+convert(varchar,t.ID)+',',','))
         /(1+len(convert(varchar,t.ID)))
        /(len(@str)-replace(@str,',','')+1)
        *100 as '百分比'
from @tb t

Answer 1

declare @str varchar(8000)
set @str=''
select @str=@str+','+NO1 from scoredeclare @tb table(ID int)
declare @i int
set @i=1
while @i<=29
begin
      insert @tb select @i
      set @i=@i+1
endselect t.ID
       ,convert(varchar,
                (len(@str)-replace(@str,','+convert(varchar,t.ID)+',',','))
                /(1+len(convert(varchar,t.ID)))
                /(len(@str)-replace(@str,',','')+1)
                *100
               )+'%' as '百分比'
from @tb t

Answer 2

declare @s varchar(8000),@i int
set @s = ''
set @i = 1
while @i<30
begin
    set @s = @s + 'union select NUM='
                + rtrim(@i) 
                +',rtrim(cast(sum(case when charindex(',''
                + rtrim(@i)+ '',','',''+NO1)>0 then 1.0 else 0.0 end)*100/count(*) as int))+''%'' from score '
    set @i = @i+1
end
set @s = stuff(@s,1,6,'')
exec(@s)

Answer 3

declare @str varchar(8000)
set @str=''
select @str=@str+','+NO1 from scoredeclare @tb table(ID int)
declare @i int
set @i=1
while @i<=29
begin
      insert @tb select @i
      set @i=@i+1
endselect t.ID
       ,(len(@str)-len(replace(@str,','+convert(varchar,t.ID)+',',','))) as '带,数量'
       ,(len(@str)-len(replace(@str,','+convert(varchar,t.ID)+',',',')))
                /(1+len(convert(varchar,t.ID))) as '数量'
       ,(len(@str)-len(replace(@str,',',''))+1) as '总数量'
       ,convert(varchar,
                ((len(@str)-len(replace(@str,','+convert(varchar,t.ID)+',',',')))+0.0)
                /(1+len(convert(varchar,t.ID)))
                /(len(@str)-len(replace(@str,',',''))+1)
                *100
               )+'%' as '百分比'
from @tb t

Answer 4

declare @score table
(
  ID int,
  NO1 varchar(200)
)
insert @score
select 1 ,'3,4,13,20,21,26,28,29,' union
select 2 ,'5,12,19,20,23,27,28,' union
select 3 ,'4,16,20,21,23,25,28,' union
select 4 ,'1,4,6,10,16,18,23,24,27,29,' union
select 5 ,'4,11,16,18,21,27,' union
select 6 ,'9,10,11,18,20,21,22,25,26,27,28,' --查询
declare @str varchar(8000)
set @str=''
select @str=@str+','+NO1 from @scoredeclare @tb table(ID int)
declare @i int
set @i=1
while @i<=29
begin
      insert @tb select @i
      set @i=@i+1
endselect t.ID
       ,(len(@str)-len(replace(@str,','+convert(varchar,t.ID)+',',','))) as '带,数量'
       ,(len(@str)-len(replace(@str,','+convert(varchar,t.ID)+',',',')))
                /(1+len(convert(varchar,t.ID))) as '数量'
       ,(len(@str)-len(replace(@str,',',''))+1) as '总数量'
       ,convert(varchar,
                ((len(@str)-len(replace(@str,','+convert(varchar,t.ID)+',',',')))+0.0)
                /(1+len(convert(varchar,t.ID)))
                /(len(@str)-len(replace(@str,',',''))+1)
                *100
               )+'%' as '百分比'
from @tb t--结果
/*
ID          带,数量        数量          总数量         百分比                             
----------- ----------- ----------- ----------- ------------------------------- 
1           2           1           56          1.78571428571428571428500%
2           0           0           56          0.00000000000000000000000%
3           2           1           56          1.78571428571428571428500%
4           8           4           56          7.14285714285714285714200%
5           2           1           56          1.78571428571428571428500%
6           2           1           56          1.78571428571428571428500%
7           0           0           56          0.00000000000000000000000%
8           0           0           56          0.00000000000000000000000%
9           2           1           56          1.78571428571428571428500%
10          6           2           56          3.57142857142857142857100%
11          6           2           56          3.57142857142857142857100%
12          3           1           56          1.78571428571428571428500%
13          3           1           56          1.78571428571428571428500%
14          0           0           56          0.00000000000000000000000%
15          0           0           56          0.00000000000000000000000%
16          9           3           56          5.35714285714285714285700%
17          0           0           56          0.00000000000000000000000%
18          9           3           56          5.35714285714285714285700%
19          3           1           56          1.78571428571428571428500%
20          12          4           56          7.14285714285714285714200%
21          12          4           56          7.14285714285714285714200%
22          3           1           56          1.78571428571428571428500%
23          9           3           56          5.35714285714285714285700%
24          3           1           56          1.78571428571428571428500%
25          6           2           56          3.57142857142857142857100%
26          6           2           56          3.57142857142857142857100%
27          12          4           56          7.14285714285714285714200%
28          12          4           56          7.14285714285714285714200%
29          6           2           56          3.57142857142857142857100%
*/