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

转载于:https://www.cnblogs.com/pfs1314/archive/2010/12/21/1912598.html