RESULT
1_a,2_b,3_c
1_a,2_a,3_c
1_a,2_b,3_a
1_a,2_b,3_b
1_b,2_a,3_a
1_a,2_b,3_d
1_a,2_a,3_c
1_a,2_c,3_d
1_a,2_a,3_a
1_a,2_b,3_d
1_b,2_a,3_a
1_a,2_b,3_d
1_a,2_b,3_d
1_a,2_b,3_d
1_a,2_c,3_d
1_c,2_c,3_d
数据库中字段为RESULT的一列数据,意思是3道题的反馈数据,我想通过一条SQL语句或几条SQL语句 得到 第一题 选a的有几人 选b的有几人………… 第二题选a的有几人 选b的有几人…………依次类推。。
1_a,2_b,3_c
1_a,2_a,3_c
1_a,2_b,3_a
1_a,2_b,3_b
1_b,2_a,3_a
1_a,2_b,3_d
1_a,2_a,3_c
1_a,2_c,3_d
1_a,2_a,3_a
1_a,2_b,3_d
1_b,2_a,3_a
1_a,2_b,3_d
1_a,2_b,3_d
1_a,2_b,3_d
1_a,2_c,3_d
1_c,2_c,3_d
数据库中字段为RESULT的一列数据,意思是3道题的反馈数据,我想通过一条SQL语句或几条SQL语句 得到 第一题 选a的有几人 选b的有几人………… 第二题选a的有几人 选b的有几人…………依次类推。。
count(case when charindex('1_b',result)=1 then 1 ) as 第一题选b,
count(case when charindex('1_c',result)=1 then 1 ) as 第一题选c,
count(case when charindex('1_d',result)=1 then 1 ) as 第一题选d,
from tb
-- Author: liangCK 小梁
---------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (RESULT VARCHAR(50))
INSERT INTO @T
SELECT '1_a,2_b,3_c' UNION ALL
SELECT '1_a,2_a,3_c' UNION ALL
SELECT '1_a,2_b,3_a' UNION ALL
SELECT '1_a,2_b,3_b' UNION ALL
SELECT '1_b,2_a,3_a' UNION ALL
SELECT '1_a,2_b,3_d' UNION ALL
SELECT '1_a,2_a,3_c' UNION ALL
SELECT '1_a,2_c,3_d' UNION ALL
SELECT '1_a,2_a,3_a' UNION ALL
SELECT '1_a,2_b,3_d' UNION ALL
SELECT '1_b,2_a,3_a' UNION ALL
SELECT '1_a,2_b,3_d' UNION ALL
SELECT '1_a,2_b,3_d' UNION ALL
SELECT '1_a,2_b,3_d' UNION ALL
SELECT '1_a,2_c,3_d' UNION ALL
SELECT '1_c,2_c,3_d'--SQL查询如下:SELECT
LEFT(Result,CHARINDEX('_',Result)-1) AS Result,
COUNT(*) AS 总数,
SUM(CASE WHEN CHARINDEX('a',Result)>0 THEN 1 ELSE 0 END) AS 选A的人数,
SUM(CASE WHEN CHARINDEX('b',Result)>0 THEN 1 ELSE 0 END) AS 选B的人数,
SUM(CASE WHEN CHARINDEX('c',Result)>0 THEN 1 ELSE 0 END) AS 选C的人数,
SUM(CASE WHEN CHARINDEX('d',Result)>0 THEN 1 ELSE 0 END) AS 选D的人数
FROM (
SELECT
PARSENAME(REPLACE(RESULT,',','.'),3) AS Result
FROM @T
UNION ALL
SELECT
PARSENAME(REPLACE(RESULT,',','.'),2) AS Result
FROM @T
UNION ALL
SELECT
PARSENAME(REPLACE(RESULT,',','.'),1) AS Result
FROM @T
) AS A
GROUP BY LEFT(Result,CHARINDEX('_',Result)-1)/*
Result 总数 选A的人数 选B的人数 选C的人数 选D的人数
-------------- ----------- ----------- ----------- ----------- -----------
1 16 13 2 1 0
2 16 5 8 3 0
3 16 4 1 3 8(3 行受影响)*/
正解
select count(case when charindex('1_a',result)=1 then 1 ) as 第一题选a,
count(case when charindex('1_b',result)=1 then 1 ) as 第一题选b,
count(case when charindex('1_c',result)=1 then 1 ) as 第一题选c,
count(case when charindex('1_d',result)=1 then 1 ) as 第一题选d,
from tb
INSERT INTO #t
SELECT '1_a,2_b,3_c' UNION ALL
SELECT '1_a,2_a,3_c' UNION ALL
SELECT '1_a,2_b,3_a' UNION ALL
SELECT '1_a,2_b,3_b' UNION ALL
SELECT '1_b,2_a,3_a' UNION ALL
SELECT '1_a,2_b,3_d' UNION ALL
SELECT '1_a,2_a,3_c' UNION ALL
SELECT '1_a,2_c,3_d' UNION ALL
SELECT '1_a,2_a,3_a' UNION ALL
SELECT '1_a,2_b,3_d' UNION ALL
SELECT '1_b,2_a,3_a' UNION ALL
SELECT '1_a,2_b,3_d' UNION ALL
SELECT '1_a,2_b,3_d' UNION ALL
SELECT '1_a,2_b,3_d' UNION ALL
SELECT '1_a,2_c,3_d' UNION ALL
SELECT '1_c,2_c,3_d'select title as 题目, ans 答案, count(1) 人数
from
(
select left(RESULT,1) as title,substring(RESULT,3,1) as ans from #t
union all
select substring(RESULT,5,1),substring(RESULT,7,1) from #t
union all
select substring(RESULT,9,1),substring(RESULT,11,1) from #t) t
group by title,ans
order by title,ans/**
题目 答案 人数
---- ---- -----------
1 a 13
1 b 2
1 c 1
2 a 5
2 b 8
2 c 3
3 a 4
3 b 1
3 c 3
3 d 8(所影响的行数为 10 行)
**/
这种格式应该好看很多吧
6楼的很完美select title as 题目, ans 答案, count(1) 人数
from
(
select left(RESULT,1) as title,substring(RESULT,3,1) as ans from #t
union all
select substring(RESULT,5,1),substring(RESULT,7,1) from #t
union all
select substring(RESULT,9,1),substring(RESULT,11,1) from #t) t
group by title,ans
order by title,ans如果是多项选择题的话,另当别论
INSERT INTO tb
SELECT '1_a,2_b,3_c' UNION ALL
SELECT '1_a,2_a,3_c' UNION ALL
SELECT '1_a,2_b,3_a' UNION ALL
SELECT '1_a,2_b,3_b' UNION ALL
SELECT '1_b,2_a,3_a' UNION ALL
SELECT '1_a,2_b,3_d' UNION ALL
SELECT '1_a,2_a,3_c' UNION ALL
SELECT '1_a,2_c,3_d' UNION ALL
SELECT '1_a,2_a,3_a' UNION ALL
SELECT '1_a,2_b,3_d' UNION ALL
SELECT '1_b,2_a,3_a' UNION ALL
SELECT '1_a,2_b,3_d' UNION ALL
SELECT '1_a,2_b,3_d' UNION ALL
SELECT '1_a,2_b,3_d' UNION ALL
SELECT '1_a,2_c,3_d' UNION ALL
SELECT '1_c,2_c,3_d'select timu , daan , count(*) cnt from
(
select left(parsename(replace(result,',','.'),3),1) timu, right(parsename(replace(result,',','.'),3),1) daan from tb
union all
select left(parsename(replace(result,',','.'),2),1) timu, right(parsename(replace(result,',','.'),2),1) daan from tb
union all
select left(parsename(replace(result,',','.'),1),1) timu, right(parsename(replace(result,',','.'),1),1) daan from tb
) t
group by timu , daan
order by timu , daandrop table tb/*
timu daan cnt
---- ---- -----------
1 a 13
1 b 2
1 c 1
2 a 5
2 b 8
2 c 3
3 a 4
3 b 1
3 c 3
3 d 8(所影响的行数为 10 行)
*/
INSERT INTO tb
SELECT '1_a,2_b,3_c' UNION ALL
SELECT '1_a,2_a,3_c' UNION ALL
SELECT '1_a,2_b,3_a' UNION ALL
SELECT '1_a,2_b,3_b' UNION ALL
SELECT '1_b,2_a,3_a' UNION ALL
SELECT '1_a,2_b,3_d' UNION ALL
SELECT '1_a,2_a,3_c' UNION ALL
SELECT '1_a,2_c,3_d' UNION ALL
SELECT '1_a,2_a,3_a' UNION ALL
SELECT '1_a,2_b,3_d' UNION ALL
SELECT '1_b,2_a,3_a' UNION ALL
SELECT '1_a,2_b,3_d' UNION ALL
SELECT '1_a,2_b,3_d' UNION ALL
SELECT '1_a,2_b,3_d' UNION ALL
SELECT '1_a,2_c,3_d' UNION ALL
SELECT '1_c,2_c,3_d'--竖向显示
select timu , daan , count(*) cnt from
(
select left(parsename(replace(result,',','.'),3),1) timu, right(parsename(replace(result,',','.'),3),1) daan from tb
union all
select left(parsename(replace(result,',','.'),2),1) timu, right(parsename(replace(result,',','.'),2),1) daan from tb
union all
select left(parsename(replace(result,',','.'),1),1) timu, right(parsename(replace(result,',','.'),1),1) daan from tb
) t
group by timu , daan
order by timu , daan
/*
timu daan cnt
---- ---- -----------
1 a 13
1 b 2
1 c 1
2 a 5
2 b 8
2 c 3
3 a 4
3 b 1
3 c 3
3 d 8(所影响的行数为 10 行)
*/--横向显示
select timu ,
sum(case daan when 'A' then 1 else 0 end) [A],
sum(case daan when 'B' then 1 else 0 end) [B],
sum(case daan when 'C' then 1 else 0 end) [C],
sum(case daan when 'D' then 1 else 0 end) [D]
from
(
select left(parsename(replace(result,',','.'),3),1) timu, right(parsename(replace(result,',','.'),3),1) daan from tb
union all
select left(parsename(replace(result,',','.'),2),1) timu, right(parsename(replace(result,',','.'),2),1) daan from tb
union all
select left(parsename(replace(result,',','.'),1),1) timu, right(parsename(replace(result,',','.'),1),1) daan from tb
) t
group by timu
order by timu
/*
timu A B C D
---- ----------- ----------- ----------- -----------
1 13 2 1 0
2 5 8 3 0
3 4 1 3 8(所影响的行数为 3 行)
*/drop table tb