--求某时间段内平均值的前十条的总和
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
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
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