求教一种排序

[top3372]

默认排序
code type
01 B
01 C
02 A
02 B
02 C
02 D
排序以后
code type
01 B
02 B
01 C
02 C
02 A
02 D

请教排序以后的sql应该怎么写呢

[水族杰纶]

沒看出什麼規律

[hyde100]

好像没什么规律，特别是02

[fcuandy]

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
*/

[fcuandy]

规律是按 type中每个值，第一次出现的顺序，做为基准。

则 b c a d

[wer123q]

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

[等不到来世]

if object_id('[tb]') is not null drop table [tb]
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)

*/

[等不到来世]

5楼有点投机，恰好结果吻合。
如果表里增加一条记录：'03','a'
结果就不对了。

[brio8425]

3楼好解。。。。