如何用T-SQL、SSMS或Python展示SQL Server hierarchyid数据树视图？

针对你的需求，这里提供几种快速实现层级展示的方法，涵盖SSMS直接查询、存储过程封装和Python可视化界面，满足给客户演示的需求：

一、SSMS直接生成树状缩进展示 #

这是最快的演示方式，无需额外工具，直接通过SQL语句将hierarchyid转换为直观的树状结构。假设你的表结构为：

CREATE TABLE OrgTree (
    NodeID hierarchyid PRIMARY KEY,
    NodeName NVARCHAR(100) NOT NULL
);

执行以下查询语句即可得到带树状符号的层级展示：

WITH HierarchyCTE AS (
    SELECT 
        NodeID,
        NodeName,
        NodeID.GetLevel() AS Level,
        NodeID.ToString() AS NodePath
    FROM OrgTree
)
SELECT
    CASE 
        WHEN Level = 0 THEN NodeName
        ELSE REPLICATE('    ', Level - 1) + 
             CASE WHEN EXISTS (SELECT 1 FROM HierarchyCTE h2 WHERE h2.NodePath LIKE h1.NodePath + '[0-9]/%') THEN '├─ ' ELSE '└─ ' END + NodeName
    END AS 树状视图,
    NodePath AS 节点路径,
    Level AS 层级
FROM HierarchyCTE h1
ORDER BY NodePath;

• 逻辑说明：利用GetLevel()获取节点层级，通过REPLICATE生成缩进空格；根据节点是否存在子节点，使用├─（有子节点）或└─（无子节点）区分，形成树状视觉效果。

二、存储过程封装（便于重复调用） #

如果需要频繁演示，可将上述逻辑封装为存储过程，调用更便捷：

CREATE PROCEDURE dbo.ShowHierarchyTree
AS
BEGIN
    SET NOCOUNT ON;
    WITH HierarchyCTE AS (
        SELECT 
            NodeID,
            NodeName,
            NodeID.GetLevel() AS Level,
            NodeID.ToString() AS NodePath
        FROM OrgTree
    )
    SELECT
        CASE 
            WHEN Level = 0 THEN NodeName
            ELSE REPLICATE('    ', Level - 1) + 
                 CASE WHEN EXISTS (SELECT 1 FROM HierarchyCTE h2 WHERE h2.NodePath LIKE h1.NodePath + '[0-9]/%') THEN '├─ ' ELSE '└─ ' END + NodeName
        END AS 树状视图,
        NodePath AS 节点路径,
        Level AS 层级
    FROM HierarchyCTE h1
    ORDER BY NodePath;
END;

调用方式：

EXEC dbo.ShowHierarchyTree;

三、Python 可视化交互界面（带基础增删操作） #

如果需要更直观的交互演示（支持增删节点），可以用Python快速搭建一个GUI界面，结合pyodbc连接SQL Server：

步骤1：安装依赖 #

pip install pyodbc

（tkinter通常随Python自带，无需额外安装）

步骤2：演示代码 #

import pyodbc
import tkinter as tk
from tkinter import ttk, messagebox, simpledialog

# 配置SQL Server连接信息，替换为你的实际参数
DB_CONFIG = {
    "driver": "{ODBC Driver 17 for SQL Server}",
    "server": "你的服务器地址",
    "database": "你的数据库名",
    "uid": "用户名",
    "pwd": "密码"
}

# 初始化数据库连接
def init_db():
    conn_str = ";".join([f"{k}={v}" for k, v in DB_CONFIG.items()])
    return pyodbc.connect(conn_str)

conn = init_db()
cursor = conn.cursor()

# 加载层级数据
def load_tree_data():
    cursor.execute("""
        WITH HierarchyCTE AS (
            SELECT 
                NodeID.ToString() AS NodePath,
                NodeName,
                NodeID.GetLevel() AS Level
            FROM OrgTree
        )
        SELECT NodePath, NodeName, Level FROM HierarchyCTE ORDER BY NodePath
    """)
    return cursor.fetchall()

