SQL 语句中union all和order by同时使用

时间:2021-03-05 15:47:18
        最近做的一个财物管理系统中查询过期或逾期的存储过程,返回 “财物所属的案件名称”,“财物名称”,“财物编号”,“过期或逾期时间”(超期或逾期前7天开始预警)。遇到“union all 内不能使用 order by”的问题,百度了很久,都没有一个找到一个好的解决方案。最终还是自己实现了,记录一下。 为什么用存储过程,非得用union all 而不在程序中拼接表数据?        这个存储过程不是供我们Web程序使用的,它是提供给运行在服务器上的C/S程序调用(用来投放到机房外的LED显示屏)。因为这个C/S程序不是我们写的,别人要求用存储过程并一次性返回超期和预期的数据。当时我正在客户那里安装这个系统,这个功能是客户临时加的,所以就匆忙赶了一个,当时没排序,回来后整理时才遇到这个问题。 涉及到的数据库表(字段):  案件表【AnJian:(Id,案件名称【anjianmingcheng】) 财物表【CaiWu :(Id,所属案件【Id]anjianId】,财物编号【caiwubianhao】,财物名称【caiwumingcheng】,保存指定的保存结束时间【caoqi】 财物调用记录表【CaiWuDiaoYongJiLu :(Id,被调用财物Id【caiwuId】,调用时指定的归还时间【yujingTime】) 注 保存结束时间和调用归还时间不能为空,如果是长期会在程序中指定一个超大的时间值(9999/12/30)。   最初版本: SQL 语句中union all和order by同时使用SQL 语句中union all和order by同时使用
 1 ALTER proc [dbo].[pr_get_time_limit]
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)
View Code

 

优化后的代码: SQL 语句中union all和order by同时使用SQL 语句中union all和order by同时使用
 1 ALTER proc [dbo].[pr_get_time_limit]
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
View Code

 

问题,最初版本中的代码中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)