现有规则:A,B,C,D
现有顺序:
A1 B1 空 空
A1 B1 空 D1
A1 B1 C1 空
期望顺序:
A1 B1 空 空
A1 B1 C1 空
A1 B1 空 D1
解答:
/*
名称:解决排序问题
作者:cosio
时间:2006-06-13
*/
declare @t table
(
A char(10),
B char(10),
C char(10),
D char(10)
)
insert into @t
select 'A1','B1','','' union all
select 'A1','B1','','D1' union all
select 'A1','B1','C1','' union all
select 'A1','B1','',''
select A,B,C,D from @t where C=''and D=''
union all
select A,B,C,D from @t where C<>''
union all
select A,B,C,D from @t where D<>''
/*
测试数据
*/
drop table times
create table times(id int,times datetime)
--插入数据
insert into times
select 1,'2006-08-20 18:00:01'
union all
select 2,'2006-08-20 18:00:01'
union all
select 2,'2006-08-20 18:01:01'
union all
select 3,'2006-08-20 18:00:01'
union all
select 3,'2006-08-20 18:01:01'
union all
select 4,'2006-08-20 18:00:01'
union all
select 4,'2006-08-20 18:01:01'
union all
select 1,'2006-08-20 13:00:01'
--解答
select * from times
select distinct * from (select id ,times=(select min(times) from times where times<=a.times and datediff(minute,times,a.times)<=1)
from times a) b