T-SQL查询前十条数据相关操作

 

--求某时间段内平均值的前十条的总和
SELECT SUM(Result)
FROM (
           SELECT TOP 10 AVG(Result) AS result 
	   FROM B_Record_DomainNamePage
	   WHERE RecordTime BETWEEN '2010-12-16 13:11:27' AND '2010-12-22 13:11:27'
           GROUP BY Page
           ORDER BY Result DESC
        ) AS temp

 

 

--求除去前十条的某列的总和
SELECT SUM(result)
FROM (
	  SELECT *,ROW_NUMBER() OVER(ORDER BY Result DESC) AS rownum 
	  FROM B_Record_DomainNamePage 
	  WHERE RecordTime BETWEEN '2010-12-16 13:11:27' AND '2010-12-22 13:11:27'
         ) AS temp
WHERE rownum>10

--或者用存储过程实现
ALTER PROC [dbo].[SP_test]	
	as
	begin
	declare @sumall int
	declare @sumother int
	
	select @sumall = sum(result) from B_Record_DomainNamePage
	where RecordTime between '2010-12-16 13:11:27' and '2010-12-22 13:11:27')
	
	select @sumother =sum(result) from 
         (
            select top 10 result 
            from B_Record_DomainNamePage 
            where RecordTime between '2010-12-16 13:11:27' and '2010-12-22 13:11:27'
            ORDER by result desc
         ) as aa

	select @sumall-@sumother
	end 


--执行存储过程
DECLARE	@return_value int

EXEC	@return_value = [dbo].[SP_test]

SELECT	'Return Value' = @return_value

 

 

 1 CREATE PROC [dbo].[SP_test]    
 2     as
 3     begin
 4     declare @sumall int
 5     declare @sumother int
 6     
 7     select @sumall = sum(result) from B_Record_DomainNamePage
 8     where RecordTime between '2010-12-16 13:11:27' and '2010-12-22 13:11:27'
 9     
10     select @sumother =sum(result) from
11         (
12           select top 10 result 
13           from B_Record_DomainNamePage 
14           where RecordTime between '2010-12-16 13:11:27'            and'2010-12-22 13:11:27'
15           ORDER by result desc
16         ) as aa
17 
18     select @sumall-@sumother
19     end 

 

 

 

1 --求前十条后某列的总和
2 SELECT SUM(result) FROM (
3     SELECT *,ROW_NUMBER() OVER(ORDER BY Result DESC) AS rownum 
4     FROM B_Record_DomainNamePage 
5     WHERE domainname='www.jssdw.com' AND [type]='inpage' 
6           AND (RecordTime BETWEEN '2010-12-16 13:11:27.833' AND '2010-12-22 13:11:27.833')) AS temp
7 WHERE rownum>10