如何实现这样的统计

xmvb 2004-09-10 08:53:11
有如下调查问卷选择题:

1、你是哪个年龄段:
A 18-30 B 31-40 C 41-50 D 51-60

2、你的性别
A 男 B 女

.....

37...

一共37题,选择最大字母到H,答案可多选。

现我用一个表TABLE1输入数据:

1 2 3 4...37
------------------
A C B D...A
A B A A...D
AB E CD C...B
....

另一个表TABLE2用于输入文字资料:

题目 A B C .... H
------------------------------------------------------------------------------
你是哪个年龄段 18-30 31-40 41-50 51-60
你的性别 男 女
.....

我现在想破头也想不出如何利用这两个表得出以下统计表结果:

题目 A 选A合计 B 选B合计 C 选C合计 .... H 选H合计
-------------------------------------------------------------------------------------
你是哪个年龄段 18-30 12 31-40 23 41-50 18 ....
你的性别 男 35 女 12
......

请高手帮帮我!


...全文
90 10 打赏 收藏 转发到动态 举报
写回复
用AI写文章
10 条回复
切换为时间正序
请发表友善的回复…
发表回复
huwgao 2004-09-11
  • 打赏
  • 举报
回复
--分离多选
declare @i int
set @i=2
while @i<=(select max(len(v)) from #t1)
begin
set @s='insert into #t1 select qid,v=substring(v,'+cast(@i as varchar(1))+',1) from #t1 where len(v)>='+cast(@i as varchar(1)) --将=条件改为>=条件
exec(@s)
set @i=@i+1
end
update #t1 set v=left(v,1) where len(v)>1
xmvb 2004-09-11
  • 打赏
  • 举报
回复
十分感谢!
huwgao 2004-09-10
  • 打赏
  • 举报
回复
--另解--

--构造测试数据,以4个题目为例
create table t1([1] varchar(10),[2] varchar(10),[3] varchar(10),[4] varchar(10))
create table t2(题目 varchar(100),A varchar(10),B varchar(10),C varchar(10),D varchar(10))
insert into t1 select 'A','C','B','D'
union all select 'A','B','A','A'
union all select 'AB','C','CD','B'
insert into t2 select '你是哪个年龄段','18-30','31-40','41-50','51-60'
union all select '你的性别','男','女','',''
union all select '你的职位','学生','职员','经理',''
union all select '你的收入','<1000','1000-2000','2000-3000','>3000'

--首先你的t2表必须有【题目id】字段,与t1表的字段(1、2……)对应
--因为你的表没有,先用临时表构造id字段
--假设了题目id是按默认顺序排列下来的
select qid=identity(int,1,1),* into #t2 from t2

--合并答案表
declare @s varchar(8000)
set @s=''
select @s=@s+' union all select qid='+name+',v=['+name+'] from t1'
from syscolumns
where id=object_id('t1')
order by colid
set @s=stuff(@s,1,10,'insert into #t1')

create table #t1(qid int,v varchar(10))
exec(@s)

--分离多选
declare @i int
set @i=2
while @i<=(select max(len(v)) from #t1)
begin
print 'insert into #t1 select qid,v=substring(v,'+cast(@i as varchar(1))+',1) from #t1 where len(v)='+cast(@i as varchar(1))
set @s='insert into #t1 select qid,v=substring(v,'+cast(@i as varchar(1))+',1) from #t1 where len(v)='+cast(@i as varchar(1))
exec(@s)
set @i=@i+1
end
update #t1 set v=left(v,1) where len(v)>1

select 题目,A,选A合计,B,选B合计,C,选C合计,D,选D合计
--,E,选E合计,F,选F合计,G,选G合计,H,选H合计
from #t2 a join (
select qid
,选A合计=sum(case v when 'A' then 1 else 0 end)
,选B合计=sum(case v when 'B' then 1 else 0 end)
,选C合计=sum(case v when 'C' then 1 else 0 end)
,选D合计=sum(case v when 'D' then 1 else 0 end)
,选E合计=sum(case v when 'E' then 1 else 0 end)
,选F合计=sum(case v when 'F' then 1 else 0 end)
,选G合计=sum(case v when 'G' then 1 else 0 end)
,选H合计=sum(case v when 'H' then 1 else 0 end)
from #t1
group by qid
) b on a.qid=b.qid

drop table #t1,#t2
drop table t1,t2
huwgao 2004-09-10
  • 打赏
  • 举报
回复
楼上的掉了sum:
from (
sum(select case when ['+cast(@i as varchar(10))+'] like ''%A%'' then 1 else 0 end) as 选A合计,
sum(case when ['+cast(@i as varchar(10))+'] like ''%B%'' then 1 else 0 end) as 选B合计,
……………………

CSDMN 2004-09-10
  • 打赏
  • 举报
回复
不好意思,上面的回复是误操作的,中间结果,乱七八糟

现在有个解法,应该要写个存储过程:

create proc pr_query
as
set nocount on

select IDENTITY(int,1,1) as id,*,0 as 选A合计,0 as 选B合计
,0 as 选C合计,0 as 选D合计,0 as 选E合计,0 as 选F合计,0 as 选G合计,0 as 选H合计
into #t from table2

declare @i int
declare @sql varchar(2000)
set @i=1

while @i<=37
begin
set @sql='update #t
set 选A合计=t.选A合计,选B合计=t.选B合计,选C合计=t.选C合计,
选D合计=t.选D合计,选E合计=t.选E合计,选F合计=t.选F合计,
选G合计=t.选G合计,选H合计=t.选H合计
from (
select case when ['+cast(@i as varchar(10))+'] like ''%A%'' then 1 else 0 end) as 选A合计,
case when ['+cast(@i as varchar(10))+'] like ''%B%'' then 1 else 0 end) as 选B合计,
case when ['+cast(@i as varchar(10))+'] like ''%C%'' then 1 else 0 end) as 选C合计,
case when ['+cast(@i as varchar(10))+'] like ''%D%'' then 1 else 0 end) as 选D合计,
case when ['+cast(@i as varchar(10))+'] like ''%E%'' then 1 else 0 end) as 选E合计,
case when ['+cast(@i as varchar(10))+'] like ''%F%'' then 1 else 0 end) as 选F合计,
case when ['+cast(@i as varchar(10))+'] like ''%G%'' then 1 else 0 end) as 选G合计,
case when ['+cast(@i as varchar(10))+'] like ''%H%'' then 1 else 0 end) as 选H合计
from table1
) as t,#t x
where x.id='+cast(@i as varchar(10))
exec(@sql)
set @i=@i+1
end

