select * from tablename order by case when upid=0 then ID else upid end,id
select * from tablename order by name
--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL DROP TABLE [tb] GO CREATE TABLE [tb] ( [id] INT, upid INT, [name] NVARCHAR(50) ) INSERT [tb] SELECT 1,0,'第一大项' UNION ALL SELECT 2,0,'第二大项' UNION ALL SELECT 3,2,'第一大项第一小项' UNION ALL SELECT 4,2,'第一大项第二小项' GO SELECT * FROM tb order by LEFT([name],4) desc,id/*id upid name ----------- ----------- -------------------------------------------------- 1 0 第一大项 3 2 第一大项第一小项 4 2 第一大项第二小项 2 0 第二大项(4 行受影响)*/ BOM按节点排序应用实例 ---------------------------------------------------------------------------- Author : htl258(Tony)-- Date : 2010-04-23 02:37:28-- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) -- Jul 9 2008 14:43:34 -- Copyright (c) 1988-2008 Microsoft Corporation-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)-- Subject: BOM按节点排序应用实例-------------------------------------------------------------------------- --实例1:--> 生成测试数据表:tb IF NOT OBJECT_ID('[tb]') IS NULL DROP TABLE [tb]GOCREATE TABLE [tb]([id] INT,[code] NVARCHAR(10),[pid] INT,[name] NVARCHAR(10))INSERT [tb]SELECT 1,'01',0,N'服装' UNION ALLSELECT 2,'01',1,N'男装' UNION ALLSELECT 3,'01',2,N'西装' UNION ALLSELECT 4,'01',3,N'全毛' UNION ALLSELECT 5,'02',3,N'化纤' UNION ALLSELECT 6,'02',2,N'休闲装' UNION ALLSELECT 7,'02',1,N'女装' UNION ALLSELECT 8,'01',7,N'套装' UNION ALLSELECT 9,'02',7,N'职业装' UNION ALLSELECT 10,'03',7,N'休闲装' UNION ALLSELECT 11,'04',7,N'西装' UNION ALLSELECT 12,'01',11,N'全毛' UNION ALLSELECT 13,'02',11,N'化纤' UNION ALLSELECT 14,'05',7,N'休闲装'GO--SELECT * FROM [tb] -->SQL查询如下: ;WITH T AS( SELECT CAST(CODE AS VARCHAR(20)) AS CODE,*, CAST(ID AS VARBINARY(MAX)) AS px FROM tb AS A WHERE NOT EXISTS(SELECT * FROM tb WHERE id=A.pid) UNION ALL SELECT CAST(B.CODE+A.CODE AS VARCHAR(20)),A.*, CAST(B.px+CAST(A.ID AS VARBINARY) AS VARBINARY(MAX)) FROM tb AS A JOIN T AS B ON A.pid=B.id)SELECT Code,Name FROM T ORDER BY px/*Code Name-------------------- ----------01 服装0101 男装010101 西装01010101 全毛01010102 化纤010102 休闲装0102 女装010201 套装010202 职业装010203 休闲装010204 西装01020401 全毛01020402 化纤010205 休闲装 (14 行受影响)*/ --实例2:--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL DROP TABLE [tb] GO CREATE TABLE [tb]([id] INT,[parentid] INT,[categoryname] NVARCHAR(10)) INSERT [tb] SELECT 1,0,'test1' UNION ALL SELECT 2,0,'test2' UNION ALL SELECT 3,1,'test1.1' UNION ALL SELECT 4,2,'test2.1' UNION ALL SELECT 5,3,'test1.1.1' UNION ALL SELECT 6,1,'test1.2' GO --SELECT * FROM [tb]-->SQL查询如下: ;WITH T AS ( SELECT *,CAST(ID AS VARBINARY(MAX)) AS px FROM tb AS A WHERE NOT EXISTS(SELECT * FROM tb WHERE id=A.[parentid]) UNION ALL SELECT A.*,CAST(B.px+CAST(A.ID AS VARBINARY) AS VARBINARY(MAX)) FROM tb AS A JOIN T AS B ON A.[parentid]=B.id ) SELECT [id],[parentid],[categoryname] FROM T ORDER BY px /* id parentid categoryname ----------- ----------- ------------ 1 0 test1 3 1 test1.1 5 3 test1.1.1 6 1 test1.2 2 0 test2 4 2 test2.1(6 行受影响) */本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/htl258/archive/2010/04/23/5518166.aspx
id 和upid有关联吗? select * from table name order by name是不可以的查出来第二大项在第二个而不是第二个 我觉的如果有关系的话这个第一大项的upid应该是1,不应该是2 不知道楼主的upid是干嘛的
SELECT * FROM test_1 order by LEFT([name],4) desc,id 这个是可以的
from tablename
order by case when upid=0 then ID else upid end,id
from tablename
order by name
--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]
(
[id] INT,
upid INT,
[name] NVARCHAR(50)
)
INSERT [tb]
SELECT 1,0,'第一大项' UNION ALL
SELECT 2,0,'第二大项' UNION ALL
SELECT 3,2,'第一大项第一小项' UNION ALL
SELECT 4,2,'第一大项第二小项'
GO
SELECT * FROM tb order by LEFT([name],4) desc,id/*id upid name
----------- ----------- --------------------------------------------------
1 0 第一大项
3 2 第一大项第一小项
4 2 第一大项第二小项
2 0 第二大项(4 行受影响)*/
BOM按节点排序应用实例
---------------------------------------------------------------------------- Author : htl258(Tony)-- Date : 2010-04-23 02:37:28-- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) -- Jul 9 2008 14:43:34 -- Copyright (c) 1988-2008 Microsoft Corporation-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)-- Subject: BOM按节点排序应用实例-------------------------------------------------------------------------- --实例1:--> 生成测试数据表:tb IF NOT OBJECT_ID('[tb]') IS NULL DROP TABLE [tb]GOCREATE TABLE [tb]([id] INT,[code] NVARCHAR(10),[pid] INT,[name] NVARCHAR(10))INSERT [tb]SELECT 1,'01',0,N'服装' UNION ALLSELECT 2,'01',1,N'男装' UNION ALLSELECT 3,'01',2,N'西装' UNION ALLSELECT 4,'01',3,N'全毛' UNION ALLSELECT 5,'02',3,N'化纤' UNION ALLSELECT 6,'02',2,N'休闲装' UNION ALLSELECT 7,'02',1,N'女装' UNION ALLSELECT 8,'01',7,N'套装' UNION ALLSELECT 9,'02',7,N'职业装' UNION ALLSELECT 10,'03',7,N'休闲装' UNION ALLSELECT 11,'04',7,N'西装' UNION ALLSELECT 12,'01',11,N'全毛' UNION ALLSELECT 13,'02',11,N'化纤' UNION ALLSELECT 14,'05',7,N'休闲装'GO--SELECT * FROM [tb] -->SQL查询如下: ;WITH T AS( SELECT CAST(CODE AS VARCHAR(20)) AS CODE,*, CAST(ID AS VARBINARY(MAX)) AS px FROM tb AS A WHERE NOT EXISTS(SELECT * FROM tb WHERE id=A.pid) UNION ALL SELECT CAST(B.CODE+A.CODE AS VARCHAR(20)),A.*, CAST(B.px+CAST(A.ID AS VARBINARY) AS VARBINARY(MAX)) FROM tb AS A JOIN T AS B ON A.pid=B.id)SELECT Code,Name FROM T ORDER BY px/*Code Name-------------------- ----------01 服装0101 男装010101 西装01010101 全毛01010102 化纤010102 休闲装0102 女装010201 套装010202 职业装010203 休闲装010204 西装01020401 全毛01020402 化纤010205 休闲装 (14 行受影响)*/ --实例2:--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([id] INT,[parentid] INT,[categoryname] NVARCHAR(10))
INSERT [tb]
SELECT 1,0,'test1' UNION ALL
SELECT 2,0,'test2' UNION ALL
SELECT 3,1,'test1.1' UNION ALL
SELECT 4,2,'test2.1' UNION ALL
SELECT 5,3,'test1.1.1' UNION ALL
SELECT 6,1,'test1.2'
GO
--SELECT * FROM [tb]-->SQL查询如下:
;WITH T AS
(
SELECT *,CAST(ID AS VARBINARY(MAX)) AS px
FROM tb AS A
WHERE NOT EXISTS(SELECT * FROM tb WHERE id=A.[parentid])
UNION ALL
SELECT A.*,CAST(B.px+CAST(A.ID AS VARBINARY) AS VARBINARY(MAX))
FROM tb AS A
JOIN T AS B
ON A.[parentid]=B.id
)
SELECT [id],[parentid],[categoryname] FROM T
ORDER BY px
/*
id parentid categoryname
----------- ----------- ------------
1 0 test1
3 1 test1.1
5 3 test1.1.1
6 1 test1.2
2 0 test2
4 2 test2.1(6 行受影响)
*/本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/htl258/archive/2010/04/23/5518166.aspx
select * from table name order by name是不可以的查出来第二大项在第二个而不是第二个
我觉的如果有关系的话这个第一大项的upid应该是1,不应该是2
不知道楼主的upid是干嘛的
这个是可以的
1 0 第一大项
2 0 第二大项
3 2 第一大项第一小项
4 2 第一大项第二小项
测试数据有问题吧 ?是否应该
1 0 第一大项
2 0 第二大项
3 1 第一大项第一小项
4 1 第一大项第二小项
那为啥不直接用BOM结构的 ?
数据是有失误
应该是这样的
id upid name
1 0 第一大项
2 0 第二大项
3 1 第一大项第一小项
4 1 第一大项第二小项
这是个老系统,改数据结构有些麻烦
我先挨个测试下哈
name那列我是为了说明数据关系才给出这样的名字,所以按name来分是不实用的
upid是指父级id
4楼结果很全,第三个可以不改数据表就正常运行
好了结贴