[SQL]排序的解决方案

现有规则：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