表结构
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
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
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
*/
假设父表是以倒序排序:
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
select * from table where fid<>0 and isshow='true' order by fid desc,name desc