最近做的一个财物管理系统中查询过期或逾期的存储过程，返回 “财物所属的案件名称”，“财物名称”，“财物编号”，“过期或逾期时间”（超期或逾期前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）。 最初版本：
ALTER proc [dbo].[pr_get_time_limit] as select
cast(a.anjianmingcheng as varchar(100)) as anjianmingcheng,
cast(c.caiwumingcheng as varchar(100)) as caiwumingcheng,
cast(c.caiwubianhao as varchar(100)) as caiwubianhao,
case
when datediff(day,c.caoqi,getdate())> 0 then '保存超期'+cast(abs(datediff(day,c.caoqi,getdate())) as varchar(50))+'天'
else cast(abs(datediff(day,c.caoqi,getdate())) as varchar(50))+'天后保存超期'
end as state,
c.caoqi as tagtime
from SACW_CaiWu c
left join SACW_CaiWuDiaoYongJiLu as d on c.Id=d.caiwuId
left join SACW_AnJian as a on c.anjianId=a.id
where c.jiazhijine>0 and getdate()>dateadd(day,-7,c.caoqi) or c.kucunshuliang>0 and getdate()>dateadd(day,-7,c.caoqi) union all select
cast(a.anjianmingcheng as varchar(100)) as anjianmingcheng,
cast(c.caiwumingcheng as varchar(100)) as caiwumingcheng,
cast(c.caiwubianhao as varchar(100)) as caiwubianhao,
case
when datediff(day,d.yujingTime,getdate())> 0 then '归还逾期'+cast(abs(datediff(day,d.yujingTime,getdate())) as varchar(50))+'天'
else cast(abs(datediff(day,d.yujingTime,getdate())) as varchar(50))+'天后归还逾期'
end as state,
d.yujingTime as tagtime
from SACW_CaiWuDiaoYongJiLu d
left join SACW_CaiWu c on c.Id=d.caiwuId
left join SACW_AnJian as a on c.anjianId=a.id
where d.jiazhijine>0 and getdate()> dateadd(day,-7,d.yujingTime) or d.caiwushuliang>0 and getdate()>dateadd(day,-7,d.yujingTime)
优化后的代码：
ALTER proc [dbo].[pr_get_time_limit] as DECLARE @TempTime datetime
SET @TempTime = DATEADD(DAY,7,GETDATE()) SELECT
a.anjianmingcheng as [anjianmingcheng],
t.cm as [caiwumingcheng],
t.cb as [caiwubianhao],
t.tagtime as [tagtime],
case
when t.orderby = 0 then
case
when t.timeSpan > 0 then '调用逾期'+cast(t.timeSpan as varchar(50))+'天'
--when t.timeSpan = 0 then '即将逾期'
else cast(abs(t.timeSpan) as varchar(50))+'天后调用逾期'
end
else
case
when t.timeSpan > 0 then '保存超期'+cast(t.timeSpan as varchar(50))+'天'
--when t.timeSpan = 0 then '即将超期'
else cast(abs(t.timeSpan) as varchar(50))+'天后保存超期'
end
end as [state] FROM (
select
c.anjianId as aid,
cast(c.caiwumingcheng as varchar(100)) as cm,
cast(c.caiwubianhao as varchar(100)) as cb,
datediff(day,d.yujingTime,getdate()) as timeSpan,
d.yujingTime as tagtime,
0 as orderby
from SACW_CaiWuDiaoYongJiLu d
left join SACW_CaiWu c on c.Id=d.caiwuId
where (d.jiazhijine>0 or d.caiwushuliang>0) and @TempTime > d.yujingTime union all select
c.anjianId as aid,
cast(c.caiwumingcheng as varchar(100)) as cm,
cast(c.caiwubianhao as varchar(100)) as cb,
datediff(day,c.caoqi,getdate()) as timeSpan,
c.caoqi as tagtime,
1 as orderby
from SACW_CaiWu c
where (c.jiazhijine>0 or c.kucunshuliang>0) and @TempTime > c.caoqi
) as t
left join SACW_AnJian as a on t.aid=a.id
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)
