Sql Server按树形结构排序查询表记录(CSDN论坛转载)
2023-09-11 14:15:10 时间
WITH T AS
(
SELECT CAST(Tree_Id AS VARCHAR(20)) AS CODE,*,CAST(City_Id AS VARBINARY(MAX)) AS px FROM Class_City AS A
WHERE NOT EXISTS(SELECT * FROM Class_City WHERE City_Id=A.Father_Id)
UNION ALL
SELECT CAST(B.Tree_Id+A.Tree_Id AS VARCHAR(20)),A.*,CAST(B.px+CAST(A.City_Id AS VARBINARY) AS VARBINARY(MAX)) FROM Class_City AS A
JOIN T AS B ON A.Father_Id=B.City_Id
)
SELECT City_Id,HelpCode,City_Name,Class_Class,Father_Id FROM T
ORDER BY px
以下是论坛中的代码:
--------------------------------------------------------------------------
-- 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]
GO
CREATE TABLE [tb]([id] INT,[code] NVARCHAR(10),[pid] INT,[name] NVARCHAR(10))
INSERT [tb]
SELECT 1,'01',0,N'服装' UNION ALL
SELECT 2,'01',1,N'男装' UNION ALL
SELECT 3,'01',2,N'西装' UNION ALL
SELECT 4,'01',3,N'全毛' UNION ALL
SELECT 5,'02',3,N'化纤' UNION ALL
SELECT 6,'02',2,N'休闲装' UNION ALL
SELECT 7,'02',1,N'女装' UNION ALL
SELECT 8,'01',7,N'套装' UNION ALL
SELECT 9,'02',7,N'职业装' UNION ALL
SELECT 10,'03',7,N'休闲装' UNION ALL
SELECT 11,'04',7,N'西装' UNION ALL
SELECT 12,'01',11,N'全毛' UNION ALL
SELECT 13,'02',11,N'化纤' UNION ALL
SELECT 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:
--> 生成测试数据表:tb
IF 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
相关文章
- SQL Server-聚焦sp_executesql执行动态SQL查询性能真的比exec好?
- Sql server在另一台服务器,在Visual Studio 中没问题,IIS中 提示“在与 SQL Server 建立连接时出现与网络相关的或特定于实例的错误。。。。”
- An introduction to Data-Tier applications in SQL Server
- An overview of the column level SQL Server encryption
- Changing SQL Server Collation After Installation
- CASE函数 sql server——分组查询(方法和思想) ref和out 一般处理程序结合反射技术统一执行客户端请求 遍历查询结果集,update数据 HBuilder设置APP状态栏
- 本人收藏的Sql server经典t-sql语句(备忘录,持续更新中)
- SQL Server 占用内存太高,查找占用内存高以及影响其性能的sql语句及解决方法
- SQL server CLR命令执行
- 《PowerShell V3——SQL Server 2012数据库自动化运维权威指南》——第2章 SQL Server和PowerShell基础任务2.1 介绍
- MYSQL导入csv类型的数据出现The MySQL server is running with the --secure-file-priv option
- sql:SQL Server metadata queries
- SQL Server 2012 读写分离设置
- sql server 数据分析优化实战(一)——SQL语句优化
- JavaWeb项目实现SQL Server的增删改查登录(大作业)
- Microsoft SQL Server 2008 R2官方中文版(SQL2008下载)
- Sql server不同数据类型间拼接(+)
- 【沉淀】懵懂入行,但一做就沉心钻研十年——记访谈阿里云SQL Server专家杨钊
- 轻松解决SQL Server 2005中的常见问题
- sql server游标的特性(套娃特性,排序就不套娃特性)
- (4.52)解决sql server动态SQL中对表字段操作时需要频繁 ISNULL的问题
- sql server该账户当前被锁定,所以用户'sa'登录失败。系统管理员无法将该账户解锁。(Microsoft SQL Server,错误:18486),登录错误18456
- sql server日志传送报警,该文件被占用
- (1.2)sql server for linux 开启代理服务(SQL AGENT),使用T-SQL新建作业
- 最小配置启动SQL SERVER,更改SQL Server最大内存大小导致不能启动的解决方法
- SQL CHECK sql server免费监控单实例工具
- (4.9)SQL Server如何校验备份文件
- (3.6)sql server存储引擎--文件与数据页及数据行的结构
- SQL Server Profiler 跟踪某个数据库某张表sql语句
- SQL SERVER的统计信息
- SQL Server创建dblink跨库查询
- SQL server 统计分组经计