想了半天,还是发到这个难度大一点的地方来好了,基础区肯定没人能答得上

Question

有一个这样的表结构
ID ClassName ClassID SortId
1  湖南省      0       2
2  广东省      0       1
3  长沙市      1       2
4  湘潭        1       1
5  深圳市      2       2
6  广州市      2       1这是一个省市结构,现在我要把它显示在下拉框中,原来我们做的方式是首先select * from table where classid=0 把省显示出来,然后在每个省的里面再加一个循环读取classid等于这个省的ID,这样读取数据库几次.对于有几百万条记录的表来说,不太好.现在我从网上找到一个方法,如下sql语句,
sql = "select case [classid] when 0 then [classname] else '-' end as class1,case [classid] when 0 then '-' else [classname] end as class2 from p_class where username='"&session("UserName")&"' order by case [classid] when 0 then [id]*2 else [classid]*2+1 end,sortid"这样显示出来就是一个已成分好二级类的表了.真的是很方便,在高兴之余,我又发现一个问题,现在二级类可以排序,但是一级类就不能排序的了.本来湖南和广东,是应该先显示广东再显示湖南的,现在排不了序,就先显示湖南,再显示广东了.大家可以先建表试一下再回答,这个确实有点难度,这个sql语句我都是花了半天工夫才看懂的.只是在排序这上面找不着北了.小类是可以排序的.因为我后面加了一个sortid,但是大类不行.
高手帮忙解决,和我一样低手的就当学习学习.在优化数据库方面,这个很重要.值得一学.当然你的记录没上十万之前,可以不用看.

Answer 1

参考：
create table tb(id       int,pid       int,poder       int,sname       varchar(100))   
insert   tb   select   1,0,2,'N02' 
union   all   select   2,0,1,'N01' 
union   all   select   3,0,3,'N03' 
union   all   select   4,1,2,'N0202' 
union   all   select   5,4,1,'N020201' 
union   all   select   6,1,1,'N0201' 
union   all   select   7,4,2,'N020202' 
union   all   select   8,0,4,'N04' 
union   all   select   9,8,1,'N0401'   go
create function fn_Test(@id int)
returns varchar(8000) as
begin
    declare @ids varchar(8000)
    select @ids=','
    select @ids=@ids+rtrim(id)+dbo.fn_Test(id) from tb where pid=@id order by poder
    return @ids
end
godeclare @ids varchar(8000)
select @ids=','+dbo.fn_Test(0)
--select @ids
select * from tb
order by charindex(','+rtrim(id)+',', @ids)drop function dbo.fn_Test
drop table tb/*
id          pid         poder       sname
----------- ----------- ----------- ------------------------
2           0           1           N01
1           0           2           N02
6           1           1           N0201
4           1           2           N0202
5           4           1           N020201
7           4           2           N020202
3           0           3           N03
8           0           4           N04
9           8           1           N0401(9 row(s) affected)
*/

Answer 2

楼主把湖南和广东的地市对应弄反了吧？
declare @t table(ID int,ClassName varchar(20),ClassID int,SortId int)
insert into @t values(1,'湖南省',0,2) 
insert into @t values(2,'广东省',0,1) 
insert into @t values(3,'长沙市',1,2) 
insert into @t values(4,'湘潭市',1,1) 
insert into @t values(5,'深圳市',2,2) 
insert into @t values(6,'广州市',2,1) select
    *
from
    @t
order by
    (case ClassID when 0 then SortId else ClassID end),
    (case ClassID when 0 then ClassID else SortId end)/*
ID          ClassName            ClassID     SortId      
----------- -------------------- ----------- ----------- 
2           广东省                  0           1
4           湘潭市                  1           1
3           长沙市                  1           2
1           湖南省                  0           2
6           广州市                  2           1
5           深圳市                  2           2
*/

Answer 3

create table tb(ID int, ClassName varchar(32), ClassID int, SortId int)   
insert tb select 1, '湖南省', 0,               2 
union all select 2, '广东省', 0 ,              1 
union all select 3, '长沙市', 1  ,             2 
union all select 4, '湘潭', 1    ,           1 
union all select 5, '深圳市', 2   ,            2 
union all select 6, '广州市', 2    ,           1 go
create function fn_Test(@id int)
returns varchar(8000) as
begin
    declare @ids varchar(8000)
    select @ids=','
    select @ids=@ids+rtrim(id)+dbo.fn_Test(ID) from tb where ClassID=@id order by SortId
    return @ids
end
godeclare @ids varchar(8000)
select @ids=','+dbo.fn_Test(0)
--select @ids
select * from tb
order by charindex(','+rtrim(id)+',', @ids)drop function dbo.fn_Test
drop table tb/*
ID          ClassName                        ClassID     SortId
----------- -------------------------------- ----------- -----------
2           广东省                              0           1
6           广州市                              2           1
5           深圳市                              2           2
1           湖南省                              0           2
4           湘潭                               1           1
3           长沙市                              1           2(6 row(s) affected)
*/

Answer 4

hehe...我也是开到最大了sql区.

Answer 5

