DECLARE @t TABLE(v VARCHAR(1000))
INSERT @t SELECT '0,1,1,1,1,1,0,1,1,1,1,0'
UNION ALL SELECT '1,0,0,0,0,0,1,1,1,1,0,0,0,0,1'SELECT LEN(REPLACE(REPLACE(v,',',''),'1','')) 为0的项,
LEN(REPLACE(REPLACE(v,',',''),'0','')) 为1的项,
LEN(REPLACE(v,',','')) 总项数,
CAST(LEN(REPLACE(REPLACE(v,',',''),'1','')) * 100.0 / LEN(REPLACE(v,',','')) AS DECIMAL(18,2)) AS 为0项占的比率,
CAST(LEN(REPLACE(REPLACE(v,',',''),'0','')) * 100.0 / LEN(REPLACE(v,',','')) AS DECIMAL(18,2)) AS 为1项占的比率
FROM @t/*
为0的项 为1的项 总项数 为0项占的比率 为1项占的比率
----------- ----------- ----------- -------------------- --------------------
3 9 12 25.00 75.00
9 6 15 60.00 40.00
*/
INSERT @t SELECT '0,1,1,1,1,1,0,1,1,1,1,0'
UNION ALL SELECT '1,0,0,0,0,0,1,1,1,1,0,0,0,0,1'SELECT LEN(REPLACE(REPLACE(v,',',''),'1','')) 为0的项,
LEN(REPLACE(REPLACE(v,',',''),'0','')) 为1的项,
LEN(REPLACE(v,',','')) 总项数,
CAST(LEN(REPLACE(REPLACE(v,',',''),'1','')) * 100.0 / LEN(REPLACE(v,',','')) AS DECIMAL(18,2)) AS 为0项占的比率,
CAST(LEN(REPLACE(REPLACE(v,',',''),'0','')) * 100.0 / LEN(REPLACE(v,',','')) AS DECIMAL(18,2)) AS 为1项占的比率
FROM @t/*
为0的项 为1的项 总项数 为0项占的比率 为1项占的比率
----------- ----------- ----------- -------------------- --------------------
3 9 12 25.00 75.00
9 6 15 60.00 40.00
*/
CREATE TABLE tb(ID int,col varchar(50),num int)
INSERT tb SELECT 1,'aa,bb,cc',10
UNION ALL SELECT 2,'aa,aa,bb',20
UNION ALL SELECT 3,'aa,aa,bb',20
UNION ALL SELECT 4,'dd,ccc,c',30
UNION ALL SELECT 5,'ddaa,ccc',40
UNION ALL SELECT 6,'eee,ee,c',50
GO--1. 记录数统计示例
--分拆处理需要的辅助表(由于是直接处理,所以根据col1列中最大的数据长度来创建)
DECLARE @len int
SELECT TOP 1 @len=LEN(col)+1 FROM tb ORDER BY LEN(col) DESC
IF ISNULL(@len,1)=1 RETURN
SET ROWCOUNT @len
SELECT ID=IDENTITY(int,1,1) INTO # FROM syscolumns a,syscolumns b
ALTER TABLE # ADD PRIMARY KEY(ID)
SET ROWCOUNT 0--统计处理
SELECT data=SUBSTRING(a.col,b.ID,CHARINDEX(',',a.col+',',b.ID)-b.ID),
[COUNT]=COUNT(DISTINCT a.ID),
Numbers=COUNT(*)
FROM tb a,# b
WHERE b.ID<=LEN(a.col)
AND SUBSTRING(','+a.col,b.ID,1)=','
GROUP BY SUBSTRING(a.col,b.ID,CHARINDEX(',',a.col+',',b.ID)-b.ID)
DROP TABLE #
GO
insert into answer select '0,0,1,0,0,0,0,0,1,0,0,0,0,1,0,0,0,0,0,1,0,0,0,0,1,0,0,0,1,0,0,0,0,0,1,0,0,1,0,0,0,1,0,0,0,0,0,1,0,0,1,0,0,0,0,1,0,0,0,1,0,0,0,0,0,0,0,1,0,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,1,0,0,0,0,0,0,1,0,0,1,0,0,0,0,0,1,0,0,0,1,0,0,0,0,'
insert into answer select '0,1,0,0,0,0,1,0,0,0,0,1,0,1,0,0,0,0,1,0,1,0,0,1,0,0,0,0,0,1,0,0,0,0,1,0,0,1,0,0,0,1,0,0,0,1,0,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,1,0,0,0,0,0,1,0,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,1,0,0,0,0,0,1,0,0,0,1,0,0,0,0,'
select convert(decimal(6,2),len(replace(a,'1,','')))*100/len(a) as 不是的百分数,convert(decimal(6,2),len(replace(a,'0,','')))*100/len(a) as 是的百分数 from answer
go
drop table answer
/*
不是的百分数 是的百分数
--------------------------------------- ---------------------------------------
80.3030303030303 19.6969696969696
78.4615384615384 21.5384615384615(2 行受影响)
*/
create TABLE #t(v VARCHAR(1000))
INSERT #t SELECT '0,1,1,1,1,1,0,1,1,1,1,0'
UNION ALL SELECT '1,0,0,0,0,0,1,1,1,1,0,0'
UNION ALL SELECT '0,1,1,1,1,1,0,1,1,1,1,0'
UNION ALL SELECT '1,0,0,0,0,0,1,1,1,1,0,0'
UNION ALL SELECT '0,1,1,1,1,1,0,1,1,1,1,0'select identity(int,1,1) as iD,* into #tbl from #t
select a.ID,b.Number,substring(replace(a.v,' ',','),b.Number,charindex(',',replace(a.v,' ',',')+',',b.Number)-b.Number) AS COLS
into # from #tbl a,master.dbo.spt_values b
where b.Number between 1 and len(a.v) and b.type = 'P'
and charindex(',',','+replace(a.v,' ',','),b.Number) = b.Number
select isnull(a.number,t.number),isnull(a.b,0),isnull(a.per,0) as 'per0',isnull(t.b,1),isnull(t.per,0) as 'per1' from
(select Number,'0' as b,count(cols)/5.0*100 as per from # where cols = 0 group by Number) a
full outer join (select Number,'1' as b,count(cols)/5.0*100 as per from # where cols = 1 group by Number) t on a.Number = t.Number
from (
select convert(varchar,len(replace(REPLACE(STR,',',''),'0',''))*100/len( REPLACE(STR,',',''))) as print0
from test2 where CN=14
) a其中test2是放这个字段的表,STR是这个字段0,1,0,1...的列..
print0 print1
21% 79%
insert into test select '0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,1,0,0,0,0,0,1,0,0,0,0,1,0,0,0,1,0,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,1,0,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,1,0,0,0,0,0,0,1,0,0,1,0,0,0,0,0,1,0,0,0,1,0,0,0,0,'
insert into test select '0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,1,0,0,0,0,0,1,0,0,0,0,1,0,0,0,1,0,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,1,0,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,1,0,0,0,0,0,0,1,0,0,1,0,0,0,0,0,1,0,0,0,1,0,0,0,0,0,1,'
insert into test select '0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,1,0,0,0,0,0,1,0,0,0,0,1,0,0,0,1,0,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,1,0,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,1,0,0,0,0,0,0,1,0,0,1,0,0,0,0,0,1,0,0,0,1,0,0,0,0,0,1,0,0,'
insert into test select '0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,1,0,0,0,0,0,1,0,0,0,0,1,0,0,0,1,0,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,1,0,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,1,0,0,0,0,0,0,1,0,0,1,0,0,0,0,0,1,0,0,0,1,0,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,'
gocreate procedure sp_test
as
begin
declare @sql varchar(8000)
create table #(id int,code int)
declare tc cursor for select code from test
open tc
fetch next from tc into @sql
while @@fetch_status=0
begin
set @sql='declare @t table(id int identity(1,1),code int) insert into @t select '+replace(left(@sql,len(@sql)-1),',','union all select ')+' insert into # select * from @t' exec(@sql)
fetch next from tc into @sql
end
close tc
deallocate tc
select id,sum(code) as '1',sum(1-code) as '0' from # group by id
end
goexec sp_test
/*
id 1 0
----------- ----------- -----------
1 0 4
2 1 3
3 1 3
4 0 4
5 2 2
6 0 4
7 1 3
8 1 3
9 0 4
10 2 2
11 0 4
12 1 3
13 1 3
14 0 4
15 2 2
16 0 4
17 1 3
18 1 3
19 1 3
20 1 3
21 0 4
22 1 3
23 1 3
24 0 4
25 2 2
26 0 4
27 1 3
28 1 3
29 0 4
30 2 2
31 0 4
32 2 2
33 0 4
34 2 2
35 0 4
36 1 3
37 0 4
38 1 3
39 0 4
40 2 2
41 0 4
42 1 3
43 1 3
44 0 4
45 2 2
46 0 4
47 2 2
48 0 4
49 1 3
50 1 3
51 1 3
52 0 4
53 1 3
54 1 3
55 1 3
56 0 4
57 1 3
58 1 3
59 0 4
60 2 2
61 0 4
62 1 3
63 1 3
64 0 4
65 2 2
66 0 4
67 2 2
68 0 4
69 1 3
70 1 3
71 1 3
72 0 4
73 1 3
74 0 4
75 2 2
76 0 4
77 1 3
78 1 3
79 1 3
80 1 3
81 0 4
82 1 3
83 1 3
84 1 3
85 1 3
86 0 4
87 1 3
88 1 3
89 1 3
90 1 3
91 0 4
92 2 2
93 0 4
94 2 2
95 0 4
96 1 3
97 1 3
98 0 4
99 1 3
100 1 3
101 1 3
102 1 3
103 0 4
104 1 3
105 1 3
106 1 3
107 1 3
108 0 4
109 1 3
110 1 3
111 1 3
112 0 4
113 1 3
114 0 4
115 2 2
116 0 4
117 1 3
118 1 3
119 0 4
120 2 2
121 0 4
122 1 3
123 1 3
124 0 4
125 2 2
126 0 4
127 1 3
128 1 3
129 0 4
130 2 2
131 0 4
132 1 3
133 1 3
134 1 3
135 1 3
136 0 4
137 1 3
138 1 3
139 0 4
140 1 3
141 1 3
142 1 3
143 1 3
144 1 3
145 1 3
146 0 4
147 2 2
148 0 4
149 1 3
150 1 3
151 1 3
152 0 4
153 0 4
154 2 2
155 0 4
156 1 3
157 1 3
158 1 3
159 1 3
160 1 3
161 1 3
162 0 4
163 1 3
164 0 4
165 2 2
166 0 4
167 2 2
168 0 4
169 1 3
170 1 3
171 1 3
172 0 4
173 1 3
174 0 4
175 2 2
*/
godrop procedure sp_test
drop table test
go
--测试数据只有5行，你把#t换成你的数据表
select isnull(a.number,t.number),isnull(a.b,0),isnull(a.per,0) as 'per0',isnull(t.b,1),isnull(t.per,0) as 'per1' from
(select Number,'0' as b,count(cols)/5.0*100 as per from # where cols = 0 group by Number) a
full outer join (select Number,'1' as b,count(cols)/5.0*100 as per from # where cols = 1 group by Number) t on a.Number = t.Number
--count(cols)/5.0*100 5.0 换成你的行数，一定要加.0，有2个地方
insert into test select '0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,1,0,0,0,0,0,1,0,0,0,0,1,0,0,0,1,0,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,1,0,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,1,0,0,0,0,0,0,1,0,0,1,0,0,0,0,0,1,0,0,0,1,0,0,0,0,'
insert into test select '0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,1,0,0,0,0,0,1,0,0,0,0,1,0,0,0,1,0,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,1,0,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,1,0,0,0,0,0,0,1,0,0,1,0,0,0,0,0,1,0,0,0,1,0,0,0,0,0,1,'
insert into test select '0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,1,0,0,0,0,0,1,0,0,0,0,1,0,0,0,1,0,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,1,0,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,1,0,0,0,0,0,0,1,0,0,1,0,0,0,0,0,1,0,0,0,1,0,0,0,0,0,1,0,0,'
insert into test select '0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,1,0,0,0,0,0,1,0,0,0,0,1,0,0,0,1,0,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,1,0,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,1,0,0,0,0,0,0,1,0,0,1,0,0,0,0,0,1,0,0,0,1,0,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,0,0,0,0,1,'
gocreate procedure sp_test
as
begin
declare @sql varchar(8000)
create table #(id int,code int)
declare tc cursor for select code from test
open tc
fetch next from tc into @sql
while @@fetch_status=0
begin
set @sql='declare @t table(id int identity(1,1),code int) insert into @t select '+replace(left(@sql,len(@sql)-1),',','union all select ')+' insert into # select * from @t' exec(@sql)
fetch next from tc into @sql
end
close tc
deallocate tc
select id,rtrim(100*sum(code)/count(1))+'%' as 正确率 from # group by id
end
goexec sp_test
/*
id 正确率
----------- -------------
1 0%
2 25%
3 25%
4 0%
5 50%
6 0%
7 25%
8 25%
9 0%
10 50%
11 0%
12 25%
13 25%
14 0%
15 50%
16 0%
17 25%
18 25%
19 25%
20 25%
21 0%
22 25%
23 25%
24 0%
25 50%
26 0%
27 25%
28 25%
29 0%
30 50%
31 0%
32 50%
33 0%
34 50%
35 0%
36 25%
37 0%
38 25%
39 0%
40 50%
41 0%
42 25%
43 25%
44 0%
45 50%
46 0%
47 50%
48 0%
49 25%
50 25%
51 25%
52 0%
53 25%
54 25%
55 25%
56 0%
57 25%
58 25%
59 0%
60 50%
61 0%
62 25%
63 25%
64 0%
65 50%
66 0%
67 50%
68 0%
69 25%
70 25%
71 25%
72 0%
73 25%
74 0%
75 50%
76 0%
77 25%
78 25%
79 25%
80 25%
81 0%
82 25%
83 25%
84 25%
85 25%
86 0%
87 25%
88 25%
89 25%
90 25%
91 0%
92 50%
93 0%
94 50%
95 0%
96 25%
97 25%
98 0%
99 25%
100 25%
101 25%
102 25%
103 0%
104 25%
105 25%
106 25%
107 25%
108 0%
109 25%
110 25%
111 25%
112 0%
113 25%
114 0%
115 50%
116 0%
117 25%
118 25%
119 0%
120 50%
121 0%
122 25%
123 25%
124 0%
125 50%
126 0%
127 25%
128 25%
129 0%
130 50%
131 0%
132 25%
133 25%
134 25%
135 25%
136 0%
137 25%
138 25%
139 0%
140 25%
141 25%
142 25%
143 25%
144 25%
145 25%
146 0%
147 50%
148 0%
149 25%
150 25%
151 25%
152 0%
153 0%
154 50%
155 0%
156 25%
157 25%
158 25%
159 25%
160 25%
161 25%
162 0%
163 25%
164 0%
165 50%
166 0%
167 50%
168 0%
169 25%
170 25%
171 25%
172 0%
173 25%
174 0%
175 50%
*/
godrop procedure sp_test
drop table test
go
bcp 表名 in aaa.txt -t "," -r "\r\n" -S数据库 -U用户 -P密码