现有表A(ID varchar(8),DT datetime,P float),primary key(ID,DT) 表记录数有300万
现在求一个ID每一年(表中有记录的年份,如ID=50100100 表A中有记录的年份从1951-2008年)连续3天最大P值和(P为null不参与计算).
附A表一些示例记录
50100100 1951-1-1 2
50100100 1951-1-2 3.5
50100100 1951-1-3 null
.
.
.
50100100 1952-1-1 5
50100100 1952-1-2 7
50100100 1952-1-3 3.3
.
.
.
62012560 .............
高手看看有什么高效的算法!输出应该是年份 P和 开始日期
1951
1952
.
.
.
2008
现在求一个ID每一年(表中有记录的年份,如ID=50100100 表A中有记录的年份从1951-2008年)连续3天最大P值和(P为null不参与计算).
附A表一些示例记录
50100100 1951-1-1 2
50100100 1951-1-2 3.5
50100100 1951-1-3 null
.
.
.
50100100 1952-1-1 5
50100100 1952-1-2 7
50100100 1952-1-3 3.3
.
.
.
62012560 .............
高手看看有什么高效的算法!输出应该是年份 P和 开始日期
1951
1952
.
.
.
2008
select 1951 + number as dd into #1 from master.dbo.spt_values where type = 'p' and number <2008 - 1950
--------查询
select dd,max(p) from
(
select * from #1 a join 表A b on year(b.dt) = a.dd
where exists (select 1 from 表A where dt = b.dt - 1 )
and exists (select 1 from 表A where dt = b.dt - 2 )
and exists (select 1 from 表A where dt = b.dt + 1 )
and exists (select 1 from 表A where dt = b.dt + 2 )
) T
group by dd
写错了,更正一下----生产年份临时表
select 1951 + number as dd into #1 from master.dbo.spt_values where type = 'p' and number <2008 - 1950
--------查询
select dd,max(p) from
(
select * from #1 a join 表A b on year(b.dt) = a.dd
where exists (select 1 from 表A where dt = b.dt - 1 )
and exists (select 1 from 表A where dt = b.dt + 1 )
union all
select * from #1 a join 表A b on year(b.dt) = a.dd
where exists (select 1 from 表A where dt = b.dt - 1 )
and exists (select 1 from 表A where dt = b.dt - 2 )
union all
select * from #1 a join 表A b on year(b.dt) = a.dd
where exists (select 1 from 表A where dt = b.dt + 1 )
and exists (select 1 from 表A where dt = b.dt + 2 )
) T
group by dd
create table tbA(id varchar(8),dt datetime,p float)
insert into tbA select '50100100','1951-1-1',2 union all
select '50100100','1951-1-2',3.5 union all
select '50100100','1951-1-3',4.2 union all
select '50100100','1951-1-4',null union all
select '50100100','1951-1-6',7.2 union all
select '50100100','1951-1-7',null union all
select '50100100','1951-1-8',10.5 union all
select '50100100','1952-1-2',5 union all
select '50100100','1952-1-3',7 union all
select '50100100','1952-1-4',3.3 union all
select '50100100','1952-1-5',6.5 union all
select '50100100','1952-1-6',1.2
go
select datepart(yy,a.dt) as [year],max(a.p+b.p+c.p) as sumP
from tbA a inner join tbA b on a.id=b.id inner join tbA c on b.id=c.id
where datediff(dd,a.dt,b.dt)=1 and datediff(dd,b.dt,c.dt)=1
and datepart(yy,a.dt)=datepart(yy,b.dt) and datepart(yy,b.dt)=datepart(yy,c.dt)
and not a.p is null and not b.p is null and not c.p is null
group by datepart(yy,a.dt)
go
drop table tbA
/*
year sumP
----------- ----------------------
1951 9.7
1952 16.8
*/
而且默认排序肯定按日期了, 所以再用只读向前的游标统计是比较高效的游标内的算法 可以采用类似 冒泡排序的方法, 但比冒泡简单, 因为只一次数据遍历就行了, 如
变量1记录从1累加三天的数据, 之后清零后 再累加三天
变量2记录从2累加三天的数据, 之后清零后 再累加三天
变量3记录从3累加三天的数据, 之后清零后 再累加三天
变量11是 变量1的最大值, 用于与变量1比较, 如果小于变量1, 就取变量1的值
变量22是 变量2的最大值, 用于与变量2比较, 如果小于变量2, 就取变量2的值
变量33是 变量3的最大值, 用于与变量3比较, 如果小于变量3, 就取变量3的值
遍历到当年年末 变量11, 变量22, 变量33比较再取最大值, 就知道结果了
当然 三个变更要动态维护只有三条记录的表变量, 用于记录是哪三条记录ID, DT
最后 ID, DT都知道了, 数据自然也就出来了
按此方法一个循环就出来了, 变量计算耗时可以忽略不计, 真正时耗在于检索, 恰恰相当于查询一个有2万条记录的表所需时间
create table tbA(id varchar(8),dt datetime,p float)
insert into tbA select '50100100','1951-1-1',2 union all
select '50100100','1951-1-2',3.5 union all
select '50100100','1951-1-3',4.2 union all
select '50100100','1951-1-4',null union all
select '50100100','1951-1-6',7.2 union all
select '50100100','1951-1-7',null union all
select '50100100','1951-1-8',10.5 union all
select '50100100','1951-1-15',10.5 union all
select '50100100','1951-1-16',10 union all
select '50100100','1951-1-17',8.5 union all
select '50100100','1952-1-2',5 union all
select '50100100','1952-1-3',7 union all
select '50100100','1952-1-4',3.3 union all
select '50100100','1952-1-5',6.5 union all
select '50100100','1952-1-6',1.2 union all
select '50100101','1952-1-3',6.5 union all
select '50100101','1952-1-4',3.8 union all
select '50100101','1952-1-5',16 union all
select '50100101','1952-1-6',7
go
select a.id,a.dt,a.p+b.p+c.p as sumP into #
from tbA a inner join tbA b on a.id=b.id inner join tbA c on b.id=c.id
where datediff(dd,a.dt,b.dt)=1 and datediff(dd,b.dt,c.dt)=1
and datepart(yy,a.dt)=datepart(yy,b.dt) and datepart(yy,b.dt)=datepart(yy,c.dt)
and not a.p is null and not b.p is null and not c.p is null
select * from # a where not exists(select 1 from # where id=a.id and datepart(yy,dt)=datepart(yy,a.dt) and sump>a.sump)
go
drop table tbA,#
/*
create table tbA(id varchar(8),dt datetime,p float)
insert into tbA select '50100100','1951-1-1',2 union all
select '50100100','1951-1-2',3.5 union all
select '50100100','1951-1-3',4.2 union all
select '50100100','1951-1-4',null union all
select '50100100','1951-1-6',7.2 union all
select '50100100','1951-1-7',null union all
select '50100100','1951-1-8',10.5 union all
select '50100100','1951-1-15',10.5 union all
select '50100100','1951-1-16',10 union all
select '50100100','1951-1-17',8.5 union all
select '50100100','1952-1-2',5 union all
select '50100100','1952-1-3',7 union all
select '50100100','1952-1-4',3.3 union all
select '50100100','1952-1-5',6.5 union all
select '50100100','1952-1-6',1.2 union all
select '50100101','1952-1-3',6.5 union all
select '50100101','1952-1-4',3.8 union all
select '50100101','1952-1-5',16 union all
select '50100101','1952-1-6',7
go
select a.id,a.dt,a.p+b.p+c.p as sumP into #
from tbA a inner join tbA b on a.id=b.id inner join tbA c on b.id=c.id
where datediff(dd,a.dt,b.dt)=1 and datediff(dd,b.dt,c.dt)=1
and datepart(yy,a.dt)=datepart(yy,b.dt) and datepart(yy,b.dt)=datepart(yy,c.dt)
and not a.p is null and not b.p is null and not c.p is null
select * from # a where not exists(select 1 from # where id=a.id and datepart(yy,dt)=datepart(yy,a.dt) and sump>a.sump)
go
drop table tbA,#
/*
id dt sumP
-------- ----------------------- ----------------------
50100100 1951-01-15 00:00:00.000 29
50100100 1952-01-03 00:00:00.000 16.8
50100101 1952-01-04 00:00:00.000 26.8
*/
-- Author: liangCK 小梁
---------------------------------
--> 生成测试数据: #T
IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T
CREATE TABLE #T (ID VARCHAR(8),DT DATETIME,P DECIMAL(18,2),PRIMARY KEY(ID,DT))
INSERT INTO #T
SELECT '50100100','1951-1-1',2 UNION ALL
SELECT '50100100','1951-1-2',3.5 UNION ALL
SELECT '50100100','1951-1-3',null UNION ALL
SELECT '50100100','1951-1-4',2 UNION ALL
SELECT '50100100','1951-1-5',5 UNION ALL
SELECT '50100100','1951-1-6',5 UNION ALL
SELECT '50100100','1951-1-8',5 UNION ALL
SELECT '50100100','1951-1-9',2 UNION ALL
SELECT '50100100','1951-1-10',5 UNION ALL
SELECT '50100100','1952-1-1',5 UNION ALL
SELECT '50100100','1952-1-2',7 UNION ALL
SELECT '50100100','1952-1-3',3.3--SQL查询如下:DECLARE @min_dt DATETIME,@max_dt DATETIME;
SELECT
@min_dt=MIN(DT),@max_dt=MAX(dt)
FROM #T;CREATE TABLE #temp_dt(DT DATETIME);
INSERT #temp_dt(DT)
SELECT
DATEADD(year,number,DATEADD(year,0,@min_dt))
FROM master.dbo.spt_values
WHERE type='p' AND number < DATEDIFF(year,@min_dt,@max_dt)+1;
SELECT
YEAR(A.DT) AS 年份,
B.DT AS [开始时间],
B.total AS P和
FROM #temp_dt AS A
CROSS APPLY (
SELECT TOP(1) *
FROM (
SELECT
DT,total=(SELECT SUM(ISNULL(P,0)) FROM #T
WHERE ID=D.ID AND DT BETWEEN D.DT AND DATEADD(day,2,D.DT))
FROM #T AS D
WHERE DT >= A.DT AND DT < DATEADD(year,1,A.DT)
) AS T
ORDER BY total DESC
) AS B
DROP TABLE #T,#temp_dt;/*
年份 开始时间 P和
----------- ----------------------- ---------------------------------------
1951 1951-01-04 00:00:00.000 12.00
1952 1952-01-01 00:00:00.000 15.30(2 行受影响)
*/
-- Author: liangCK 小梁
---------------------------------
--> 生成测试数据: #T
IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T
CREATE TABLE #T (ID VARCHAR(8),DT DATETIME,P DECIMAL(18,2),PRIMARY KEY(ID,DT))
INSERT INTO #T
SELECT '50100100','1951-1-1',2 UNION ALL
SELECT '50100100','1951-1-2',3.5 UNION ALL
SELECT '50100100','1951-1-3',null UNION ALL
SELECT '50100100','1951-1-4',2 UNION ALL
SELECT '50100100','1951-1-5',5 UNION ALL
SELECT '50100100','1951-1-6',5 UNION ALL
SELECT '50100100','1951-1-8',5 UNION ALL
SELECT '50100100','1951-1-9',2 UNION ALL
SELECT '50100100','1951-1-10',5 UNION ALL
SELECT '50100100','1952-1-1',5 UNION ALL
SELECT '50100100','1952-1-2',7 UNION ALL
SELECT '50100100','1952-1-3',3.3--SQL查询如下:DECLARE @min_dt DATETIME,@max_dt DATETIME;
SELECT
@min_dt=MIN(DT),@max_dt=MAX(dt)
FROM #T;CREATE TABLE #temp_dt(DT DATETIME);
INSERT #temp_dt(DT)
SELECT
DATEADD(year,number,DATEADD(year,0,@min_dt))
FROM master.dbo.spt_values
WHERE type='p' AND number < DATEDIFF(year,@min_dt,@max_dt)+1;
SELECT
YEAR(A.DT) AS 年份,
B.DT AS [开始时间],
B.total AS P和
FROM #temp_dt AS A
CROSS APPLY (
SELECT TOP(1) WITH TIES *
FROM (
SELECT
DT,total=(SELECT SUM(ISNULL(P,0)) FROM #T
WHERE ID=D.ID AND DT BETWEEN D.DT AND DATEADD(day,2,D.DT))
FROM #T AS D
WHERE DT >= A.DT AND DT < DATEADD(year,1,A.DT)
) AS T
ORDER BY total DESC
) AS B
DROP TABLE #T,#temp_dt;/*
年份 开始时间 P和
----------- ----------------------- ---------------------------------------
1951 1951-01-04 00:00:00.000 12.00
1951 1951-01-08 00:00:00.000 12.00
1952 1952-01-01 00:00:00.000 15.30(3 行受影响)
*/
--创建一个函数
create function sumP(@date varchar(10),@diffdate int)
returns float
as
begin
declare @sums float
select @sums=sum(p) from testtable where filedate between @date and dateadd(day,@diffdate,@date)
return @sums
end
--调用方式
select dbo.sump(filedate,2) from testtable
select dbo.sump(filedate,-2) from testtable
select * from
tbA A left join tbA B On A.id = B.id And Year(A.dt) = Year(B.dt) And Day(B.Dt) = Day(A.Dt) + 1
left join tbA C On A.id = C.id And Year(A.dt) = Year(C.dt) And Day(C.dt) = Day(A.Dt) + 2
结果会形如
id dt p id dt p id dt p
-------- ----------------------- --- -------- ----------------------- ---- -------- ----------------------- ---
50100100 1951-01-01 00:00:00.000 2 50100100 1951-01-02 00:00:00.000 3.5 50100100 1951-01-03 00:00:00.000 4.2
50100100 1951-01-02 00:00:00.000 3.5 50100100 1951-01-03 00:00:00.000 4.2 50100100 1951-01-04 00:00:00.000 NULL
然后把三个 p 加起来找最大值。呵呵,这两天事比较多,没法做完整的了,见谅!那位兄弟有兴趣弄个完整的。上面的 SQL 日期处理不正确,没有考虑跨月的情形。
insert into test select '50100100','2009-1-1',1
union all select '50100100','2009-1-2',3
--union all select '50100100','2009-1-3',2
union all select '50100100','2009-1-4',5
union all select '50100100','2009-1-5',4
--union all select '50100100','2009-1-6',1
union all select '50100100','2009-1-7',2
union all select '50100100','2009-1-8',5
--union all select '50100100','2009-12-30',1
union all select '50100100','2009-12-31',200
union all select '50100100','2010-1-1',100--要求必须有三天连续记录
select y,p,(select top 1 dt from test where id='50100100' and (select count(id) from test as A5 where id='50100100' and dt between dateadd(day,1,test.dt) and dateadd(day,2,test.dt))=2 and (month(dt)<>12 or day(dt)>30) and (select sum(p) from test as A6 where id='50100100' and dt between test.dt and dateadd(day,2,test.dt) and p is not null)=A4.p) as dt from
(select y,MAX(p) as p from
(select year(dt) as y,(select sum(p) from test where id='50100100' and dt between A2.dt and dateadd(day,2,A2.dt) and p is not null) as p from
(select dt from test where id='50100100' and (select count(id) from test as A1 where id='50100100' and dt between dateadd(day,1,test.dt) and dateadd(day,2,test.dt))=2 and (month(dt)<>12 or day(dt)>30)) as A2
) as A3 group by y
)as A4--不要求必须有三天连续记录
select y,p,(select top 1 dt from test where id='50100100' and (select sum(p) from test as A6 where id='50100100' and dt between test.dt and dateadd(day,2,test.dt) and p is not null and year(dt)=year(test.dt))=A4.p) as dt from
(select y,MAX(p) as p from
(select year(dt) as y,(select sum(p) from test where id='50100100' and dt between A2.dt and dateadd(day,2,A2.dt) and p is not null and year(dt)=year(A2.dt)) as p from test as A2) as A3 group by y
)as A4
select y,p,(select top 1 dt from test where id='50100100' and (select sum(p) from test as A6 where id='50100100' and dt between test.dt and dateadd(day,2,test.dt) and p is not null and year(dt)=year(test.dt))=A4.p) as dt from
(select y,MAX(p) as p from
(select year(dt) as y,(select sum(p) from test where id='50100100' and dt between A2.dt and dateadd(day,2,A2.dt) and p is not null and year(dt)=year(A2.dt)) as p from test as A2 where id='50100100') as A3 group by y
)as A4--id很多的情况下可用
select y,p,(select top 1 dt from test where id='50100100' and (select sum(p) from test as A6 where id='50100100' and dt between test.dt and dateadd(day,2,test.dt) and p is not null and year(dt)=year(test.dt))=A4.p) as dt from
(select y,MAX(p) as p from
(select year(dt) as y,(select sum(p) from test where id='50100100' and dt between A2.dt and dateadd(day,2,A2.dt) and p is not null and year(dt)=year(A2.dt)) as p from
(select dt from test where id='50100100') as A2
) as A3 group by y
)as A4
我说出我的做法,,肯定符合你想要的结果,看下我造的数据(表A)是不是你想要的数据..
ID DT P
50100100 5/1/1951 2.3
50100100 6/1/1951 2.5
50100100 7/1/1951 NULL
50100100 1/2/1951 3.5
50100100 2/2/1951 2.5
50100100 3/2/1951 2.8
50100100 4/3/2008 2.6
50100100 5/3/2008 5.6
50100100 6/3/2008 6.2
50100101 4/6/1952 NULL
50100101 5/6/1952 5.6
50100101 6/6/1952 3.5DECLARE @ID VARCHAR(8),
@DT DATETIME,
@P FLOAT
DECLARE @TIMES INT,@PSUM FLOAT,@COUNT INT,@DT_FRIST VARCHAR(12)
--DECLARE @DT_NEXT VARCHAR(12)
SET @PSUM=0
DECLARE @SQL VARCHAR(999)
SET @SQL=''
SET @COUNT=(SELECT COUNT(ID) FROM A)
CREATE TABLE #T (ID VARCHAR(8),DT DATETIME,P FLOAT,PRIMARY KEY(ID,DT))
DECLARE SUM_CUSOR CURSOR
FOR SELECT ID,CONVERT(VARCHAR,DT,111) AS DT,CONVERT(VARCHAR,ISNULL(P,0)) AS P FROM A ORDER BY ID,DT
FOR READ ONLY
SET @TIMES=1
OPEN SUM_CUSOR
WHILE @COUNT <> 0
BEGIN
FETCH NEXT FROM SUM_CUSOR INTO @ID,@DT,@P
SET @PSUM=@PSUM+@P
IF(@TIMES=1)
BEGIN
SET @DT_FRIST=CONVERT(VARCHAR,@DT,111)
END
SET @TIMES=@TIMES+1
IF(@TIMES=4)
BEGIN
SET @SQL=@SQL+' INSERT INTO #T VALUES ('''+CONVERT(VARCHAR,@ID)+''','''+@DT_FRIST+''','+CONVERT(VARCHAR,@PSUM)+')'
SET @TIMES=1
SET @PSUM=0
END
SET @COUNT=@COUNT-1
END
CLOSE SUM_CUSOR
DEALLOCATE SUM_CUSOR
--PRINT @SQL
EXEC (@SQL)
SELECT T1.ID,CONVERT(VARCHAR,T2.DT,111)AS DT,CONVERT(VARCHAR,T1.P) AS P FROM (SELECT ID,MAX(P)AS P FROM #T GROUP BY ID,YEAR(DT)) AS T1
LEFT OUTER JOIN #T AS T2 ON T2.ID=T1.ID AND T2.P=T1.P
ORDER BY T1.ID
DROP TABLE #T
这下面是输出结果,,
50100100 1954/02/01 8.8
50100100 2008/03/04 14.4
50100101 1952/06/04 9.1
你看看,应该是你想要的...给分吧:)
有什么问题直接联系我MSN:[email protected],随时在线
as
/*
@stcd 编号
@year 分析的年份
@days 连续天数
*/
begin
select DT,P into #s from AHSW2005.dbo.HY_DP_C where stcd=@stcd and year(DT)=@year and P is not null
declare @minTime datetime
declare @maxTime datetime
declare @time datetime
declare @time1 datetime
declare @maxP float
set @maxP=0
declare @P float
set @P=0
select @minTime=min(DT),@maxTime=max(DT) from #s
set @time=@minTime
while(dateadd(dd,@days-1,@time)<=@maxTime)
begin
select @p=sum(P) from #s where DT between @time and dateadd(dd,@days-1,@time)
if(@p>@maxP)
begin
set @maxp=@p
set @time1=@time
end
set @time=dateadd(dd,1,@time)
end
drop table #s
select @maxP,@time1
end
下面以编号为作为50104200测试declare @time1 datetime
set @time1=getdate()
declare @minyear int
declare @maxyear int
declare @year int
declare @id varchar(8)
declare @days int
set @days=3
set @id='50104200'
select @minYear=min(YR),@maxYear=max(YR) from (select distinct year(DT) as YR from AHSW2005.DBO.HY_DP_C where stcd='50104200') a
set @year=@minYear
create table #result(maxp float,dates datetime)
while(@year<=@maxYear)
begin
insert #result
exec HQ_GetMax_P @id,@year,@days
set @year=@year+1
end
select * from #result where maxp>0 and dates is not null
drop table #result
select datediff(ms,@time1,getdate())
结果
265.80000000000001 1950-07-12 00:00:00.000
121.40000000000001 1951-07-18 00:00:00.000
114.19999999999999 1952-08-28 00:00:00.000
93.5 1953-08-03 00:00:00.000
223.80000000000001 1954-07-05 00:00:00.000
112.19999999999999 1955-08-13 00:00:00.000
271.19999999999999 1956-06-06 00:00:00.000
109.70000000000002 1957-06-30 00:00:00.000
86.299999999999997 1958-06-26 00:00:00.000
77.200000000000003 1959-05-02 00:00:00.000
89.5 1960-11-21 00:00:00.000
138.09999999999999 1961-07-28 00:00:00.000
211.5 1962-08-06 00:00:00.000
91.799999999999997 1963-07-30 00:00:00.000
130.5 1964-08-18 00:00:00.000
179.89999999999998 1965-06-30 00:00:00.000
146.90000000000003 1966-03-02 00:00:00.000
126.3 1967-07-02 00:00:00.000
173.0 1968-06-28 00:00:00.000
131.09999999999999 1969-09-27 00:00:00.000
166.90000000000001 1970-05-27 00:00:00.000
152.0 1971-06-09 00:00:00.000
168.30000000000001 1972-07-01 00:00:00.000
77.900000000000006 1973-07-12 00:00:00.000
173.69999999999999 1974-08-10 00:00:00.000
125.0 1975-08-15 00:00:00.000
81.599999999999994 1976-05-15 00:00:00.000
120.19999999999999 1977-07-17 00:00:00.000
53.200000000000003 1978-08-09 00:00:00.000
135.19999999999999 1979-07-13 00:00:00.000
171.29999999999998 1980-06-23 00:00:00.000
140.0 1981-06-25 00:00:00.000
99.299999999999997 1982-07-20 00:00:00.000
116.0 1983-06-29 00:00:00.000
134.40000000000001 1984-07-24 00:00:00.000
102.5 1985-07-14 00:00:00.000
127.40000000000001 1986-07-15 00:00:00.000
152.29999999999998 1987-08-27 00:00:00.000
95.0 1988-09-08 00:00:00.000
191.0 1989-08-22 00:00:00.000
156.09999999999999 1990-07-09 00:00:00.000
271.79999999999995 1991-06-12 00:00:00.000
127.09999999999999 1992-10-01 00:00:00.000
85.400000000000006 1993-08-11 00:00:00.000
97.099999999999994 1994-06-06 00:00:00.000
151.70000000000002 1995-05-10 00:00:00.000
114.8 1996-07-02 00:00:00.000
134.90000000000001 1997-07-14 00:00:00.000
133.80000000000001 1998-06-29 00:00:00.000
156.19999999999999 1999-07-05 00:00:00.000
183.19999999999999 2000-06-01 00:00:00.000
101.60000000000001 2001-08-01 00:00:00.000
161.19999999999999 2002-07-22 00:00:00.000
164.79999999999998 2003-07-08 00:00:00.000
68.800000000000011 2004-07-10 00:00:00.000
237.40000000000001 2005-07-06 00:00:00.000
159.5 2006-06-29 00:00:00.000
187.5 2007-07-06 00:00:00.000耗时1733毫秒
Select Top 1 * from (select S1.*, IsNUll(S1.OrderQty, 0) + IsNull(S2.OrderQty, 0) + IsNull(S3.OrderQty, 0) as QtyTotal From Sales.SalesOrderDetail S1
left join Sales.SalesOrderDetail S2 On S1.SalesOrderID = S2.SalesOrderID And S1.SalesOrderDetailID + 1 = S2.SalesOrderDetailID
left join Sales.SalesOrderDetail S3 On S1.SalesOrderID = S3.SalesOrderID And S1.SalesOrderDetailID + 2 = S3.SalesOrderDetailID) New
Order By QtyTotal Desc37 楼的方式如下:
函数:
Create Function TotalThree(@Id int)
Returns int
Begin
declare @total int
Select @total = Sum(OrderQty) From Sales.SalesOrderDetail Where SalesOrderDetailID between @Id and @Id + 2
return @total
End
SQL:
Select Top 1 * from (Select SalesOrderID, SalesOrderDetailID, dbo.TotalThree(SalesOrderDetailID) QtyTotal From Sales.SalesOrderDetail) New
Order By QtyTotal Desc用外连接 1 秒之内(绝对不到 1 秒,因为查询分析器经常显示的为 0)能提取到结果,而后者基本上是一个不可能完成的任务,在查询分析器中运行了 10 分钟了,还没有结束,只能手动结束,如果把资料笔数限定 1000 笔,也需要 13 秒才能够提出结果。注意,这是查询分析器下面显示出的时候,实际使用的时候应该比这个少一些。资料的总笔数是 121317 笔。
我机器比较破哈,CD420 的 CPU,2G 的内存。
我上面的 SQL 应该没写错吧?
INSERT INTO @TEMP_A
SELECT ID,DT,P,(DENSE_RANKE()OVER(PARTITION BY ID,YEAR(DT) ORDER BY DT)) AS IDX
FROM ASELECT
YEAR(A.YEAR_OF_DT) AS "年份",MAX_P AS "P和",A1.DT AS "开始日期"
FROM
(SELECT
A1.DT
,(MAX(A1.P + A2.P + A3.P)OVER(PARTITION BY A1.ID,YEAR(DT))) AS MAX_P
,(A1.P + A2.P + A3.P) AS SUM_P
,(DENSE_RANKE()OVER(PARTITION BY A1.ID,YEAR(A1.DT),(A1.P + A2.P + A3.P) ORDER BY A1.DT) AS IDX
FROM
@TEMP_A AS A1
INNER JOIN @TEMP_A AS A2
ON A1.ID = A2.ID
AND A1.DT = A2.DT
AND A1.IDX = (A2.IDX + 1)
INNER JOIN @TEMP_A AS A3
ON A1.ID = A3.ID
AND A1.DT = A3.DT
AND A1.IDX = (A3.IDX + 2)
)AS A
WHERE
A.MAX_P = A.SUM_P
AND A.IDX = 1
INSERT INTO @TEMP_A
SELECT ID,DT,P,(DENSE_RANKE()OVER(PARTITION BY ID,YEAR(DT) ORDER BY DT)) AS IDX
FROM A SELECT
YEAR(A.DT) AS "年份",A.MAX_P AS "P和",A.DT AS "开始日期"
FROM
(SELECT
A1.DT
,(MAX(A1.P + A2.P + A3.P)OVER(PARTITION BY A1.ID,YEAR(DT))) AS MAX_P
,(A1.P + A2.P + A3.P) AS SUM_P
,(DENSE_RANKE()OVER(PARTITION BY A1.ID,YEAR(A1.DT),(A1.P + A2.P + A3.P) ORDER BY A1.DT) AS IDX
FROM
@TEMP_A AS A1
INNER JOIN @TEMP_A AS A2
ON A1.ID = A2.ID
AND A1.DT = A2.DT
AND A1.IDX = (A2.IDX + 1)
INNER JOIN @TEMP_A AS A3
ON A1.ID = A3.ID
AND A1.DT = A3.DT
AND A1.IDX = (A3.IDX + 2)
)AS A
WHERE
A.MAX_P = A.SUM_P
AND A.IDX = 1