表answer 表xxb
姓名 问卷号 题号 回答内容 答题时间 题号 选项张三 1 1 a,b,c .. 1 a
张三 1 2 a .. 1 b
ff 1 1 a,c,d .. 1 c
gg 1 2 b .. 1 d
2 a
2 b
3 a
3 b
怎么统计成
题号 选项 次数
1 a 2
1 b 1
1 c 2
1 d 1
2 a 1
2 b 1
3 a 0
3 b 0
姓名 问卷号 题号 回答内容 答题时间 题号 选项张三 1 1 a,b,c .. 1 a
张三 1 2 a .. 1 b
ff 1 1 a,c,d .. 1 c
gg 1 2 b .. 1 d
2 a
2 b
3 a
3 b
怎么统计成
题号 选项 次数
1 a 2
1 b 1
1 c 2
1 d 1
2 a 1
2 b 1
3 a 0
3 b 0
go
create table answer(姓名 varchar(10),问卷号 int,题号 int,回答内容 varchar(100),答题时间 datetime)
insert into answer
select '张三',1,1,'a,b,c',NULL
union all select '张三',1,2,'a',NULL
union all select 'ff',1,1,'a,c,d',NULL
union all select 'gg',1,2,'b',NULLcreate table xxb(题号 int,选项 varchar(10))
insert into xxb
select 1,'a'
union all select 1,'b'
union all select 1,'c'
union all select 1,'d'
union all select 2,'a'
union all select 2,'b'
union all select 3,'a'
union all select 3,'b'select *,(select count(*) from answer where charindex(选项,回答内容)>0 and answer.题号=xxb.题号) as '次数'
from xxb
/*
题号 选项 次数
----------- ---------- -----------
1 a 2
1 b 1
1 c 2
1 d 1
2 a 1
2 b 1
3 a 0
3 b 0(所影响的行数为 8 行)
*/
insert into @a select '张三',1,1,'a,b,c'
insert into @a select '张三',1,2,'a '
insert into @a select 'ff ',1,1,'a,c,d'
insert into @a select 'gg ',1,2,'b 'declare @b table(id int,answer char(1))
insert into @b select 1,'a'
insert into @b select 1,'b'
insert into @b select 1,'c'
insert into @b select 1,'d'
insert into @b select 2,'a'
insert into @b select 2,'b'
insert into @b select 3,'a'
insert into @b select 3,'b'select
b.id,b.answer,isnull(count(a.id),0)
from
@b b
left join
@a a
on
a.id=b.id and charindex(','+b.answer+',',','+a.answer+',')>0
group by
b.id,b.answer
order by
b.id,b.answer/*
id answer
----------- ------ -----------
1 a 2
1 b 1
1 c 2
1 d 1
2 a 0
2 b 0
3 a 0
3 b 0
*/
from
(
select 题号,选项,回答内容,case when charindex(选项,回答内容)>0 then 1 else 0 end as num
from
(select xxxb.*,answer.回答内容 from xxxb left outer join answer on xxxb.题号=answer.题号) bb) cc
group by 题号,选项
order by 题号
(
姓名 varchar(10),
问卷号 int ,
题号 int,
回答内容 varchar(20)
)
insert into answer
select '张三',1,1,'a,b,c' union all
select '张三',1,2,'a' union all
select 'ff',1,1,'a,c,d' union all
select 'gg',1,2,'b'
create table xxb
(
题号 int,
选项 varchar(20)
)
insert into xxb
select 1,'a' union all
select 1,'b' union all
select 1,'c' union all
select 1,'d' union all
select 2,'a' union all
select 2,'b' union all
select 3,'a' union all
select 3,'b'
go
select 题号,选项,(select count(*) from answer where 题号=xxb.题号 and charindex(','+xxb.选项+',',','+回答内容+',')>0) cnt from xxb
--结果
题号 选项 cnt
----------- -------------------- -----------
1 a 2
1 b 1
1 c 2
1 d 1
2 a 1
2 b 1
3 a 0
3 b 0(8 行受影响)