SQL 关于高效率查询分叉结构的方法

GEshaoyebuhuaxin 2017-12-28 11:47:01
如题所示，现在需要查询一组数据，这组数据比较特殊，具有分叉结构



数据库结构是：
父类
子类
父类标识


父类就是类似汽车，车轮，发动机这些拥有下层结构的
子类就是对于汽车而言，子类是车壳，车轮，发动机，车载系统
而父类标识表示此子类也是父类


父类 子类 父类标识
汽车 车壳 0
汽车 车轮 1
汽车 发动机 1
汽车 车载系统 1
车轮 轮毂 0
车轮 轮胎 0
发动机 活塞 0
发动机 缸体 0
车载系统 传感器 0
车载系统 电子设备 0



现在需要查询出这种结构，结果类似下图 、

汽车
车壳
车轮
轮毂
轮胎
发动机
活塞
缸体
车载系统
传感器
电子设备

单纯依靠程序我C# 程序中的循环走，效率太慢，时间太长了。
各位看看有没有什么好的想法交流一下。
...全文
360 11 打赏 收藏 转发到动态 举报
写回复
用AI写文章
11 条回复
切换为时间正序
请发表友善的回复…
发表回复
道素 2017-12-30
  • 打赏
  • 举报
 回复
你再试试用临时表会否好点，虽然这个也是递归的，但换了种写法： 

ALTER FUNCTION fn_GetFullNode(@node nvarchar(100),@levelid varchar(100))
RETURNS @tbnodes TABLE(ID int IDENTITY(1,1), [父类] nvarchar(24),[子类] nvarchar(24),[父类标识] int,nodeid varchar(100))
AS 
BEGIN
    ;WITH nodes AS (
        SELECT 父类,子类,父类标识,CONVERT(VARCHAR(100),@levelid+LTRIM(ROW_NUMBER()OVER(ORDER BY 子类)))  AS nodeid 
        FROM dbo.Tree 
        WHERE 父类=@node
    )
    INSERT INTO @tbnodes(父类,子类,父类标识,nodeid) 
    SELECT * FROM nodes
    UNION
    SELECT c.父类,c.子类,c.父类标识,c.nodeid FROM nodes AS n CROSS APPLY fn_GetFullNode(n.子类,n.nodeid) AS c

    RETURN
END 

SELECT * FROM fn_GetFullNode(N'汽车',100) ORDER BY nodeid
吉普赛的歌 2017-12-30
  • 打赏
  • 举报
 回复
#6 黄泽成的方案不错。 其实总得牺牲一些东西。 一般是以空间换时间（增加冗余字段，而且需要维护）。
薛定谔的DBA 2017-12-29
  • 打赏
  • 举报
 回复
给你参考一下这种表结构设计思路。你可以把字段 【父类标识】改为【层级】，如第一层0，第二层1………




道素 2017-12-29
  • 打赏
  • 举报
 回复
你是不是希望将主子关系的排在一起呢？ 我在你的数据上又增加一层 


if not object_id(N'Tempdb..#T') is null drop table #T

Create table #T([父类] nvarchar(24),[子类] nvarchar(24),[父类标识] int)
Insert #T
select N'汽车',N'车壳',0 union all
select N'汽车',N'车轮',1 union all
select N'汽车',N'发动机',1 union all
select N'汽车',N'车载系统',1 union all
select N'车轮',N'轮毂',0 union all
select N'车轮',N'轮胎',0 union all
select N'发动机',N'活塞',0 union all
select N'发动机',N'缸体',0 union all
select N'车载系统',N'传感器',0 union all
select N'车载系统',N'电子设备',1 union ALL
select N'电子设备',N'电子设备1',0 union ALL
select N'电子设备',N'电子设备2',0 

;WITH cte AS
(
   SELECT CONVERT(NVARCHAR(24), N'汽车') AS 父类 ,CONVERT(NVARCHAR(24),N'汽车') AS 子类,CONVERT(VARCHAR(10),'0') AS ID
   UNION ALL
   SELECT t.父类,t.子类,CONVERT(VARCHAR(10),cte.ID+LTRIM(ROW_NUMBER()OVER(ORDER BY t.子类))) AS ID FROM #T AS t JOIN cte ON t.父类=cte.子类

)
SELECT * FROM cte ORDER BY ID


