一个捆扰我好几天sql分组排(树形结构查询+排序)

我要做的是一个菜单的排序问题
id           fatherId             menuPath                 number
107             1                   /107                     38
108            107                  /107/108                  2
109            107                  /107/109                  1
110            109                  /107/109/110              5
111            109                 /107/109/111               4
112             1                  /112                      23
113            112                 /112/113                   2
114            112                 /112/114                   1
115            114                 /112/114/115               9
116            114                 /112/114/116               8




我要实现的是先按menuPath分组  /107开头的是一组 /112开头的又是一组

fatherId是1的话代表是父菜单  父和父之间要排序(number38和number23)....最后父菜单下面的子菜单也要排序 按字段number排序 如下是107的子菜单
 108     107          /107/108        2 
 109     107          /107/109        1 
(我的菜单可以是无限级的  108和109的父菜单是107 113和114的父菜单是112  110和111的父菜单是109  115和116的父菜单114)


最后实现后的效果需要这个
id      fatherId        menuPath           number
112        1             /112               23       (一级父菜单)
114        112           /112/114            1     (一级下的子菜单,也可以是二级父菜单)
116        114           /112/114/116        8       (二级父菜单下的子菜单)
115        114           /112/114/115        9       (二级父菜单下的子菜单)
113        112           /112/113            2      (一级下的子菜单,也可以是二级父菜单)

107        1             /107                38         (一级父菜单)
109        107          /107/109             1    (一级下的子菜单,也可以是二级父菜单)  
111        109          /107/109/111         4      (二级父菜单下的子菜单)
110        109          /107/109/110         5      (二级父菜单下的子菜单)
108        107          /107/108             2    (一级下的子菜单,也可以是二级父菜单)
说的够详细了吧..大家吧 我脑袋都要爆炸了.可以随便增加字段  不管什么方法只要能实现就OK了嘿嘿

评论

30 楼 leadyu 2007-07-26  

如果你用oracle好办得很，一个SQL搞定，主要思路是通过connect by 语句先解决层级查询问题，然后套大的子查询，采用oracle分组函数语句，进行分组，组内排序用partition ** order by **语法，具体我就不写了，查查这两种语法的用法就知道了。

29 楼 ahau205109 2007-07-23  

id           fatherId             menuPath                 number    number_lev
107             1                   /107                     38      38
108            107                  /107/108                  2      38/2
109            107                  /107/109                  1      38/1
110            109                  /107/109/110              5      38/1/5
111            109                 /107/109/111               4      38/1/4
112             1                  /112                      23      23
113            112                 /112/113                   2      23/2
114            112                 /112/114                   1      23/1
115            114                 /112/114/115               9      23/1/9
116            114                 /112/114/116               8      23/1/8



order by number_lev ok?

28 楼 cat 2007-07-20  

Sql Server 2000还是2005? 2005对于递归查询有新的语法。如果是sqlserver 2000可以自己用temp table模拟递归来避免层次过深的问题。

27 楼 aijm 2007-07-20  

加了一个字段sortOrder,由上级的sortOrder + （本级number生成的等长字符串
需要触发器维护sortOrder字段

id    fatherID     menuPath    number      sortOrder
1	   NULL	      /1            1	     .0000000001
112	   1	      /112	        23	     .0000000001.0000000023
114	   112	      /112/114	    1	     .0000000001.0000000023.0000000001
116	   114	      /112/114/116	8	     .0000000001.0000000023.0000000001.0000000008
115	   114	      /112/114/115	9	     .0000000001.0000000023.0000000001.0000000009
113	   112	      /112/113	    2	     .0000000001.0000000023.0000000002
107	   1	      /107	        38	     .0000000001.0000000038
109	   107	      /107/109	    1	     .0000000001.0000000038.0000000001
111	   109	      /107/109/111  4	     .0000000001.0000000038.0000000001.0000000004
110	   109	      /107/109/110	5	     .0000000001.0000000038.0000000001.0000000005
108	   107	      /107/108	    2 	     .0000000001.0000000038.0000000002

26 楼 borland 2007-07-20  

oracle 的话试试：

select t.id,t.fatherid,t.menupath,t.number
from table1 t
connect by prior t.id=t.fatherid
start with t.child_key_num=1
order by number asc;

25 楼 emarket 2007-07-17  

如果是sql server
看看这个
http://vyaskn.tripod.com/hierarchies_in_sql_server_databases.htm

24 楼 emarket 2007-07-17  

如果是用oracle, 看看这个会不会有帮助
http://www.adp-gmbh.ch/ora/sql/connect_by.html

23 楼 junjie314 2007-06-30  

谢谢哦

22 楼 qinysong 2007-06-20  

junjie314 写道

如果ID小与32这个确实可以..但是如果ID大于32又有那个错了...实际运用中ID不可能小于32的吧....你可以试下哦..如果这个解决那就完美了..虽然只能支持到9层,不过应该也够了 嘿嘿

ID小与32是从哪里来的数值？
这种方法和ID的取值完全没有关系，就算ID大于1万，100万都没关系

这种方法的唯一限制是层数和number取值，因为太大了在计算的时候就超出了bigint的范围而溢出，不过六层/1000个节点在一般情况下应该已经足够了，再大的话适当调整一下也可以支持

21 楼 junjie314 2007-06-20  

如果ID小与32这个确实可以..但是如果ID大于32又有那个错了...实际运用中ID不可能小于32的吧....你可以试下哦..如果这个解决那就完美了..虽然只能支持到9层,不过应该也够了 嘿嘿

