sqlserver如何实现从父级到子集的一级一级排序

时间：2020-01-03 15:21:20 收藏：0 阅读：199

已知有这样一张表，数据内容如下，其中L_ID为自增id,L_PARID为父ID该ID指向L_ID,C_ZQNAME为省市区名称，如何通过一条sql语句实现从省到市再到区的查询排序

技术分享图片

查询sql如下

WITH cte AS (
        select L_ID,L_PARID,C_ZQNAME ,CAST(C_ZQNAME AS VARCHAR(1000)) AS fullname,CAST(L_ID AS VARCHAR(1000)) AS fullid
        from T_SYS_ZQSJZD
        WHERE L_ZQJB=1 
        UNION ALL 
        SELECT a.L_ID,a.L_PARID,a.C_ZQNAME,CAST(b.fullname+‘/‘+a.C_ZQNAME AS VARCHAR(1000)) AS FULLname,
        CAST(b.fullid+‘/‘+CAST(a.L_ID as VARCHAR(1000)) AS VARCHAR(1000)) AS FULLID
        FROM T_SYS_ZQSJZD a INNER JOIN cte b ON a.L_PARID=b.L_ID
    )
    SELECT * FROM cte

查询结果如下图

技术分享图片

该查询最好做成一个sql函数，创建函数方法如下

CREATE FUNCTION [dbo].[RootOrgId]
(
) 
RETURNS TABLE 
AS
RETURN 
(
    WITH cte AS (
        select L_ID,L_PARID,C_ZQNAME ,CAST(C_ZQNAME AS VARCHAR(1000)) AS fullname,CAST(L_ID AS VARCHAR(1000)) AS fullid
        from T_SYS_ZQSJZD
        WHERE L_ZQJB=1 
        UNION ALL 
        SELECT a.L_ID,a.L_PARID,a.C_ZQNAME,CAST(b.fullname+‘/‘+a.C_ZQNAME AS VARCHAR(1000)) AS FULLname,
        CAST(b.fullid+‘/‘+CAST(a.L_ID as VARCHAR(1000)) AS VARCHAR(1000)) AS FULLID
        FROM T_SYS_ZQSJZD a INNER JOIN cte b ON a.L_PARID=b.L_ID
    )
    SELECT * FROM cte
)

这样在sqlserver种就可以直接通过select * from RootOrgId()这样的查询方法实现查询了

原文：https://www.cnblogs.com/dushaojun/p/12145056.html