+------+-------+------+
| 父类   | 子类    | ID   |
+------+-------+------+
| 汽车   | 汽车    | 0    |
| 汽车   | 发动机   | 01   |
| 发动机  | 活塞    | 011  |
| 发动机  | 缸体    | 012  |
| 汽车   | 车壳    | 02   |
| 汽车   | 车轮    | 03   |
| 车轮   | 轮毂    | 031  |
| 车轮   | 轮胎    | 032  |
| 汽车   | 车载系统  | 04   |
| 车载系统 | 传感器   | 041  |
| 车载系统 | 电子设备  | 042  |
| 电子设备 | 电子设备1 | 0421 |
| 电子设备 | 电子设备2 | 0422 |
+------+-------+------+
日月路明 2017-12-29
  • 打赏
  • 举报
 回复
我还是坚持我原来的观点，如果你在实现某项要求的时候很困难，很可能是你的模型有问题，这个时候应该改变模型而不是寻求一种复杂的算法去解决问题
日月路明 2017-12-29
  • 打赏
  • 举报
 回复
我个人认为最好的解决方案是改表结构，增加路径的概念
GEshaoyebuhuaxin 2017-12-29
  • 打赏
  • 举报
 回复
引用 5 楼 ch21st 的回复:
你是不是希望将主子关系的排在一起呢？ 我在你的数据上又增加一层 


if not object_id(N'Tempdb..#T') is null drop table #T

Create table #T([父类] nvarchar(24),[子类] nvarchar(24),[父类标识] int)
Insert #T
select N'汽车',N'车壳',0 union all
select N'汽车',N'车轮',1 union all
select N'汽车',N'发动机',1 union all
select N'汽车',N'车载系统',1 union all
select N'车轮',N'轮毂',0 union all
select N'车轮',N'轮胎',0 union all
select N'发动机',N'活塞',0 union all
select N'发动机',N'缸体',0 union all
select N'车载系统',N'传感器',0 union all
select N'车载系统',N'电子设备',1 union ALL
select N'电子设备',N'电子设备1',0 union ALL
select N'电子设备',N'电子设备2',0 

;WITH cte AS
(
   SELECT CONVERT(NVARCHAR(24), N'汽车') AS 父类 ,CONVERT(NVARCHAR(24),N'汽车') AS 子类,CONVERT(VARCHAR(10),'0') AS ID
   UNION ALL
   SELECT t.父类,t.子类,CONVERT(VARCHAR(10),cte.ID+LTRIM(ROW_NUMBER()OVER(ORDER BY t.子类))) AS ID FROM #T AS t JOIN cte ON t.父类=cte.子类

)
SELECT * FROM cte ORDER BY ID


+------+-------+------+
| 父类   | 子类    | ID   |
+------+-------+------+
| 汽车   | 汽车    | 0    |
| 汽车   | 发动机   | 01   |
| 发动机  | 活塞    | 011  |
| 发动机  | 缸体    | 012  |
| 汽车   | 车壳    | 02   |
| 汽车   | 车轮    | 03   |
| 车轮   | 轮毂    | 031  |
| 车轮   | 轮胎    | 032  |
| 汽车   | 车载系统  | 04   |
| 车载系统 | 传感器   | 041  |
| 车载系统 | 电子设备  | 042  |
| 电子设备 | 电子设备1 | 0421 |
| 电子设备 | 电子设备2 | 0422 |
+------+-------+------+
总是会提示 语句被终止。完成执行语句前已用完最大递归 100。 因为数据量比较大，一个完整的结构大概在600个数据左右。 如果把最大递归改为0，无限制，那速度是慢的不行了
日月路明 2017-12-29
  • 打赏
  • 举报
 回复
