SELECT Rate = 1.0*SUM(CASE WHEN arat = brat THEN 1 ELSE 0 END)/COUNT(*) FROM ( SELECT a.[user id], a.age, a.gender, a.genre, arat = a.rating, brat = b.rating FROM 原表 a INNER JOIN 预测表 b ON a.[user id] = b.[user id] AND a.age = b.age AND a.gender = b.gender AND a.genre = b.genre ) c
declare @table1 table (userid int,age int,gender varchar(1),col varchar(7),genre varchar(8),rating int) insert into @table1 select 849,13,'f','student','action',5 union all select 849,13,'f','student','children',5 union all select 849,13,'f','student','horror',5 union all select 849,13,'f','student','war',5 union all select 849,13,'f','student','drama',5declare @table2 table (userid int,age int,gender varchar(1),col varchar(7),genre varchar(8),rating int) insert into @table2 select 849,13,'f','student','action',5 union all select 849,13,'f','student','children',1 union all select 849,13,'f','student','horror',1 union all select 849,13,'f','student','war',5 union all select 849,13,'f','student','drama',1SELECT a.rating,b.rating,CASe WHEN a.rating=b.rating THEN 1 ELSE 0 END iscurrent FROM @table1 a LEFT JOIN @table2 b ON a.userid =b.userid and a.age =b.age and a.gender =b.gender and a.genre=b.genre /* rating rating iscurrent ----------- ----------- ----------- 5 5 1 5 1 0 5 1 0 5 5 1 5 1 0 */你嵌套一下,统计个数就行了
(
SELECT a.[user id], a.age, a.gender, a.genre, arat = a.rating, brat = b.rating FROM
原表 a
INNER JOIN
预测表 b
ON a.[user id] = b.[user id] AND a.age = b.age AND a.gender = b.gender AND a.genre = b.genre
) c
declare @table1 table
(userid int,age int,gender varchar(1),col varchar(7),genre varchar(8),rating int)
insert into @table1
select 849,13,'f','student','action',5 union all
select 849,13,'f','student','children',5 union all
select 849,13,'f','student','horror',5 union all
select 849,13,'f','student','war',5 union all
select 849,13,'f','student','drama',5declare @table2 table
(userid int,age int,gender varchar(1),col varchar(7),genre varchar(8),rating int)
insert into @table2
select 849,13,'f','student','action',5 union all
select 849,13,'f','student','children',1 union all
select 849,13,'f','student','horror',1 union all
select 849,13,'f','student','war',5 union all
select 849,13,'f','student','drama',1SELECT a.rating,b.rating,CASe WHEN a.rating=b.rating THEN 1 ELSE 0 END iscurrent FROM @table1 a LEFT JOIN @table2 b
ON
a.userid =b.userid and
a.age =b.age and
a.gender =b.gender and
a.genre=b.genre
/*
rating rating iscurrent
----------- ----------- -----------
5 5 1
5 1 0
5 1 0
5 5 1
5 1 0
*/你嵌套一下,统计个数就行了
declare @a int
declare @b intselect @a = count(*) from 预测表
select @b = count(*) from 原表 a,预测表 b where a.[user id] = b.[user id] AND a.age = b.age AND a.gender = b.gender AND a.genre = b.genre ANDa.rating = b.ratingselect @b/@a*100