select
题目,
A,
选A合计,
B,
选B合计,
C,
选C合计,
D,
选D合计,
E,
选E合计,
F,
选F合计,
G,
选G合计,
H,
选H合计
from #t
order by id

drop table #t
go
CSDMN 2004-09-10
  • 打赏
  • 举报
回复

table1是答题表?

你的table2怎么没有题号呀



select IDENTITY(int,1,1) as id,*,0 as 选A合计,0 as 选B合计
,0 as 选C合计,0 as 选D合计,0 as 选E合计,0 as 选F合计,0 as 选G合计,0 as 选H合计
into #t from table2

declare @i int
declare @sql varchar(2000)
set @i=1

while @i<=37
begin
update #t
set 选A合计=t.选A合计,选B合计=t.选B合计,选C合计=t.选C合计,
选D合计=t.选D合计,选E合计=t.选E合计,选F合计=t.选F合计,
选G合计=t.选G合计,选H合计=t.选H合计
from (
select case when [1] like '%A%' then 1 else 0 end) as 选A合计,
case when [1] like '%B%' then 1 else 0 end) as 选B合计,
case when [1] like '%C%' then 1 else 0 end) as 选C合计,
case when [1] like '%D%' then 1 else 0 end) as 选D合计,
case when [1] like '%E%' then 1 else 0 end) as 选E合计,
case when [1] like '%F%' then 1 else 0 end) as 选F合计,
case when [1] like '%G%' then 1 else 0 end) as 选G合计,
case when [1] like '%H%' then 1 else 0 end) as 选H合计
from table1
) as t,#t x
where x.id=1

select
题目,
A,
(select count(*) from table1 where [1] like '%A%') as 选A合计
B, C .... H
from #t
order by id
from table2 a,table1 b
CSDMN 2004-09-10
  • 打赏
  • 举报
回复
选A合计怎么计算?
你少了一个答题表吧??
Andy__Huang 2004-09-10
  • 打赏
  • 举报
回复
或者把用“出生日期”來代替“年齡”,知道出生日期了,當然可以得知年齡。
Andy__Huang 2004-09-10
  • 打赏
  • 举报
回复
這個是你表設計的問題,不用什麼問你是哪個年齡段,只要輸入年齡就可以了。

表的設計
id 姓名 性別 年齡
................
.....................
.........

這樣設置就夠了,合計到你統計時,再用sql語句找它出來。
而且有時候你的統計年齡段不是你現在規定的,

guanhua0206 2004-09-10
  • 打赏
  • 举报
回复
可以通两表之间相互关联的方法来计算结果
比如:select

27,579

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