该数据库表结构，增加路径的概念，也就是从根节点到当前结点的路径，例如： 父类 子类 父类标识 路径 汽车 车壳 0 汽车,车壳 汽车 车轮 1 汽车,车轮 汽车 发动机 1 汽车 车载系统 1 车轮 轮毂 0 汽车 ,车轮,轮毂 车轮 轮胎 0 。。。。。 发动机 活塞 0 发动机 缸体 0 车载系统 传感器 0 车载系统 电子设备 0 sql依赖路径即可实现你的要求
shoppo0505 2017-12-28
  • 打赏
  • 举报
 回复
SQL中用CTE的循环
RINK_1 2017-12-28
  • 打赏
  • 举报
 回复
借用#2的数据 


WITH CTE_1
AS
(select *,ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS seq_1 from #T),

CTE_2
AS
(select *,seq_1 as par_seq,1 as level
 from CTE_1 where 父类='汽车'
 union all
 select A.*,par_seq,level+1
 from CTE_1 A
 join CTE_2 B ON A.父类=B.子类)
 
 select * from 
 (select 子类,par_seq,level,ROW_NUMBER() over (partition by par_seq order by (select 1)) as seq_2 from CTE_2
  union all
  select '汽车' ,0,0,0) as A
 order by par_seq,level,seq_2
二月十六 2017-12-28
  • 打赏
  • 举报
 回复 
--测试数据

if not object_id(N'Tempdb..#T') is null

	drop table #T

Go

Create table #T([父类] nvarchar(24),[子类] nvarchar(24),[父类标识] int)

Insert #T

select N'汽车',N'车壳',0 union all

select N'汽车',N'车轮',1 union all

select N'汽车',N'发动机',1 union all

select N'汽车',N'车载系统',1 union all

select N'车轮',N'轮毂',0 union all

select N'车轮',N'轮胎',0 union all

select N'发动机',N'活塞',0 union all

select N'发动机',N'缸体',0 union all

select N'车载系统',N'传感器',0 union all

select N'车载系统',N'电子设备',0

Go

--测试数据结束

;WITH cte AS (

SELECT *,1 AS rn FROM #T WHERE 父类='汽车'

UNION ALL

SELECT #T.*,rn+1 FROM #T JOIN cte ON #T.父类=cte.子类

),cteb AS (

SELECT '汽车' AS 子类,0 AS rn

UNION

SELECT 子类,rn FROM cte 

)

SELECT 子类 FROM cteb ORDER BY rn


MySQL高级及SQL优化
①影响性能下降SQL慢，在于执行SQL时间长或者等待时间长②影响...允许表出现冗余字段减少联查（SQL优化）--没有充分利用到索引，可以通过建立索引来提高SQL执行效率--服务器调优及各个参数的设置，可以通过调整my.cnf。
【SQL性能优化】索引的原理：我们为什么用B+树来做索引？
虽然传统的二叉树数据结构查找数据的效率高，但很容易增加磁盘 I/O 操作的次数，影响索引使用的效率。因此在构造索引的时候，我们更倾向于采用“矮胖”的数据结构。B 树和 B+ 树都可以作为索引的数据结构，在 MySQL ...
Mysql慢查询Sql优化（超详细）
一句话说明，索引是排好序的快速查找B+树数据结构理解方式一：索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它 不必读取整个行。毕竟索引叶子节点存储了它们索引的数据；当能通过...
一文搞懂MySQL索引数据结构
当我们在查询数据慢的情况下第一个想到的会是添加索引，索引在数据库的实际应用场景中十分常见，数据库的优化也离不开对索引的优化。而且索引相关的知识也是面试频率非常高的点之一，因此，本文将从基础理论出发，...
Mysql数据库索引数据结构学习篇
MySQL是一种开放源代码的关系型数据库管理系统（RDBMS），使用最常用的数据库管理语言--结构化查询语言（SQL）进行数据库管理。MySQL是开放源代码的。MySQL因为其速度、可靠性和适应性而备受关注。大多数人都认为在...
应用实例

27,579

社区成员

68,558

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章