create table Q(id int,p_id varchar(20),q_id int,answer varchar(40)) insert into Q values(29,'P200711290028',1,rtrim('1=1,2=1,3=1,4=0,5=0 ')) insert into Q values(30,'P200711290028',2,rtrim('9=1,10=1,11=1,12=1,13=0,14=0,15=0,16=0')) insert into Q values(31,'P200711290028',3,rtrim('17=1,18=1,19=1 ')) insert into Q values(32,'P200711290029',1,rtrim('1=1,2=1,3=1,4=0,5=0 ')) insert into Q values(33,'P200711290029',2,rtrim('9=1,10=1,11=1,12=1,13=0,14=0,15=0,16=0')) insert into Q values(34,'P200711290029',3,rtrim('17=1,18=1,19=1 ')) gocreate procedure sp_test as begin declare @sql varchar(8000),@q_id int
select 1 as q_id,1 as s_id,1 as num into # from Q delete #
declare q_c cursor for select q_id,answer from Q
open q_c
fetch next from q_c into @q_id,@sql
while @@fetch_status=0 begin set @sql='insert into # select '+rtrim(@q_id)+','+replace(replace(@sql,',',' union all select '+rtrim(@q_id)+','),'=',',') --print @sql
exec(@sql) fetch next from q_c into @q_id,@sql end close q_c deallocate q_c select q_id,s_id,sum(num) as tolcount from # group by q_id,s_id end goexec sp_test go/* q_id s_id tolcount ----------- ----------- ----------- 1 1 2 1 2 2 1 3 2 1 4 0 1 5 0 2 9 2 2 10 2 2 11 2 2 12 2 2 13 0 2 14 0 2 15 0 2 16 0 3 17 2 3 18 2 3 19 2 */drop procedure sp_test drop table Q go
如果需要按照 q_id 来选择统计的内容,可以在存储过程定义中加一个参数。
select q_id,s_id,tolcount=sum(answer) from (select a.q_id,a.s_id ,answer=cast(substring(b.answer,charindex(rtrim(a.s_id)+'=',b.answer)+1,1) as int) from Question_Moption_Item a join Question_MoptionAnswer b on a.q_id=b.q_id ) t group by q_id,s_id order by q_id,s_id
DECLARE @t Table(id int,p_id varchar(20),q_id int,answer varchar(100)) INSERT INTO @t SELECT 29, 'P200711290028' , 1 , '1=1,2=1,3=1,4=0,5=0' UNION ALL SELECT 30, 'P200711290028' , 2 , '9=1,10=1,11=1,12=1,13=0,14=0,15=0,16=0' UNION ALL SELECT 31, 'P200711290028' , 3 , '17=1,18=1,19=1' UNION ALL SELECT 32, 'P200711290029' , 1 , '1=1,2=1,3=1,4=0,5=0' UNION ALL SELECT 33, 'P200711290029' , 2 , '9=1,10=1,11=1,12=1,13=0,14=0,15=0,16=0' UNION ALL SELECT 34, 'P200711290029' , 3 , '17=1,18=1,19=1' SELECT top 100 identity(int,1,1) as Num INTO #t FROM SysobjectsSELECT q_id,LEFT(result,CHARINDEX('=',result)-1) as s_id, sum(case when STUFF(result,1,CHARINDEX('=',result),'')=1 then 1 else 0 end )as total from ( SELECT a.q_id,SUBSTRING(answer,Num,CHARINDEX(',',answer+',',Num+1)-Num) AS Result FROM @t a JOIN #t b ON SUBSTRING(','+answer,Num,1)=',' ) t GROUP BY q_id,result ORDER BY q_id,s_idDROP TABLE #t/* q_id s_id total ----------- ------------------------------ ----------- 1 1 2 1 2 2 1 3 2 1 4 0 1 5 0 2 10 2 2 11 2 2 12 2 2 13 0 2 9 2 3 17 2 3 18 2 3 19 2*/
create table Q(id int,p_id varchar(20),q_id int,answer varchar(40)) insert into Q values(29,'P200711290028',1,rtrim('1=1,2=1,3=1,4=0,5=0 ')) insert into Q values(30,'P200711290028',2,rtrim('9=1,10=1,11=1,12=1,13=0,14=0,15=0,16=0')) insert into Q values(31,'P200711290028',3,rtrim('17=1,18=1,19=1 ')) insert into Q values(32,'P200711290029',1,rtrim('1=1,2=1,3=1,4=0,5=0 ')) insert into Q values(33,'P200711290029',2,rtrim('9=1,10=1,11=1,12=1,13=0,14=0,15=0,16=0')) insert into Q values(34,'P200711290029',3,rtrim('17=1,18=1,19=1 ')) go SELECT a.q_id,b.n s_id,sum(b.v) tolcount FROM ( SELECT q_id,anss= CAST('<r id="' + REPLACE( REPLACE( STUFF(b.ans.value('/R[1]','nvarchar(max)'),1,1,'') , '=' , '">' ) , ',' , '</r><r id="' ) + '</r>' AS XML ) FROM (SELECT DISTINCT q_id FROM q) a CROSS APPLY (SELECT ans=(SELECT N','+answer FROM q WHERE q_id=a.q_id FOR XML PATH(''),ROOT('R'),TYPE))b ) a CROSS APPLY (SELECT n=mm.x.value('@id','int'),v=mm.x.value('.','int') FROM a.anss.nodes('//r') mm(x)) b GROUP BY a.q_id,b.nGO DROP TABLE q GO /* 1 1 2 1 2 2 1 3 2 1 4 0 1 5 0 2 9 2 2 10 2 2 11 2 2 12 2 2 13 0 2 14 0 2 15 0 2 16 0 3 17 2 3 18 2 3 19 2 */
加一个参数: create table Q(id int,p_id varchar(20),q_id int,answer varchar(40)) insert into Q values(29,'P200711290028',1,rtrim('1=1,2=1,3=1,4=0,5=0 ')) insert into Q values(30,'P200711290028',2,rtrim('9=1,10=1,11=1,12=1,13=0,14=0,15=0,16=0')) insert into Q values(31,'P200711290028',3,rtrim('17=1,18=1,19=1 ')) insert into Q values(32,'P200711290029',1,rtrim('1=1,2=1,3=1,4=0,5=0 ')) insert into Q values(33,'P200711290029',2,rtrim('9=1,10=1,11=1,12=1,13=0,14=0,15=0,16=0')) insert into Q values(34,'P200711290029',3,rtrim('17=1,18=1,19=1 '))create table P(p_id varchar(20),d_id varchar(20)) insert into P values('P200711290028','D0001') go create procedure sp_test(@d_id varchar(20)) as begin declare @sql varchar(8000),@q_id int
select 1 as q_id,1 as s_id,1 as num into # from Q delete #
declare q_c cursor for select Q.q_id,Q.answer from Q,P where Q.p_id=P.p_id and P.d_id=@d_id
open q_c
fetch next from q_c into @q_id,@sql
while @@fetch_status=0 begin set @sql='insert into # select '+rtrim(@q_id)+','+replace(replace(@sql,',',' union all select '+rtrim(@q_id)+','),'=',',') --print @sql
exec(@sql) fetch next from q_c into @q_id,@sql end close q_c deallocate q_c select q_id,s_id,sum(num) as tolcount from # group by q_id,s_id end goexec sp_test 'D0001' go/* q_id s_id tolcount ----------- ----------- ----------- 1 1 1 1 2 1 1 3 1 1 4 0 1 5 0 2 9 1 2 10 1 2 11 1 2 12 1 2 13 0 2 14 0 2 15 0 2 16 0 3 17 1 3 18 1 3 19 1 */drop procedure sp_test drop table Q,P go
29 P200711290028 1 1=1,2=1,3=1,4=0,5=0
30 P200711290028 2 9=1,10=1,11=1,12=1,13=0,14=0,15=0,16=0
31 P200711290028 3 17=1,18=1,19=1
32 P200711290029 1 1=1,2=1,3=1,4=0,5=0
33 P200711290029 2 9=1,10=1,11=1,12=1,13=0,14=0,15=0,16=0
34 P200711290029 3 17=1,18=1,19=1
Question_Moption(多项选择主表)
q_id q_name
1 问题标题1
2 问题标题2
3 问题标题3 Question_Moption_Item(多项选择表)
s_id q_id item_name
1 1 产品质量
2 1 造型美观
3 1 服务质量
4 2 他人推荐
5 2 店员推荐
6 2 广告宣传
7 3 满意
8 3 一般
9 3 不满意
那么我想列出以下格式: q_id s_id tolcount(这里为多项选择的总计)
1 1 2
1 2 2
1 3 2
1 4 0
q_id s_id tolcount
1 1 2
insert into Q values(29,'P200711290028',1,rtrim('1=1,2=1,3=1,4=0,5=0 '))
insert into Q values(30,'P200711290028',2,rtrim('9=1,10=1,11=1,12=1,13=0,14=0,15=0,16=0'))
insert into Q values(31,'P200711290028',3,rtrim('17=1,18=1,19=1 '))
insert into Q values(32,'P200711290029',1,rtrim('1=1,2=1,3=1,4=0,5=0 '))
insert into Q values(33,'P200711290029',2,rtrim('9=1,10=1,11=1,12=1,13=0,14=0,15=0,16=0'))
insert into Q values(34,'P200711290029',3,rtrim('17=1,18=1,19=1 '))
gocreate procedure sp_test
as
begin
declare @sql varchar(8000),@q_id int
select 1 as q_id,1 as s_id,1 as num into # from Q
delete #
declare q_c cursor for
select q_id,answer from Q
open q_c
fetch next from q_c into @q_id,@sql
while @@fetch_status=0
begin
set @sql='insert into # select '+rtrim(@q_id)+','+replace(replace(@sql,',',' union all select '+rtrim(@q_id)+','),'=',',')
--print @sql
exec(@sql)
fetch next from q_c into @q_id,@sql
end
close q_c
deallocate q_c select q_id,s_id,sum(num) as tolcount from # group by q_id,s_id
end
goexec sp_test
go/*
q_id s_id tolcount
----------- ----------- -----------
1 1 2
1 2 2
1 3 2
1 4 0
1 5 0
2 9 2
2 10 2
2 11 2
2 12 2
2 13 0
2 14 0
2 15 0
2 16 0
3 17 2
3 18 2
3 19 2
*/drop procedure sp_test
drop table Q
go
select q_id,s_id,tolcount=sum(answer) from
(select a.q_id,a.s_id
,answer=cast(substring(b.answer,charindex(rtrim(a.s_id)+'=',b.answer)+1,1) as int)
from Question_Moption_Item a
join Question_MoptionAnswer b on a.q_id=b.q_id
) t
group by q_id,s_id
order by q_id,s_id
DECLARE @t Table(id int,p_id varchar(20),q_id int,answer varchar(100))
INSERT INTO @t
SELECT
29, 'P200711290028' , 1 , '1=1,2=1,3=1,4=0,5=0'
UNION ALL SELECT
30, 'P200711290028' , 2 , '9=1,10=1,11=1,12=1,13=0,14=0,15=0,16=0'
UNION ALL SELECT
31, 'P200711290028' , 3 , '17=1,18=1,19=1'
UNION ALL SELECT
32, 'P200711290029' , 1 , '1=1,2=1,3=1,4=0,5=0'
UNION ALL SELECT
33, 'P200711290029' , 2 , '9=1,10=1,11=1,12=1,13=0,14=0,15=0,16=0'
UNION ALL SELECT
34, 'P200711290029' , 3 , '17=1,18=1,19=1' SELECT top 100 identity(int,1,1) as Num INTO #t FROM SysobjectsSELECT q_id,LEFT(result,CHARINDEX('=',result)-1) as s_id,
sum(case when STUFF(result,1,CHARINDEX('=',result),'')=1 then 1 else 0 end )as total
from
(
SELECT a.q_id,SUBSTRING(answer,Num,CHARINDEX(',',answer+',',Num+1)-Num) AS Result
FROM @t a JOIN #t b
ON SUBSTRING(','+answer,Num,1)=','
) t
GROUP BY q_id,result
ORDER BY q_id,s_idDROP TABLE #t/*
q_id s_id total
----------- ------------------------------ -----------
1 1 2
1 2 2
1 3 2
1 4 0
1 5 0
2 10 2
2 11 2
2 12 2
2 13 0
2 9 2
3 17 2
3 18 2
3 19 2*/
p_id d_id
P200711290028 D0001为了统计这个经销商的统计数据,那么怎样变为:d_id q_id id tolcount呢?请libin_ftsafe 指点!!!!
insert into Q values(29,'P200711290028',1,rtrim('1=1,2=1,3=1,4=0,5=0 '))
insert into Q values(30,'P200711290028',2,rtrim('9=1,10=1,11=1,12=1,13=0,14=0,15=0,16=0'))
insert into Q values(31,'P200711290028',3,rtrim('17=1,18=1,19=1 '))
insert into Q values(32,'P200711290029',1,rtrim('1=1,2=1,3=1,4=0,5=0 '))
insert into Q values(33,'P200711290029',2,rtrim('9=1,10=1,11=1,12=1,13=0,14=0,15=0,16=0'))
insert into Q values(34,'P200711290029',3,rtrim('17=1,18=1,19=1 '))
go
SELECT a.q_id,b.n s_id,sum(b.v) tolcount
FROM
(
SELECT q_id,anss=
CAST('<r id="'
+
REPLACE(
REPLACE(
STUFF(b.ans.value('/R[1]','nvarchar(max)'),1,1,'')
,
'='
,
'">'
)
,
','
,
'</r><r id="'
)
+
'</r>'
AS XML
)
FROM
(SELECT DISTINCT q_id FROM q) a
CROSS APPLY
(SELECT ans=(SELECT N','+answer FROM q WHERE q_id=a.q_id FOR XML PATH(''),ROOT('R'),TYPE))b
) a
CROSS APPLY
(SELECT n=mm.x.value('@id','int'),v=mm.x.value('.','int') FROM a.anss.nodes('//r') mm(x)) b
GROUP BY a.q_id,b.nGO
DROP TABLE q
GO
/*
1 1 2
1 2 2
1 3 2
1 4 0
1 5 0
2 9 2
2 10 2
2 11 2
2 12 2
2 13 0
2 14 0
2 15 0
2 16 0
3 17 2
3 18 2
3 19 2
*/
create table Q(id int,p_id varchar(20),q_id int,answer varchar(40))
insert into Q values(29,'P200711290028',1,rtrim('1=1,2=1,3=1,4=0,5=0 '))
insert into Q values(30,'P200711290028',2,rtrim('9=1,10=1,11=1,12=1,13=0,14=0,15=0,16=0'))
insert into Q values(31,'P200711290028',3,rtrim('17=1,18=1,19=1 '))
insert into Q values(32,'P200711290029',1,rtrim('1=1,2=1,3=1,4=0,5=0 '))
insert into Q values(33,'P200711290029',2,rtrim('9=1,10=1,11=1,12=1,13=0,14=0,15=0,16=0'))
insert into Q values(34,'P200711290029',3,rtrim('17=1,18=1,19=1 '))create table P(p_id varchar(20),d_id varchar(20))
insert into P values('P200711290028','D0001')
go
create procedure sp_test(@d_id varchar(20))
as
begin
declare @sql varchar(8000),@q_id int
select 1 as q_id,1 as s_id,1 as num into # from Q
delete #
declare q_c cursor for
select Q.q_id,Q.answer from Q,P where Q.p_id=P.p_id and P.d_id=@d_id
open q_c
fetch next from q_c into @q_id,@sql
while @@fetch_status=0
begin
set @sql='insert into # select '+rtrim(@q_id)+','+replace(replace(@sql,',',' union all select '+rtrim(@q_id)+','),'=',',')
--print @sql
exec(@sql)
fetch next from q_c into @q_id,@sql
end
close q_c
deallocate q_c select q_id,s_id,sum(num) as tolcount from # group by q_id,s_id
end
goexec sp_test 'D0001'
go/*
q_id s_id tolcount
----------- ----------- -----------
1 1 1
1 2 1
1 3 1
1 4 0
1 5 0
2 9 1
2 10 1
2 11 1
2 12 1
2 13 0
2 14 0
2 15 0
2 16 0
3 17 1
3 18 1
3 19 1
*/drop procedure sp_test
drop table Q,P
go