DECLARE @t TABLE(code VARCHAR(2), type VARCHAR(2))
INSERT @t SELECT '01', 'B'
UNION ALL SELECT '01', 'C'
UNION ALL SELECT '02', 'A'
UNION ALL SELECT '02', 'B'
UNION ALL SELECT '02', 'C'
UNION ALL SELECT '02', 'D'SELECT a.* FROM @t a
INNER JOIN
(
SELECT Type,MIN(idx) MI FROM (
SELECT Type,ROW_NUMBER() OVER(Order BY GETDATE()) idx FROM @t
) x
GROUP BY Type
) x
ON a.Type= x.Type
ORDER BY mi
/*
01 B
02 B
01 C
02 C
02 A
02 D
*/
INSERT @t SELECT '01', 'B'
UNION ALL SELECT '01', 'C'
UNION ALL SELECT '02', 'A'
UNION ALL SELECT '02', 'B'
UNION ALL SELECT '02', 'C'
UNION ALL SELECT '02', 'D'SELECT a.* FROM @t a
INNER JOIN
(
SELECT Type,MIN(idx) MI FROM (
SELECT Type,ROW_NUMBER() OVER(Order BY GETDATE()) idx FROM @t
) x
GROUP BY Type
) x
ON a.Type= x.Type
ORDER BY mi
/*
01 B
02 B
01 C
02 C
02 A
02 D
*/
declare @aa table(
code varchar(10),
type char(4)
)insert into @aa select '01','B'
union all select '01','C'
union all select '02','A'
union all select '02','B'
union all select '02','C'
union all select '02','D'
select * from @aa a order by (select count(1) from @aa where type=a.type )desc,type,code
create table [tb]([code] varchar(2),[type] varchar(1))
insert [tb]
select '01','b' union all
select '01','c' union all
select '02','a' union all
select '02','b' union all
select '02','c' union all
select '02','d'--select * from [tb]with cte
as
(
select code=code,type=type,zorder=code+type+code from tb a where not exists(select 1 from tb where code<a.code and type=a.type)
union all
select a.code,a.type,zorder=b.code+b.type+a.code from tb a join cte b on a.type=b.type and a.code>b.code
)
select code,type from cte order by zorder--测试结果:
/*
code type
---- ----
01 b
02 b
01 c
02 c
02 a
02 d(6 row(s) affected)*/