# 更新树控件内容
def refresh_tree(tree):
    # 清空现有节点
    for item in tree.get_children():
        tree.delete(item)
    # 插入新数据
    data = load_tree_data()
    parent_map = {"/": ""}  # 顶级节点父ID为空
    for node_path, node_name, level in data:
        # 计算父节点路径
        if level > 0:
            parent_path = node_path.rsplit('/', 2)[0] + '/'
        else:
            parent_path = "/"
        # 插入节点到树中
        tree.insert(parent_map[parent_path], 'end', text=node_name, values=(node_path))
        parent_map[node_path] = node_path

# 添加子节点
def add_child_node(tree):
    selected_item = tree.selection()
    if not selected_item:
        messagebox.showwarning("提示", "请先选择父节点")
        return
    parent_path = tree.item(selected_item)['values'][0]
    node_name = simpledialog.askstring("输入", "请输入节点名称：")
    if not node_name:
        return
    
    # 获取父节点的hierarchyid
    cursor.execute("SELECT NodeID FROM OrgTree WHERE NodeID.ToString() = ?", parent_path)
    parent_id = cursor.fetchone()[0]
    
    # 生成子节点的编号（父节点下最大子节点编号+1）
    cursor.execute("""
        SELECT ISNULL(MAX(CAST(NodeID.GetDescendant(NULL, NULL).ToString().replace('/', '') AS INT)), 0) + 1
        FROM OrgTree
        WHERE NodeID.IsDescendantOf(?) = 1 AND NodeID.GetLevel() = ?
    """, (parent_id, parent_id.GetLevel() + 1))
    child_num = cursor.fetchone()[0]
    
    # 构造新节点的hierarchyid字符串
    new_node_path = parent_path.rstrip('/') + f"/{child_num}/"
    # 插入数据
    cursor.execute("INSERT INTO OrgTree (NodeID, NodeName) VALUES (hierarchyid::Parse(?), ?)", (new_node_path, node_name))
    conn.commit()
    refresh_tree(tree)

# 删除节点（仅允许删除无子节点的节点）
def delete_selected_node(tree):
    selected_item = tree.selection()
    if not selected_item:
        messagebox.showwarning("提示", "请先选择要删除的节点")
        return
    node_path = tree.item(selected_item)['values'][0]
    
    # 检查是否存在子节点
    cursor.execute("""
        SELECT COUNT(*) 
        FROM OrgTree 
        WHERE NodeID.IsDescendantOf(hierarchyid::Parse(?)) = 1 
          AND NodeID.ToString() != ?
    """, (node_path, node_path))
    child_count = cursor.fetchone()[0]
    if child_count > 0:
        messagebox.showerror("错误", "该节点存在子节点，无法删除")
        return
    
    # 删除节点
    cursor.execute("DELETE FROM OrgTree WHERE NodeID.ToString() = ?", node_path)
    conn.commit()
    refresh_tree(tree)

# 创建GUI窗口
if __name__ == "__main__":
    root = tk.Tk()
    root.title("层级树演示系统")
    root.geometry("600x450")
    
    # 树状视图控件
    tree_view = ttk.Treeview(root)
    tree_view.pack(fill=tk.BOTH, expand=True, padx=10, pady=10)
    
    # 操作按钮栏
    btn_frame = ttk.Frame(root)
    btn_frame.pack(fill=tk.X, padx=10, pady=5)
    
    add_btn = ttk.Button(btn_frame, text="添加子节点", command=lambda: add_child_node(tree_view))
    add_btn.pack(side=tk.LEFT, padx=5)
    
    delete_btn = ttk.Button(btn_frame, text="删除节点", command=lambda: delete_selected_node(tree_view))
    delete_btn.pack(side=tk.LEFT, padx=5)
    
    # 初始加载数据
    refresh_tree(tree_view)
    
    root.mainloop()
    
    # 关闭数据库连接
    conn.close()

• 使用说明：替换代码中的数据库连接参数，运行后会弹出一个窗口，展示树状结构，支持选择父节点添加子节点、删除无子节点的节点，适合给客户做交互演示。

内容的提问来源于stack exchange，提问作者Ronald Jetson