排序问题..

[it_sql]

table1,table2
table1
a,b,c
dh001005,dh001014,10
dh001015,dh001024,10
dh001025,dh001034,10
dh001035,dh001044,10
......
dh001105,dh001114,10
table2
a
dh001056
dh001059
dh001103
dh001099
排序table1，table2中数据在table1中存在则优先排序，如上列的排序结果应该是：
dh001054,dh001064,10 //table2中的dh001056，dh001059在此范围
dh001094,dh001104,10 //table2中的dh001103，dh001099在此范围
dh001005,dh001014,10
dh001015,dh001024,10
dh001025,dh001034,10
dh001035,dh001044,10
......
怎么查询。。。?

[yeah920]

帮忙顶起来。

[pt1314917]

--> 测试数据: @table1
declare @table1 table (a varchar(8),b varchar(8),c int)
insert into @table1
select 'dh001005','dh001014',10 union all
select 'dh001015','dh001024',10 union all
select 'dh001025','dh001034',10 union all
select 'dh001035','dh001044',10 union all
select 'dh001045','dh001054',10 union all
select 'dh001055','dh001064',10 union all
select 'dh001065','dh001074',10 union all
select 'dh001075','dh001084',10 union all
select 'dh001085','dh001094',10 union all
select 'dh001095','dh001104',10 union all
select 'dh001105','dh001114',10
--> 测试数据: @table2
declare @table2 table (a varchar(8))
insert into @table2
select 'dh001056' union all
select 'dh001059' union all
select 'dh001103' union all
select 'dh001099'

select * from @table1 a
order by case when exists(select 1 from @table2 where cast(right(a,len(a)-2) as int) between cast(right(a.a,len(a.a)-2) as int) and cast(right(a.b,len(a.b)-2) as int))
then 0 else 1 end 

[橡胶轮胎行业数字化高总]

create table table1 (a varchar(10),b varchar(10),c int)
insert table1
select 'dh001005','dh001014',10 union
select 'dh001015','dh001024',10 union
select 'dh001025','dh001034',10
create table table2 (a varchar(10),b varchar(10))
insert table2
select 'dh001005','1'

select T1.a,T1.b,T1.c
from table1 T1 left join 
     table2 T2 on T1.a=T2.a
order by case when T2.b is not null then char(0) else 'a' end

drop table table1,table2

结果显示：

[橡胶轮胎行业数字化高总]

(3 行受影响)

(1 行受影响)
a b c
---------- ---------- -----------
dh001005 dh001014 10
dh001015 dh001024 10
dh001025 dh001034 10

(3 行受影响)

[橡胶轮胎行业数字化高总]

思路，增加了一个用于排序的字段，通过左连接方式，不存在信息为null的条件进行排序。

order by case when T2.b is not null then char(0) else 'a' end

这个是排序的关键所在。

[中国风]

參照
http://topic.csdn.net/u/20080612/22/c850499f-bce3-4877-82d5-af2357857872.html

[it_sql]

还有一个问题想不明白,帮忙解决一下:
table1
a,b
1,dh10001005
1,dh10001006
1,dh10001007
1,dh10001008
1,dh10001009
1,dh10001010
2,dh10001005
2,dh10001006
2,dh10001007
2,dh10001008
2,dh10001009
2,dh10001010
.....
我想得到的结果是:
1,dh10001005/dh10001006/dh10001007/dh10001008/dh10001009...
2,dh10001005/dh10001006/dh10001007/dh10001008/dh10001009...
我的函数是:

CREATE   function   dbo.hebing(@a VARCHAR(20)) 
returns   varchar(8000) 
as 
begin 
        declare   @ret   varchar(8000) 
        set   @ret   =   '' 
        select   @ret   =   @ret+case when charindex(b+'/',@ret) > 0 then '' else '/'+rtrim(b) end    from  table where   a=   @a 
       set   @ret   =   stuff(@ret,1,1,'') 
        return   @ret   
end 
select a,dbo.hebing(a) from table

假如1有200多行,用dbo.hebing(a)查询到的结果是不全的,少了很多,我算了算,没有到8000个字符啊,是为什么呢?

[水族杰纶]

[Quote=引用 6 楼 it_sql 的回复:]
还有一个问题想不明白,帮忙解决一下:
table1
a,b
1,dh10001005
1,dh10001006
1,dh10001007
1,dh10001008
1,dh10001009
1,dh10001010
2,dh10001005
2,dh10001006
2,dh10001007
2,dh10001008
2,dh10001009
2,dh10001010
.....
我想得到的结果是:
1,dh10001005/dh10001006/dh10001007/dh10001008/dh10001009...
2,dh10001005/dh10001006/dh10001007/dh10001008/dh10001009...
我的函数是:
…
[/Quote]

你return   LEN(@ret) 看看有多長   

[it_sql]

绝对没有超过的..

[it_sql]

[Quote=引用 9 楼 roy_88 的回复:]
參照
http://topic.csdn.net/u/20080612/22/c850499f-bce3-4877-82d5-af2357857872.html
[/Quote]
还是不行啊 ,和用我的那个函数得出来的结果是一样的,我用len()返回,发现长度最长的都没有超过2000个,怎么回事呢?