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

时间：2015-10-28 18:49:21 阅读：708 评论：0 收藏：0 [点我收藏+]

最近做的一个财物管理系统中查询过期或逾期的存储过程，返回 “财物所属的案件名称”，“财物名称”，“财物编号”，“过期或逾期时间”（超期或逾期前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）。

最初版本：

 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

优化后的代码：

 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