SQL一张表排序的问题

[mryecq]

表结构
fid:父id 只有2级
id name fid sort isshow
1 a 0 2 true
2 b 0 1 true
3 c 0 3 false
4 aa 1 1 true
5 ab 1 2 true
6 ba 2 1 true
7 bb 2 2 true
8 bc 2 3 true
9 ac 1 3 true
10 ca 3 1 false
11 cb 3 2 false

查询语句?
select * from table where fid<>0 and isshow=1 order by ????

先对父级的sort进行排序
需要的结果:
id name fid sort isshow
6 ba 2 1 true
7 bb 2 2 true
8 bc 2 3 true
4 aa 1 1 true
5 ab 1 2 true
9 ac 1 3 true

[xuam]

select * from table where fid<>0 and isshow=1 order by fid desc,sort  asc

[叶子]

declare @t table (id int,name varchar(2),fid int,sort int,isshow varchar(5))
insert into @t
select 1,'a',0,2,'true' union all
select 2,'b',0,1,'true' union all
select 3,'c',0,3,'false' union all
select 4,'aa',1,1,'true' union all
select 5,'ab',1,2,'true' union all
select 6,'ba',2,1,'true' union all
select 7,'bb',2,2,'true' union all
select 8,'bc',2,3,'true' union all
select 9,'ac',1,3,'true' union all
select 10,'ca',3,1,'false' union all
select 11,'cb',3,2,'false'

select * from @t where fid<>0 and isshow='true'
order by fid desc,sort
/*
id          name fid         sort        isshow
----------- ---- ----------- ----------- ------
6           ba   2           1           true
7           bb   2           2           true
8           bc   2           3           true
4           aa   1           1           true
5           ab   1           2           true
9           ac   1           3           true
*/

[mryecq]

前面两位大哥没理解我的意思啊...

[mryecq]

先对父级的sort进行排序

[快溜]

不对吗？

[小海贼]

看你的问题因该是父级表中也包含Sort字段了。那就用做连接实现，假设你的附表为 tabParent 字段有fid,sort
假设父表是以倒序排序：
select a.* from tablename a left join tabParent b on a.fid=b.fid
where a.isshow=1 order by b.sort desc,a.asc

[小海贼]

看来我是理解你的意思了

[叶子]

[Quote=引用 4 楼 mryecq 的回复:]
先对父级的sort进行排序
[/Quote]
你要什么样的结果？

[ZhuZhuYuXiang]

select * from table where fid<>0 and isshow='true' order by fid desc,name desc