sql 嵌套统计性能问题。求高手解答 急！急急急急急！！

Users 表
UserID   LoginName
 1         张三
 2         李四
 3          aaaa
 4          asdf
 5          asdf23
-------------------
成绩(Scores)
id    cheji    userid    Createdtime  
1     50        1        2012-01-07
3     50        1        2012-01-08
4     50        1        2012-01-09
5     50        1        2012-04-07
6     50        2        2012-04-10
7     50        2        2012-04-22
8     50        2        2012-05-07
9     50        2        2012-07-07
------------------------------------------

统计每个用户前 3期成绩。并按3期总成绩排名

我现在写的这个表性能很低。我现在表数据 Users  Scores 表数据都是上万。
下面是我自己写的。性能很低。求更高效SQL代码 

select  top 10 Users.LoginName,Users.UserID,(select Sum(cheji)as Grade from  Scores
where  id in (select top 3 ID  from  Scores where UserID=Users.UserID and  order by Createdtime  desc) ) as  Grade
from  Users   order by  Grade  desc

7 个解决方案

#1

补充：统计每个用户最新 3期成绩。

#2

--try
declare @Users table(UserID int ,LoginName nvarchar(8))
insert into @users 
select 1,'张三' union all
select 2 ,'李四' union all
select 3 ,'aaaa' union all
select 4 ,'asdf' union all
select 5 ,'asdf23' 

declare @Scores table (id int,cheji int,userid int,createdtime datetime)
insert into @Scores
select 1 ,50 ,1 ,'2012-01-07' union all
select 3 ,20 ,1 ,'2012-01-08' union all
select 4 ,50 ,1 ,'2012-01-09' union all
select 5 ,50 ,1 ,'2012-04-07' union all
select 6 ,50, 2 ,'2012-04-10' union all
select 7 ,50 ,2 ,'2012-04-22' union all
select 8 ,50 ,2 ,'2012-05-07' union all
select 9 ,50 ,2 ,'2012-07-07' 

;with t as (
	select row_number() over (partition by a.UserID order by b.createdtime desc )   as Px,
		   a.UserID,a.LoginName,b.cheji
	from @Users a  left join @Scores b on a.UserID = b.userid
),t2 as 
(
	select * from t where px <=3 
)
select UserID,LoginName,sum(cheji) as cheji from t2
group by UserID,LoginName
order by sum(cheji) desc

#3

---------------------------------  
--  Author: liangCK 小梁  
--  Title : 查每个分组前N条记录  
--  Date  : 2008-11-13 17:19:23  
---------------------------------  
   
--> 生成测试数据: #T  
IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T  
CREATE TABLE #T (ID VARCHAR(3),GID INT,Author VARCHAR(29),Title VARCHAR(39),Date DATETIME)  
INSERT INTO #T  
SELECT '001',1,'邹建','深入浅出SQLServer2005开发管理与应用实例','2008-05-10' UNION ALL  
SELECT '002',1,'胡百敬','SQLServer2005性能调校','2008-03-22' UNION ALL  
SELECT '003',1,'格罗夫Groff.J.R.','SQL完全手册','2009-07-01' UNION ALL  
SELECT '004',1,'KalenDelaney','SQLServer2005技术内幕存储引擎','2008-08-01' UNION ALL  
SELECT '005',2,'Alex.Kriegel.Boris.M.Trukhnov','SQL宝典','2007-10-05' UNION ALL  
SELECT '006',2,'飞思科技产品研发中心','SQLServer2000高级管理与开发','2007-09-10' UNION ALL  
SELECT '007',2,'胡百敬','SQLServer2005数据库开发详解','2008-06-15' UNION ALL  
SELECT '008',3,'陈浩奎','SQLServer2000存储过程与XML编程','2005-09-01' UNION ALL  
SELECT '009',3,'赵松涛','SQLServer2005系统管理实录','2008-10-01' UNION ALL  
SELECT '010',3,'黄占涛','SQL技术手册','2006-01-01'  
--SQL查询如下:  
--按GID分组,查每个分组中Date最新的前2条记录  
  
