这样应该怎样统计?

wecen 2008-09-18 03:04:30

Question_MoptionAnswer表(多项选择答案表)

id p_id q_id answer(这里的格试是 s_id=答案,用逗号分开)
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

...全文

136 15 打赏收藏转发到动态举报

写回复

用AI写文章

15 条回复

切换为时间正序

请发表友善的回复…

发表回复

wecen 2008-09-18

打赏
举报

多谢大家,问题解决了,都给分!

anovice 2008-09-18

打赏
举报

拆分表

wecen 2008-09-18

打赏
举报

想问下,你们是怎样的思考的过程,而写出这些过程?

子陌红尘 2008-09-18

打赏
举报

加一个参数：



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

go



exec 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

fcuandy 2008-09-18

打赏
举报

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.n



GO

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

*/

wecen 2008-09-18

打赏
举报

谢谢大家的回复,特别是libin_ftsafe

如果我还有一个表

Projects(项目表)

项目单号经销商编号
p_id d_id
P200711290028 D0001

为了统计这个经销商的统计数据,那么怎样变为:

d_id q_id id tolcount呢?

请libin_ftsafe 指点!!!!

Garnett_KG 2008-09-18

打赏
举报



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 Sysobjects



SELECT 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_id



DROP 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



*/

等不到来世 2008-09-18

打赏
举报



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

子陌红尘 2008-09-18

打赏
举报

如果需要按照 q_id 来选择统计的内容，可以在存储过程定义中加一个参数。

子陌红尘 2008-09-18

打赏
举报

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



create 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

go



exec 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

wecen 2008-09-18

打赏
举报

tolcount的结果是将answer字符串中,当1=1(格式 S_ID=答案 ,用逗号分开) 出现两次 tolcount=2

标题ID 选项ID 总计
q_id s_id tolcount
1 1 2

wecen 2008-09-18

打赏
举报

以下是表的结构

Question_MoptionAnswer表(多项选择答案表)

id p_id q_id answer(这里的格试是 s_id=答案,用逗号分开)
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

fcuandy 2008-09-18