已知有这样一张表,数据内容如下,其中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()这样的查询方法实现查询了