求正确率语句，在线等，谢谢
本帖最后由 delmores 于 2013-07-17 00:10:08 编辑 sql 语句查询5个选择题选择ABCD正确率和错误率
张三 D;C;D;A;A
李四 D;B;C;B;B
王五 D;C;B;B;A
标准答案 B,C,B,A,C
求每个这五个选择题每个题选对的正确率和错误率
[解决办法]
select sum(case when col1='B' then 1 else 0 end)/count(*) as A正确率,
sum(case when col1<>'B' then 1 else 0 end)/count(*) as A错误率,
.......
from tb
[解决办法]
--创建自定义函数：
CREATE FUNCTION [dbo].[f_split]
(
@c VARCHAR(MAX) ,
@split VARCHAR(2)
)
RETURNS @t TABLE ( col VARCHAR(20) )
AS
BEGIN
WHILE ( CHARINDEX(@split, @c) <> 0 )
BEGIN
INSERT @t
( col
)
VALUES ( SUBSTRING(@c, 1, CHARINDEX(@split, @c) - 1)
)
SET @c = STUFF(@c, 1, CHARINDEX(@split, @c), '')
END
INSERT @t
( col )
VALUES ( @c )
RETURN
END
GO
--查询代码：
IF object_id('tempdb..#a1') is not null
DROP table #a1;
IF object_id('tempdb..#a2') is not null
DROP table #a2;
IF object_id('tempdb..#a3') is not null
DROP table #a3;
SELECT *,ROW_NUMBER() OVER (ORDER BY GETDATE()) re
INTO #a1
FROM
(
SELECT '张三' NAME,'D;C;D;A;A' answer UNION all
SELECT '李四','D;B;C;B;B' UNION all
SELECT '王五','D;C;B;B;A'
) a
SELECT 'B,C,B,A,C' answer INTO #a2
SELECT TOP 0 name,CAST('' AS CHAR(1)) answer INTO #a3 FROM #a1
DECLARE @i1 INT,@i2 INT
SELECT @i1=1,@i2=MAX(re) FROM #a1
WHILE @i1<=@i2
BEGIN
INSERT INTO #a3
select (SELECT name FROM #a1 WHERE re=@i1),*
from dbo.f_split((SELECT answer FROM #a1 WHERE re=@i1),';')
SET @i1=@i1+1
END
;
WITH a1 AS
(
SELECT a.NAME,a.answer,b.col AS std_answer
from
(SELECT *,ROW_NUMBER() OVER (PARTITION BY name ORDER BY GETDATE()) re FROM #a3) a
INNER JOIN
(select *,ROW_NUMBER() OVER (ORDER BY GETDATE()) re from dbo.f_split((SELECT * FROM #a2),',')) b
ON a.re=b.re
)
,a2 AS
(
SELECT name
,SUM(CASE WHEN answer=std_answer THEN 1 ELSE 0 END) t_answer
,SUM(CASE WHEN answer=std_answer THEN 0 ELSE 1 END) f_answer
FROM a1
GROUP BY name
)
SELECT name
,CAST(1.0*t_answer/(t_answer+f_answer)*100 AS int) t_p
,CAST(1.0*f_answer/(t_answer+f_answer)*100 AS int) f_p
FROM a2
