求教一条SQL语句的写法
有这样的一个表:
Time Sign SN
2013-01-01 AAA 1
2013-01-04 CCC 2
2013-01-06 BBB 3
2013-01-05 BBB 1
2013-01-02 AAA 2
2013-01-05 BBB 2
2013-01-06 BBB 4
2013-01-03 AAA 3
2013-01-04 AAA 4
2013-01-03 CCC 1
排序后要求是这样的:
Sign Time SN
AAA 2013-01-01 1
AAA 2013-01-02 2
AAA 2013-01-03 3
AAA 2013-01-04 4
CCC 2013-01-03 1
CCC 2013-01-04 2
BBB 2013-01-05 1
BBB 2013-01-05 2
BBB 2013-01-06 3
BBB 2013-01-06 4
对了,你这个有什么排序的要求不,不是很明白
首先:先确定Sign的顺序,依据是每个Sign的SN为1的这条记录对应的Time。
之后:根据SN排序。
Sign:AAA的SN等于1的这条记录对应的Time为2013-01-01;
Sign:BBB的SN等于1的这条记录对应的Time为2013-01-05;
Sign:CCC的SN等于1的这条记录对应的Time为2013-01-03;
因此,先确定Sign的顺序是AAA,CCC,BBB。
之后就根据SN排序就行了。
是这样吗:
;with t(Time, Sign, SN)
as
(
select '2013-01-01', 'AAA', 1
union all select '2013-01-04', 'CCC', 2
union all select '2013-01-06', 'BBB', 3
union all select '2013-01-05', 'BBB', 1
union all select '2013-01-02', 'AAA', 2
union all select '2013-01-05', 'BBB', 2
union all select '2013-01-06', 'BBB', 4
union all select '2013-01-03', 'AAA', 3
union all select '2013-01-04', 'AAA', 4
union all select '2013-01-03', 'CCC', 1
)
select Sign , Time,SN
from
(
select *,
MIN(Time) over(partition by Sign) min_time
from t
)tt
order by min_time,SIGN,SN
/*
SignTimeSN
AAA2013-01-011
AAA2013-01-022
AAA2013-01-033
AAA2013-01-044
CCC2013-01-031
CCC2013-01-042
BBB2013-01-051
BBB2013-01-052
BBB2013-01-063
BBB2013-01-064
*/