求一个优化算法

叫我三三 2013-07-24 10:48:08



DECLARE @date date;

set @date='2013-01-08'

;WITH t1 ( ADate , CDate ) AS (

	SELECT '2013-01-01','2013-01-01' union all

	SELECT '2013-01-01','2013-01-05' union all

	

	SELECT '2013-01-02','2013-01-03' union all

	SELECT '2013-01-02','2013-01-08' union all

	

	SELECT '2013-01-03','2013-01-05' union all

	SELECT '2013-01-03','2013-01-04' union all

	

	SELECT '2013-01-05','2013-01-05' union all

	SELECT '2013-01-05','2013-01-06' union all

	SELECT '2013-01-05', NULL union all

	SELECT '2013-01-05','2013-01-05' union all

	

	SELECT '2013-01-06','2013-01-06' union all

	SELECT '2013-01-06','2013-01-08' union all

	

	SELECT '2013-01-07', NULL union all

	SELECT '2013-01-07', NULL union all

	SELECT '2013-01-07','2013-01-07' 

)

Select COUNT(*) from t1 where (CDate > @date and ADate <= @date) or (CDate is null and ADate <= @date)



-- ADate 申请日期，CDate 审核日期，null表示还未审核， 算出每天总未审核的数量，算法为当前未审核的数量加上以前未审核的数量之和。



--假如截止日期为2013-01-08 结果为：

/*

申请日期      未审核总数

2013-01-01    1

2013-01-02    3

2013-01-03    4

2013-01-04    3

2013-01-05    3

2013-01-06    3

2013-01-07    5

2013-01-08    3

*/

我现在是通过存储过程循环得出结果的，方法如上，可不可以直接用一个sql语句来完成？

...全文

178 16 打赏收藏转发到动态举报

写回复

用AI写文章

16 条回复

切换为时间正序

请发表友善的回复…

发表回复

开着拖拉机泡妞 2013-07-24

打赏
举报

SQL语句的做法你不喜欢，为何不换个思路拿程序做？

---涛声依旧--- 2013-07-24

打赏
举报

建议楼主换个思路做，将@t1 table (col date,n int)改为一个实际的固定表然后按日期分组汇总后插入数据再统计

發糞塗牆 2013-07-24

打赏
举报

我觉得你可以搞个时间表，直接关联，这样最快

叫我三三 2013-07-24

打赏
举报

引用 9 楼 kansousama 的回复:

[quote=引用 7 楼 rockyljt 的回复:] 楼主把表结构贴出来，最好有测试数据和想要的结果肯定不用while循环的

不是表是个视图


Declare @date date,@b date ,@e date,@n int,@s int;	
set @b='2013-01-01';set @e='2013-07-01'

Declare @t1 table (col date,n int)
set @date = @b
while @date <= @e
begin
	;WITH t1 as (Select ApplyDate,RepairDate,OrgID from View_Apply_Base )		
	Select @n = COUNT(*)+@s from t1 where OrgID='MD' and (RepairDate > @date and ApplyDate <= @date) or (RepairDate is null and ApplyDate <= @date) ;
	insert into @t1 (col,n) values (@date,@n);
	set @date = DATEADD(day,1,@date)
end
Select * from @t1

--耗时1分58秒

这是个是执行计划查询一天的时候

[/quote] @s 没用到，忽略掉吧

叫我三三 2013-07-24

打赏
举报

引用 8 楼 rockyljt 的回复:

--按ADate分组就行了啊 DECLARE @date date; set @date='2013-01-08' ;WITH t1 ( ADate , CDate ) AS ( SELECT '2013-01-01','2013-01-01' union all SELECT '2013-01-01','2013-01-05' union all SELECT '2013-01-02','2013-01-03' union all SELECT '2013-01-02','2013-01-08' union all SELECT '2013-01-03','2013-01-05' union all SELECT '2013-01-03','2013-01-04' union all SELECT '2013-01-05','2013-01-05' union all SELECT '2013-01-05','2013-01-06' union all SELECT '2013-01-05', NULL union all SELECT '2013-01-05','2013-01-05' union all SELECT '2013-01-06','2013-01-06' union all SELECT '2013-01-06','2013-01-08' union all SELECT '2013-01-07', NULL union all SELECT '2013-01-07', NULL union all SELECT '2013-01-07','2013-01-07' ) Select ADate,COUNT(*) from t1 where (CDate > @date and ADate <= @date) or (CDate is null and ADate <= @date) GROUP BY ROLLUP(ADate)

不行啊，亲，结果不对

叫我三三 2013-07-24

打赏
举报

引用 7 楼 rockyljt 的回复:

楼主把表结构贴出来，最好有测试数据和想要的结果
肯定不用while循环的

不是表是个视图



Declare @date date,@b date ,@e date,@n int,@s int;	

set @b='2013-01-01';set @e='2013-07-01'



Declare @t1 table (col date,n int)

set @date = @b

while @date <= @e

begin

	;WITH t1 as (Select ApplyDate,RepairDate,OrgID from View_Apply_Base )		

	Select @n = COUNT(*)+@s from t1 where OrgID='MD' and (RepairDate > @date and ApplyDate <= @date) or (RepairDate is null and ApplyDate <= @date) ;

	insert into @t1 (col,n) values (@date,@n);

	set @date = DATEADD(day,1,@date)

end

Select * from @t1



--耗时1分58秒

这是个是执行计划查询一天的时候

---涛声依旧--- 2013-07-24

打赏
举报

--按ADate分组就行了啊 DECLARE @date date; set @date='2013-01-08' ;WITH t1 ( ADate , CDate ) AS ( SELECT '2013-01-01','2013-01-01' union all SELECT '2013-01-01','2013-01-05' union all SELECT '2013-01-02','2013-01-03' union all SELECT '2013-01-02','2013-01-08' union all SELECT '2013-01-03','2013-01-05' union all SELECT '2013-01-03','2013-01-04' union all SELECT '2013-01-05','2013-01-05' union all SELECT '2013-01-05','2013-01-06' union all SELECT '2013-01-05', NULL union all SELECT '2013-01-05','2013-01-05' union all SELECT '2013-01-06','2013-01-06' union all SELECT '2013-01-06','2013-01-08' union all SELECT '2013-01-07', NULL union all SELECT '2013-01-07', NULL union all SELECT '2013-01-07','2013-01-07' ) Select ADate,COUNT(*) from t1 where (CDate > @date and ADate <= @date) or (CDate is null and ADate <= @date) GROUP BY ROLLUP(ADate)

---涛声依旧--- 2013-07-24

打赏
举报

引用 5 楼 kansousama 的回复:

我是取一个区间的，比如查询 2013-01-01 到2013-01-08 区间内的每天未审核的数目。用while循环，从2013-01-01 每天加1 为 @date赋值算出的结果加到表变量里面去。直到@date值大于 2013-01-08为止

楼主把表结构贴出来，最好有测试数据和想要的结果肯定不用while循环的

叫我三三 2013-07-24

打赏
举报

引用 3 楼 rockyljt 的回复:

t1是举例的吧，存储过程中应该是对应实际的表吧那样就没有必要用CTE了建立合理的索引可以提高查询效率

引用 4 楼 DBA_Huangzj 的回复:

CTE做这个挺好的啊，你还想怎么循环？

索引优化了的，短时间查询还可以，查询2010年到现在的数据，却要花几分钟

叫我三三 2013-07-24

打赏
举报

我是取一个区间的，比如查询 2013-01-01 到2013-01-08 区间内的每天未审核的数目。用while循环，从2013-01-01 每天加1 为 @date赋值算出的结果加到表变量里面去。直到@date值大于 2013-01-08为止

發糞塗牆 2013-07-24

打赏
举报

CTE做这个挺好的啊，你还想怎么循环？

---涛声依旧--- 2013-07-24

打赏
举报

t1是举例的吧，存储过程中应该是对应实际的表吧那样就没有必要用CTE了建立合理的索引可以提高查询效率

---涛声依旧--- 2013-07-24

打赏
举报

update 你的表 set CDate=0 where CDate is null 执行上述语句后，CDate再加上默认值 0

---涛声依旧--- 2013-07-24

打赏
举报

建议楼主将字段CDate的默认设置为0（即代表1900-01-01）因为为NULL后将逐行扫瞄，效率低

叫我三三 2013-07-24

打赏
举报


Declare @date date,@b date ,@e date,@n int;	
set @b='2010-07-01';set @e='2013-08-01'

Declare @base table (ADate date,CDate date,OrgID varchar(12))
Insert Into @base (ADate,CDate,OrgAID) Select ApplyDate,RepairDate,OrgID from View_Apply_Base where OrgID='MD' and (RepairDate >= @b or RepairDate is null) and ApplyDate <=@e

Declare @t1 table (col date,n int)
set @date = @b
while @date <= @e
begin		
	Select @n = COUNT(*) from @base where ((CDate > @date and ADate <= @date) or (CDate is null and ADate <= @date));
	insert into @t1 (col,n) values (@date,@n);
	set @date = DATEADD(day,1,@date)
end
Select * from @t1

已经解决了。先把数据范围缩小，然后只查一次视图。现在2年耗时6s，已经很满意了

叫我三三 2013-07-24