--1.字段ID唯一时:  
SELECT * FROM #T AS T WHERE ID IN(SELECT TOP 2 ID FROM #T WHERE GID=T.GID ORDER BY Date DESC)  
--2.如果ID不唯一时:  
SELECT * FROM #T AS T WHERE 2>(SELECT COUNT(*) FROM #T WHERE GID=T.GID AND Date>T.Date)  
--SQL Server 2005 使用新方法  
--3.使用ROW_NUMBER()进行排位分组  
SELECT ID,GID,Author,Title,Date  
FROM  
(  
   SELECT rid=ROW_NUMBER() OVER(PARTITION BY GID ORDER BY Date DESC),*  
   FROM #T  
) AS T  
WHERE rid<=2  
--4.使用APPLY  
SELECT DISTINCT b.*  
FROM #T AS a  
CROSS APPLY  
(  
    SELECT TOP(2) * FROM #T WHERE a.GID=GID ORDER BY Date DESC  
) AS b  
  
--结果  
/* 
ID   GID         Author                        Title                                   Date 
---- ----------- ----------------------------- --------------------------------------- ----------------------- 
003  1           格罗夫Groff.J.R.                 SQL完全手册                                 2009-07-01 00:00:00.000 
004  1           KalenDelaney                  SQLServer2005技术内幕存储引擎                   2008-08-01 00:00:00.000 
005  2           Alex.Kriegel.Boris.M.Trukhnov SQL宝典                                   2007-10-05 00:00:00.000 
007  2           胡百敬                           SQLServer2005数据库开发详解                    2008-06-15 00:00:00.000 
009  3           赵松涛                           SQLServer2005系统管理实录                     2008-10-01 00:00:00.000 
010  3           黄占涛                           SQL技术手册                                 2006-01-01 00:00:00.000 
(6 行受影响) 
*/  

#4

把子查询改成联接会好点

SELECT TOP 10  u.*, t.Grade
FROM    [Users] u
LEFT JOIN (
            SELECT  SUM(cheji) AS Grade, UserID
            FROM    Scores AS a
            WHERE   id IN ( 
			    SELECT TOP 3 ID
                            FROM    Scores
                            WHERE   UserID = a.UserID
                            ORDER BY Createdtime DESC 
                           )
            GROUP BY UserID
          ) AS t
ON      u.UserID = t.UserID
ORDER BY t.Grade DESC

你要确保一下几项满足 速度才会飙起来。
【Scores】 表的【id】 应该是主键吧，【UserID】 上面有索引没？
【user】表的【UserID】 也是主键吧

#5

declare @Users table(UserID int ,LoginName nvarchar(8))
insert into @users 
select 1,'张三' union all
select 2 ,'李四' union all
select 3 ,'aaaa' union all
select 4 ,'asdf' union all
select 5 ,'asdf23' 

declare @Scores table (id int,cheji int,userid int,createdtime datetime)
insert into @Scores
select 1 ,50 ,1 ,'2012-01-07' union all
select 3 ,20 ,1 ,'2012-01-08' union all
select 4 ,50 ,1 ,'2012-01-09' union all
select 5 ,50 ,1 ,'2012-04-07' union all
select 6 ,50, 2 ,'2012-04-10' union all
select 7 ,50 ,2 ,'2012-04-22' union all
select 8 ,50 ,2 ,'2012-05-07' union all
select 9 ,50 ,2 ,'2012-07-07';

WITH S AS
(
	SELECT cheji,
			userid,
			createdtime,
			ROW_NUMBER() OVER(PARTITION BY userid ORDER BY createdtime) AS R
	FROM @Scores
)
,SC AS
(
	SELECT 
			userid,
			SUM(cheji) AS cheji
	FROM S
	WHERE R <= 3
	GROUP BY userid
)
SELECT U.LoginName,
		SC.cheji,
		ROW_NUMBER() OVER(ORDER BY SC.cheji) AS [RANK]
FROM @Users AS U
INNER JOIN SC
	ON U.UserID = SC.userid


LoginName cheji       RANK
--------- ----------- --------------------
张三        120         1
李四        150         2

(2 row(s) affected)

#6

结贴啦！多谢帮助各位！！

#7

引用 6 楼 zhangqiang1985 的回复:

结贴啦！多谢帮助各位！！

建议你在UserID 上面建个索引