order by rq ,case when gx='aaaa' then mj end desc ,case when gx='aaaa' then hd end desc ,case when charindex('ys',gx)>0 then 0 else 1 end
select * from t order by rq, case gx when 'aaaa' then mj end desc, case gx when 'aaaa' then hd end desc, case gx when 'ys' then 0 else 1 end
如果你要最后的条件优先就需要将它放在前面 select * from t order by rq, case gx when 'ys' then 0 else 1 end, case gx when 'aaaa' then mj end desc, case gx when 'aaaa' then hd end desc
如果你要最后的条件优先就需要将它放在前面 select * from t order by rq, case gx when 'ys' then 0 else 1 end, case gx when 'aaaa' then mj end desc, case gx when 'aaaa' then hd end desc
select * from t order by rq, case gx when 'aaaa' then mj end desc, case gx when 'aaaa' then hd end desc, case gx when 'ys' then 0 else 1 end
select * from TableName order by rwdh,xh,case gx when '\root\s\qw\qw2\' then dwmj desc else dwmj end,len(cast(hd as char) desc 最后一句意思没有看明白,但上面可供参考
select * from 表 tem order by rwdh,xh, case when (select count(distinct gx) from 表 where gx in ('\root\s\qw\qw2\','\root\s\ys\ys\') and rwdh=tem.rwdh)=0 then 0 else 1 end, case when exists(select 1 from 表 where gx='\root\s\qw\qw2\' and rwdh=tem.rwdh) then dwmj end desc, case when exists(select 1 from 表 where gx='\root\s\qw\qw2\' and rwdh=tem.rwdh) then hd end desc
--排序 select * from 表 order by rwdh,xh ,case when gx='\root\s\ys\ys\' then 1 else 0 end ,case when gx='\root\s\qw\qw2\' then dwmj else (select max(dwmj) from @tb)-dwmj end desc ,case when gx='\root\s\qw\qw2\' then hd else (select max(hd) from @tb)-hd end desc
--不知道楼主是否这个意思?declare @tb table(rwdh varchar(20),xh int,dwmj decimal(10,4),hd decimal(10,4),gx varchar(20)) insert into @tb select 'KFJH-03-09-04-01_1',1,.4623,3.2,'\root\s\yb\yb2\' union all select 'KFJH-03-09-04-01_1',2,.4623,3.2,'\root\s\ya\ya4\' union all select 'KFJH-03-09-04-01_1',3,.4623,3.2,'\root\s\ya\ya5\' union all select 'KFJH-03-09-04-01_1',4,.4623,3.2,'\root\s\qw\qw2\' union all select 'KFJH-03-09-04-01_1',5,.4623,3.2,'\root\s\mb\mb3\' union all select 'KFJH-03-09-04-01_2',1,.4623,3.2,'\root\s\yb\yb2\' union all select 'KFJH-03-09-04-01_2',2,.4623,3.2,'\root\s\ya\ya4\' union all select 'KFJH-03-09-04-01_2',3,.4623,3.2,'\root\s\ya\ya5\' union all select 'KFJH-03-09-04-01_2',4,.4623,3.2,'\root\s\qw\qw2\' union all select 'KFJH-03-09-04-01_2',5,.4623,3.2,'\root\s\mb\mb3\' union all select 'XSJH-03-09-04-01_1',1,.32,5,'\root\s\ya\ya4\' union all select 'XSJH-03-09-04-01_1',2,.32,5,'\root\s\ya\ta7\' union all select 'XSJH-03-09-04-01_1',3,.32,5,'\root\s\pg\pg2\' --排序 select * from @tb order by rwdh,xh ,case when gx='\root\s\ys\ys\' then 1 else 0 end ,case when gx='\root\s\qw\qw2\' then dwmj else (select max(dwmj) from @tb)-dwmj end desc ,case when gx='\root\s\qw\qw2\' then hd else (select max(hd) from @tb)-hd end desc/*--测试结果 rwdh xh dwmj hd gx -------------------- ----------- ------------ ------------ -------------------- KFJH-03-09-04-01_1 1 .4623 3.2000 \root\s\yb\yb2\ KFJH-03-09-04-01_1 2 .4623 3.2000 \root\s\ya\ya4\ KFJH-03-09-04-01_1 3 .4623 3.2000 \root\s\ya\ya5\ KFJH-03-09-04-01_1 4 .4623 3.2000 \root\s\qw\qw2\ KFJH-03-09-04-01_1 5 .4623 3.2000 \root\s\mb\mb3\ KFJH-03-09-04-01_2 1 .4623 3.2000 \root\s\yb\yb2\ KFJH-03-09-04-01_2 2 .4623 3.2000 \root\s\ya\ya4\ KFJH-03-09-04-01_2 3 .4623 3.2000 \root\s\ya\ya5\ KFJH-03-09-04-01_2 4 .4623 3.2000 \root\s\qw\qw2\ KFJH-03-09-04-01_2 5 .4623 3.2000 \root\s\mb\mb3\ XSJH-03-09-04-01_1 1 .3200 5.0000 \root\s\ya\ya4\ XSJH-03-09-04-01_1 2 .3200 5.0000 \root\s\ya\ta7\ XSJH-03-09-04-01_1 3 .3200 5.0000 \root\s\pg\pg2\(所影响的行数为 13 行) --*/
select * from @tb order by case when gx='\root\s\ys\ys\' then 1 else 0 end ,rwdh,xh ,case when gx='\root\s\qw\qw2\' then dwmj desc ,case when gx='\root\s\qw\qw2\' then hd else (select max(hd) from @tb)-hd end desc
rq
,case when gx='aaaa' then mj end desc
,case when gx='aaaa' then hd end desc
,case when charindex('ys',gx)>0 then 0 else 1 end
order by rq,
case gx when 'aaaa' then mj end desc,
case gx when 'aaaa' then hd end desc,
case gx when 'ys' then 0 else 1 end
select * from t
order by rq,
case gx when 'ys' then 0 else 1 end,
case gx when 'aaaa' then mj end desc,
case gx when 'aaaa' then hd end desc
select * from t
order by rq,
case gx when 'ys' then 0 else 1 end,
case gx when 'aaaa' then mj end desc,
case gx when 'aaaa' then hd end desc
order by rq,
case gx when 'aaaa' then mj end desc,
case gx when 'aaaa' then hd end desc,
case gx when 'ys' then 0 else 1 end
mj字段从大到小;
hd字段是从厚到薄;如果gx不是aaaa按什么排序?
------------------------- ----- ---------- ---------- -----------------
KFJH-03-09-04-01_1 1 .4623 3.2 \root\s\yb\yb2\
KFJH-03-09-04-01_1 2 .4623 3.2 \root\s\ya\ya4\
KFJH-03-09-04-01_1 3 .4623 3.2 \root\s\ya\ya5\
KFJH-03-09-04-01_1 4 .4623 3.2 \root\s\qw\qw2\
KFJH-03-09-04-01_1 5 .4623 3.2 \root\s\mb\mb3\
KFJH-03-09-04-01_2 1 .4623 3.2 \root\s\yb\yb2\
KFJH-03-09-04-01_2 2 .4623 3.2 \root\s\ya\ya4\
KFJH-03-09-04-01_2 3 .4623 3.2 \root\s\ya\ya5\
KFJH-03-09-04-01_2 4 .4623 3.2 \root\s\qw\qw2\
KFJH-03-09-04-01_2 5 .4623 3.2 \root\s\mb\mb3\
XSJH-03-09-04-01_1 1 .32 5 \root\s\ya\ya4\
XSJH-03-09-04-01_1 2 .32 5 \root\s\ya\ta7\
XSJH-03-09-04-01_1 3 .32 5 \root\s\pg\pg2\ 这是我所用的数据(部分),先按日期排。
同一rwdh的数据放在一起,按xh排序。
要进行排序的是不同的rwdh。根据gx是否包括\root\s\qw\qw2\ 这个值来进行处理。
如果有则dwmj字段从大到小;hd字段是从厚到薄;
并且判断这一rwdh中的gx中是否有\root\s\ys\ys\这个值。有则放在前面。
没有就正常排!注意:同一rwdh的数据必须连续!!!
最后一句意思没有看明白,但上面可供参考
order by rwdh,xh,
case when (select count(distinct gx) from 表 where gx in ('\root\s\qw\qw2\','\root\s\ys\ys\') and rwdh=tem.rwdh)=0 then 0 else 1 end,
case when exists(select 1 from 表 where gx='\root\s\qw\qw2\' and rwdh=tem.rwdh) then dwmj end desc,
case when exists(select 1 from 表 where gx='\root\s\qw\qw2\' and rwdh=tem.rwdh) then hd end desc
select * from 表
order by rwdh,xh
,case when gx='\root\s\ys\ys\' then 1 else 0 end
,case when gx='\root\s\qw\qw2\' then dwmj
else (select max(dwmj) from @tb)-dwmj end desc
,case when gx='\root\s\qw\qw2\' then hd
else (select max(hd) from @tb)-hd end desc
insert into @tb
select 'KFJH-03-09-04-01_1',1,.4623,3.2,'\root\s\yb\yb2\'
union all select 'KFJH-03-09-04-01_1',2,.4623,3.2,'\root\s\ya\ya4\'
union all select 'KFJH-03-09-04-01_1',3,.4623,3.2,'\root\s\ya\ya5\'
union all select 'KFJH-03-09-04-01_1',4,.4623,3.2,'\root\s\qw\qw2\'
union all select 'KFJH-03-09-04-01_1',5,.4623,3.2,'\root\s\mb\mb3\'
union all select 'KFJH-03-09-04-01_2',1,.4623,3.2,'\root\s\yb\yb2\'
union all select 'KFJH-03-09-04-01_2',2,.4623,3.2,'\root\s\ya\ya4\'
union all select 'KFJH-03-09-04-01_2',3,.4623,3.2,'\root\s\ya\ya5\'
union all select 'KFJH-03-09-04-01_2',4,.4623,3.2,'\root\s\qw\qw2\'
union all select 'KFJH-03-09-04-01_2',5,.4623,3.2,'\root\s\mb\mb3\'
union all select 'XSJH-03-09-04-01_1',1,.32,5,'\root\s\ya\ya4\'
union all select 'XSJH-03-09-04-01_1',2,.32,5,'\root\s\ya\ta7\'
union all select 'XSJH-03-09-04-01_1',3,.32,5,'\root\s\pg\pg2\' --排序
select * from @tb
order by rwdh,xh
,case when gx='\root\s\ys\ys\' then 1 else 0 end
,case when gx='\root\s\qw\qw2\' then dwmj
else (select max(dwmj) from @tb)-dwmj end desc
,case when gx='\root\s\qw\qw2\' then hd
else (select max(hd) from @tb)-hd end desc/*--测试结果
rwdh xh dwmj hd gx
-------------------- ----------- ------------ ------------ --------------------
KFJH-03-09-04-01_1 1 .4623 3.2000 \root\s\yb\yb2\
KFJH-03-09-04-01_1 2 .4623 3.2000 \root\s\ya\ya4\
KFJH-03-09-04-01_1 3 .4623 3.2000 \root\s\ya\ya5\
KFJH-03-09-04-01_1 4 .4623 3.2000 \root\s\qw\qw2\
KFJH-03-09-04-01_1 5 .4623 3.2000 \root\s\mb\mb3\
KFJH-03-09-04-01_2 1 .4623 3.2000 \root\s\yb\yb2\
KFJH-03-09-04-01_2 2 .4623 3.2000 \root\s\ya\ya4\
KFJH-03-09-04-01_2 3 .4623 3.2000 \root\s\ya\ya5\
KFJH-03-09-04-01_2 4 .4623 3.2000 \root\s\qw\qw2\
KFJH-03-09-04-01_2 5 .4623 3.2000 \root\s\mb\mb3\
XSJH-03-09-04-01_1 1 .3200 5.0000 \root\s\ya\ya4\
XSJH-03-09-04-01_1 2 .3200 5.0000 \root\s\ya\ta7\
XSJH-03-09-04-01_1 3 .3200 5.0000 \root\s\pg\pg2\(所影响的行数为 13 行)
--*/
order by case when gx='\root\s\ys\ys\' then 1 else 0 end
,rwdh,xh
,case when gx='\root\s\qw\qw2\' then dwmj desc
,case when gx='\root\s\qw\qw2\' then hd
else (select max(hd) from @tb)-hd end desc