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

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