这样应该怎样统计?

2008-09-18 03:04:30 186 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

（篇九）C语言统计某个字母的个数、统计各种字符的个数、统计单词的个数文章目录一、统计某个字母的个数二、统计各种字符的个数三、统计单词的个数本篇文章主要介绍在C语言中统计某个字母的个数、统计各种字符的个数和统计单词的个数；总之就是计数，-由于C语言中没有直接统计的函数，因此需要我们自己编写函数来循环遍历查找需要统计的元素。一、统计某个字母的个数 1、参考代码： #include <stdio.h> int main() { int i, k=0;... 阅读详情

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

...全文

统计推断-经典统计推断统计推断-经典统计推断基本问题统计学与概率论贝叶斯统计与经典统计推断模型与推断变量术语解释经典参数估计术语最大似然估计均值和方差的估计置信区间求近似的置信区间基于方差近似估计量的置信区间线性回归最小二乘法合理性贝叶斯线性回归多元线性回归非线性回归线性规划注意事项简单假设检验内曼-皮尔逊引理显著性检验广义似然比和拟合优度检验基本问题统计推断是什么？统计推断是从观测数据推断未知变量或未知模型的... 阅读详情

wecen

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

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

打赏
举报

再多点描述更好。没看明白你的tolCount结果如何来。

zfh198601 2008-09-18

打赏
举报

看不懂

lgxyz 2008-09-18

打赏
举报

BI，看不懂。。。

这样应该怎样统计?

相关推荐