1 ALTER proc [dbo].[pr_get_time_limit]View Code
2
3 as
4
5 select
6 cast(a.anjianmingcheng as varchar(100)) as anjianmingcheng,
7 cast(c.caiwumingcheng as varchar(100)) as caiwumingcheng,
8 cast(c.caiwubianhao as varchar(100)) as caiwubianhao,
9 case
10 when datediff(day,c.caoqi,getdate())> 0 then '保存超期'+cast(abs(datediff(day,c.caoqi,getdate())) as varchar(50))+'天'
11 else cast(abs(datediff(day,c.caoqi,getdate())) as varchar(50))+'天后保存超期'
12 end as state,
13 c.caoqi as tagtime
14 from SACW_CaiWu c
15 left join SACW_CaiWuDiaoYongJiLu as d on c.Id=d.caiwuId
16 left join SACW_AnJian as a on c.anjianId=a.id
17 where c.jiazhijine>0 and getdate()>dateadd(day,-7,c.caoqi) or c.kucunshuliang>0 and getdate()>dateadd(day,-7,c.caoqi)
18
19 union all
20
21 select
22 cast(a.anjianmingcheng as varchar(100)) as anjianmingcheng,
23 cast(c.caiwumingcheng as varchar(100)) as caiwumingcheng,
24 cast(c.caiwubianhao as varchar(100)) as caiwubianhao,
25 case
26 when datediff(day,d.yujingTime,getdate())> 0 then '归还逾期'+cast(abs(datediff(day,d.yujingTime,getdate())) as varchar(50))+'天'
27 else cast(abs(datediff(day,d.yujingTime,getdate())) as varchar(50))+'天后归还逾期'
28 end as state,
29 d.yujingTime as tagtime
30 from SACW_CaiWuDiaoYongJiLu d
31 left join SACW_CaiWu c on c.Id=d.caiwuId
32 left join SACW_AnJian as a on c.anjianId=a.id
33 where d.jiazhijine>0 and getdate()> dateadd(day,-7,d.yujingTime) or d.caiwushuliang>0 and getdate()>dateadd(day,-7,d.yujingTime)
优化后的代码:
1 ALTER proc [dbo].[pr_get_time_limit]View Code
2
3 as
4
5 DECLARE @TempTime datetime
6 SET @TempTime = DATEADD(DAY,7,GETDATE())
7
8 SELECT
9 a.anjianmingcheng as [anjianmingcheng],
10 t.cm as [caiwumingcheng],
11 t.cb as [caiwubianhao],
12 t.tagtime as [tagtime],
13 case
14 when t.orderby = 0 then
15 case
16 when t.timeSpan > 0 then '调用逾期'+cast(t.timeSpan as varchar(50))+'天'
17 --when t.timeSpan = 0 then '即将逾期'
18 else cast(abs(t.timeSpan) as varchar(50))+'天后调用逾期'
19 end
20 else
21 case
22 when t.timeSpan > 0 then '保存超期'+cast(t.timeSpan as varchar(50))+'天'
23 --when t.timeSpan = 0 then '即将超期'
24 else cast(abs(t.timeSpan) as varchar(50))+'天后保存超期'
25 end
26 end as [state]
27
28 FROM (
29 select
30 c.anjianId as aid,
31 cast(c.caiwumingcheng as varchar(100)) as cm,
32 cast(c.caiwubianhao as varchar(100)) as cb,
33 datediff(day,d.yujingTime,getdate()) as timeSpan,
34 d.yujingTime as tagtime,
35 0 as orderby
36 from SACW_CaiWuDiaoYongJiLu d
37 left join SACW_CaiWu c on c.Id=d.caiwuId
38 where (d.jiazhijine>0 or d.caiwushuliang>0) and @TempTime > d.yujingTime
39
40 union all
41
42 select
43 c.anjianId as aid,
44 cast(c.caiwumingcheng as varchar(100)) as cm,
45 cast(c.caiwubianhao as varchar(100)) as cb,
46 datediff(day,c.caoqi,getdate()) as timeSpan,
47 c.caoqi as tagtime,
48 1 as orderby
49 from SACW_CaiWu c
50 where (c.jiazhijine>0 or c.kucunshuliang>0) and @TempTime > c.caoqi
51 ) as t
52 left join SACW_AnJian as a on t.aid=a.id
53 order by t.orderby,t.timeSpan
问题,最初版本中的代码中datediff函数计算值怎样用一个临时变量存起来供后面使用,而不是重新计算。不知道这样写在存储过程中会不会有性能损失(理论上的)。
网上其它相关解决方案: 关于union all中使用多个order by 子句引起的问题 http://blog.chinaunix.net/uid-20449297-id-1676810.html UNION ALL 子句不能包含ORDER BY的解决之道http://www.itpub.net/thread-1718235-1-1.html union all和order by一起使用出问题http://blog.csdn.net/tobeistdo/article/details/5613888 order by 和union all 如何共存 http://zhidao.baidu.com/link?url=GNpYMvin_xvKTQWrLMwHuoWg1yJtt0HODDLQYclj-tEuHJubI9UCJ4Uvm6qnP5eJL1sz8nKrKjM69OFHLGZcYq 来自为知笔记(Wiz)