sql 一张回答记录表统计每个题目每个答案的百分比

dwa4821 2017-04-11 10:29:05

...全文

241 3 打赏收藏转发到动态举报

写回复

用AI写文章

3 条回复

切换为时间正序

请发表友善的回复…

发表回复

二月十六版主 2017-04-11

打赏
举报

二月十六版主 2017-04-11

打赏
举报

--测试数据

if not object_id(N'Tempdb..#T') is null

	drop table #T

Go

Create table #T([ques_id] int,[answer_value] int)

Insert #T

select 4,0 union all

select 5,0 union all

select 3,2 union all

select 2,2 union all

select 5,0 union all

select 3,1 union all

select 2,1

Go

--测试数据结束

SELECT  t1.ques_id ,t1.answer_value,

       RTRIM(CONVERT(DECIMAL(10, 2), CONVERT(DECIMAL, t1.num1)

        / CONVERT(DECIMAL, t2.num2))*100)+'%' AS 比例

FROM    ( SELECT    ques_id ,

                    answer_value ,

                    COUNT(answer_value) AS num1

          FROM      #T

          GROUP BY  ques_id ,

                    answer_value

        ) t1

        JOIN ( SELECT   ques_id ,

                        COUNT(1) AS num2

               FROM     #T

               GROUP BY ques_id

             ) t2 ON t1.ques_id = t2.ques_id

二月十六版主 2017-04-11

打赏
举报

--测试数据

if not object_id(N'Tempdb..#T') is null

	drop table #T

Go

Create table #T([ques_id] int,[answer_value] int)

Insert #T

select 4,0 union all

select 5,0 union all

select 3,2 union all

select 2,2 union all

select 5,0 union all

select 3,1 union all

select 2,1

Go

--测试数据结束

SELECT  t1.ques_id ,

       RTRIM(CONVERT(DECIMAL(10, 2), CONVERT(DECIMAL, t1.num1)

        / CONVERT(DECIMAL, t2.num2))*100)+'%' AS 比例

FROM    ( SELECT    ques_id ,

                    answer_value ,

                    COUNT(answer_value) AS num1

          FROM      #T

          GROUP BY  ques_id ,

                    answer_value

        ) t1

        JOIN ( SELECT   ques_id ,

                        COUNT(1) AS num2

               FROM     #T

               GROUP BY ques_id

             ) t2 ON t1.ques_id = t2.ques_id