create table tab (id varchar(20),billentry varchar(20),audittime datetime)
insert tab select
'jk001','分录1','2009-01-01' union all select
'jk001','分录2','2009-01-02' union all select
'jk001','分录3',null union all select
'jk002','分录1','2009-02-01' union all select
'jk001','分录4','2009-01-03' union all select
'jk002','分录2',null union all select
'jk001','分录5',null union all select
'jk002','分录3','2009-02-03' union all select
'jk002','分录4',null union all select
'jk002','分录5','2009-02-04' SELECT * FROM TAB 我想实现的是给个时间段,如果表中audittime时间不在这个区间,就显示null,显示的记录还要按audittime排序、
我只会写这个就是不知道时间怎么加
select * from tab order by case when audittime is null then getdate()+1 else audittime end asc
insert tab select
'jk001','分录1','2009-01-01' union all select
'jk001','分录2','2009-01-02' union all select
'jk001','分录3',null union all select
'jk002','分录1','2009-02-01' union all select
'jk001','分录4','2009-01-03' union all select
'jk002','分录2',null union all select
'jk001','分录5',null union all select
'jk002','分录3','2009-02-03' union all select
'jk002','分录4',null union all select
'jk002','分录5','2009-02-04' SELECT * FROM TAB 我想实现的是给个时间段,如果表中audittime时间不在这个区间,就显示null,显示的记录还要按audittime排序、
我只会写这个就是不知道时间怎么加
select * from tab order by case when audittime is null then getdate()+1 else audittime end asc
case when audittime BETWEEN...AND...THEN ..ELSE ,,END AS from
tab
order by case when audittime is null then getdate()+1 else audittime end asc??
then getdate()+1 else audittime end asc
insert tab select
'jk001 ', '分录1 ', '2009-01-01 ' union all select
'jk001 ', '分录2 ', '2009-01-02 ' union all select
'jk001 ', '分录3 ',null union all select
'jk002 ', '分录1 ', '2009-02-01 ' union all select
'jk001 ', '分录4 ', '2009-01-03 ' union all select
'jk002 ', '分录2 ',null union all select
'jk001 ', '分录5 ',null union all select
'jk002 ', '分录3 ', '2009-02-03 ' union all select
'jk002 ', '分录4 ',null union all select
'jk002 ', '分录5 ', '2009-02-04' select
ID,billentry ,
case when audittime BETWEEN '2009-01-01' AND '2009-01-03' THEN audittime ELSE null END AS audittime
from
tab
order by case when audittime is null then getdate()+1 else audittime end ascdrop table tab/*ID billentry audittime
-------------------- -------------------- -----------------------
jk001 分录1 2009-01-01 00:00:00.000
jk001 分录2 2009-01-02 00:00:00.000
jk001 分录4 2009-01-03 00:00:00.000
jk002 分录1 NULL
jk002 分录3 NULL
jk002 分录5 NULL
jk002 分录4 NULL
jk002 分录2 NULL
jk001 分录5 NULL
jk001 分录3 NULL(10 行受影响)
*/[/code]
from tab order by case when audittime is null then getdate()+1 else audittime end asc
a.id,
a.billentry,
b.audittime
from
tab a
left join
(select * from tab where audittime between '2009-02-01' and '2009-02-05') b
on
a.id=b.id and a.audittime=b.audittime
order by
case when b.audittime is null then 2 else 1 end,
b.audittime,
a.id,
a.billentry drop table tab/**
id billentry audittime
-------------------- -------------------- ------------------------------------------------------
jk002 分录1 2009-02-01 00:00:00.000
jk002 分录3 2009-02-03 00:00:00.000
jk002 分录5 2009-02-04 00:00:00.000
jk001 分录1 NULL
jk001 分录2 NULL
jk001 分录3 NULL
jk001 分录4 NULL
jk001 分录5 NULL
jk002 分录2 NULL
jk002 分录4 NULL(所影响的行数为 10 行)
**/