20 楼 qinysong 2007-06-19  

junjie314 写道

你这个函数真的写的不错  但是 还是有问题..你下看
id     fatherId menuPath  number
5 1 /5 23
7 5 /5/7 8
6 5 /5/6 9
8 6 /5/6/8 111
9 7 /5/7/9 222
2 1 /2 38
4 2 /2/4 4
3 2 /2/3 5

有多于三层子节点的话 排序就有问题 /5/7/9这个子节点应该排在他的父节点/5/7下面 现在却在/5/6/8下面了

这个问题好处理

    WHILE(@treeLevel<9) --此处9为目录层次数，经测试bigInt可以支持9级目录，再多目录层次就会出现益出   
    BEGIN   
        SET @weightLevel=@weightLevel*100  
        SET @treeLevel=@treeLevel+1  
    END   

你只需要缩小那个9(@treeLevel<9)，比如设为6，这样可以支持6层菜单，增加那个100（@weightLevel=@weightLevel*100 ）为1000就可以了，这样可以支持number最大为1000

具体数值根据你的需要设置

19 楼 junjie314 2007-06-19  

你这个函数真的写的不错  但是 还是有问题..你下看
id     fatherId menuPath  number
5 1 /5 23
7 5 /5/7 8
6 5 /5/6 9
8 6 /5/6/8 111
9 7 /5/7/9 222
2 1 /2 38
4 2 /2/4 4
3 2 /2/3 5

有多于三层子节点的话 排序就有问题 /5/7/9这个子节点应该排在他的父节点/5/7下面 现在却在/5/6/8下面了

18 楼 qinysong 2007-06-19  

没问题的，我测试的也是sql server

你再重新创建一个表，把数据导近来，然后拷贝上面sql函数在分析器中执行，你再试试
下面是创建表的语句

CREATE TABLE [dbo].[treeTable] (
	[id] [int] NOT NULL ,
	[fatherId] [int] NULL ,
	[menuPath] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	[number] [int] NULL 
) ON [PRIMARY]
GO

17 楼 junjie314 2007-06-19  

应该不会是数据库的问题吧

16 楼 junjie314 2007-06-19  

sqlserver

15 楼 qinysong 2007-06-19  

你是什么数据库？

14 楼 junjie314 2007-06-19  

TO qinysong: 用你的方法出现这个问题


服务器: 消息 217，级别 16，状态 1，过程 weightValue，行 28
超出了存储过程、函数、触发器或视图的最大嵌套层数（最大层数为 32）。

13 楼 qinysong 2007-06-19  

junjie314 写道

  有谁能不能说具体的实现!~~现在如果光排序的那没问题,但是各节点之间不是有顺序的吗??这个顺序是可以改的..现在主要问题是如果要改这个的话就比较麻烦了..比如有两个父节点顺序是23和38  现在把23这个改成39的话..那23下面的所有子节点都要修改..
  这个好象比较麻烦,要么是我设计的问题???

首先创建一个SQL函数：weightValue，如下

CREATE FUNCTION weightValue(@idValue int) RETURNS bigint AS
BEGIN
	DECLARE @return bigint
	DECLARE @fatherID int
	DECLARE @number int
	DECLARE @menuPath VARCHAR(50)
	DECLARE @treeLevel int
	DECLARE @weightLevel bigint

	SET @return = 0
	SET @weightLevel = 1

	SELECT @fatherID=fatherID,@menuPath=menuPath,@number=number FROM treeTable WHERE id=@idValue

	SELECT @treeLevel=len(@menuPath)-len(replace(@menuPath,'/',''))
	WHILE(@treeLevel<9) --此处9为目录层次数，经测试bigInt可以支持9级目录，再多目录层次就会出现益出
	BEGIN
		SET @weightLevel=@weightLevel*100
		SET @treeLevel=@treeLevel+1
	END
	SET @return=@number*@weightLevel

	IF(@fatherID = 1)
	BEGIN
		RETURN(@return)
	END

	RETURN ([dbo].weightValue(@fatherID)+@return)
END

然后一个简单的查询SQL即可，如下：

SELECT id,fatherId,menuPath,number FROM treeTable ORDER BY [dbo].weightValue(id)

查询结果就是想要的排列顺序，结果如下，因为结果是实时查询，所以节点可以随时配置

112	1	/112	23
114	112	/112/114	1
116	114	/112/114/116	8
115	114	/112/114/115	9
113	112	/112/113	2
107	1	/107	38
109	107	/107/109	1
111	109	/107/109/111	4
110	109	/107/109/110	5
108	107	/107/108	2

12 楼 junjie314 2007-06-18  

  有谁能不能说具体的实现!~~现在如果光排序的那没问题,但是各节点之间不是有顺序的吗??这个顺序是可以改的..现在主要问题是如果要改这个的话就比较麻烦了..比如有两个父节点顺序是23和38  现在把23这个改成39的话..那23下面的所有子节点都要修改..
  这个好象比较麻烦,要么是我设计的问题???

11 楼 抛出异常的爱 2007-06-18  

汗。。。。
这个你不写成程序么，我的SQL知识。。。。

用存储过程的话，
先用前一个ID
select出后一级的子菜单。。
之后遍历子菜单。。。、
每次遍历把游标放入一个
临时表，
直到完成。。。。（不知道sql能否递归，如不能请用递推来写）