看了楼上几个的回答,不是和我说的问题不相同之外,就是显示出来的并没有答到我的要求.真的是希望大家都真实的试一遍,我的省市之间的对应也没有搞错.现在我把表select出来大家看一下.
select * from p_class1 湖南 0 2
2 广东 0 1
4 长沙 1 3
5 冷水江 1 1
6 新化 1 2
7 深圳 2 1
8 广州 2 3
9 中山 2 2
10 湖北 0 2用了这个命令之后select   case   [classid]   when   0   then   [classname]   else   '-'   end   as   class1,case   [classid]   when   0   then   '-'   else   [classname]   end   as   class2 ,sortid  
from   p_class  
 order   by  
 case   [classid]   when   0   then   [id]*2   else   [classid]*2+1   end,case [classid]*2 when 0 then sortid else [sortid]*2+1 end1 湖南 - 2
5 - 冷水江 1
6 - 新化 2
4 - 长沙 3
2 广东 - 1
7 - 深圳 1
9 - 中山 2
8 - 广州 3
10 湖北 - 2大家看到什么了没有?
把类都分得很清楚了,像湖南下面的,冷水江,新化,长沙都按后面的排序,1,2,3显示出来.很完美了.但是有一点不好的就是,大类后面,湖南后面的sortid是2,广东的是1,湖南的是2,这里就没有排序,应该是先广东,再湖南,再湖北.各位大爷,你们如果真的按我的这个结构来做的话,还能轻易的像上面这样回复吗?不希望再有人随便就回复,上面几个我都测了,没有达到这种效果..

Answer 6

sorry,我是随便回复的.当我不存在吧.create table tb
(
    id int,
    classname nvarchar(20),
    classid int,
    sortid int
)insert tb select 1 ,N'湖南', 0, 2 
insert tb select 2 ,N'广东', 0, 1 
insert tb select 4 ,N'长沙', 1, 3 
insert tb select 5 ,N'冷水江', 1, 1 
insert tb select 6 ,N'新化', 1, 2 
insert tb select 7 ,N'深圳', 2, 1 
insert tb select 8 ,N'广州', 2, 3 
insert tb select 9 ,N'中山', 2, 2 
insert tb select 10 ,N'湖北', 0, 2 
goDECLARE @t_Level TABLE(ID int,classname nvarchar(20),classid int,sortid int,
                           Level int,Sort varchar(8000))
DECLARE @Level int
SET @Level=1
INSERT @t_Level SELECT ID,classname,classid,sortid,@Level,cast(sortid as varchar)
FROM tb
WHERE classid=0WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,a.classname,a.classid,a.sortid,@Level,b.sort+cast(a.sortid as varchar)
FROM tb a,@t_Level b
WHERE a.classid=b.id
AND b.Level=@Level-1
ENDselect id,classname,sortid from @t_Level order by sort,sortiddrop table tb/*
id          classname            sortid      
----------- -------------------- ----------- 
2           广东                   1
7           深圳                   1
9           中山                   2
8           广州                   3
1           湖南                   2
10          湖北                   2
5           冷水江                  1
6           新化                   2
4           长沙                   3（所影响的行数为 9 行）
*/

Answer 7

因为子节点无法获取父节点的sort所以必须联合一个查询--建立测试环境
set nocount on
create table p_class(ID varchar(20),ClassName varchar(20),ClassID varchar(20),SortId varchar(20))
insert into p_class select '1','湖南省','0','2'
insert into p_class select '2','广东省','0','1'
insert into p_class select '3','长沙市','1','2'
insert into p_class select '4','湘潭','1','1'
insert into p_class select '5','深圳市','2','2'
insert into p_class select '6','广州市','2','1'
go
--测试
select case classid 
when 0 then  classname  else '-' end as class1,
  case classid 
when 0 then '-'  else classname end  as class2 ,sortid  
from p_class a 
left join(select id as id2 ,classid as classid2,sortid as sortid2 from p_class where classid=0) b 
on a.classid=b.id2
  order by 
isnull(b.sortid2,a.sortid)*10000+
case  classid  
when 0 then  id *2  else  classid *2+1 end,
case classid  
when 0 then   sortid   else sortid *2+1 end 
--删除测试环境
drop table p_class
 set nocount off/*
广东省 - 1
- 广州市 1
- 深圳市 2
湖南省 - 2
- 湘潭 1
- 长沙市 2
*/

Answer 8

create table tb(ID int, ClassName varchar(8), ClassID int, SortId int)   
insert tb select 1 ,N'湖南', 0, 2 
insert tb select 2 ,N'广东', 0, 1 
insert tb select 4 ,N'长沙', 1, 3 
insert tb select 5 ,N'冷水江', 1, 1 
insert tb select 6 ,N'新化', 1, 2 
insert tb select 7 ,N'深圳', 2, 1 
insert tb select 8 ,N'广州', 2, 3 
insert tb select 9 ,N'中山', 2, 2 
insert tb select 10 ,N'湖北', 0, 2 
go
create function fn_Test(@id int)
returns varchar(8000) as
begin
    declare @ids varchar(8000)
    select @ids=','
    select @ids=@ids+rtrim(id)+dbo.fn_Test(ID) from tb where ClassID=@id order by SortId
    return @ids
end
godeclare @ids varchar(8000)
select @ids=','+dbo.fn_Test(0)
--select @ids
select ID
,ClassName=case ClassID when 0 then '' else '-' end+ClassName
,SortId=case ClassID when 0 then '-' else '' end+ltrim(SortId)
from tb
order by charindex(','+rtrim(id)+',', @ids)drop function dbo.fn_Test
drop table tb/*
ID          ClassName SortId
----------- --------- -------------
2           广东        -1
7           -深圳       1
9           -中山       2
8           -广州       3
10          湖北        -2
1           湖南        -2
5           -冷水江      1
6           -新化       2
4           -长沙       3(9 row(s) affected)
*/