有关字符串截取的和子父节点的查询

最新推荐文章于 2022-06-12 18:16:21 发布

wyyEileen

最新推荐文章于 2022-06-12 18:16:21 发布

阅读量393

点赞数

分类专栏： MSsql数据库文章标签： null join table

本文链接：https://blog.csdn.net/wyyEileen/article/details/7044533

版权

MSsql数据库专栏收录该内容

8 篇文章 0 订阅

订阅专栏

-- 创建地区表
CREATE TABLE dbo.Districts
(
DistrictID     INT       NOT NULL,                         /*地区编号*/
Name        NVARCHAR(255)  NOT NULL,       /*地区名称*/
RootDistrictID   INT                                            /*根级地区编号*/
);
GO

---地区查询

DECLARE @DistrictList varchar(8000)='',@DistrictID int
set @DistrictID=140000
      ; WITH A AS
      (
      SELECT DistrictID,RootDistrictID
      FROM dbo.Districts D WHERE DistrictID = @DistrictID
      UNION ALL
      SELECT d.DistrictID,D.RootDistrictID
      FROM dbo.Districts D JOIN A ON D.RootDistrictID = A.DistrictID
      WHERE D.RootDistrictID IS NOT NULL
      )
      --select * from A;
      SELECT @DistrictList = @DistrictList+CAST ( DistrictID AS VARCHAR)+',' FROM A;
      select @DistrictList
      SET @DistrictList = LEFT(@DistrictList,LEN(@DistrictList)-1) ---Left 截取= SUBSTRING 截取
      --(SET @DistrictList = SUBSTRING(@DistrictList, 0,LEN(@DistrictList)-1))
      ---这一步结果:140000,

      select @DistrictList

     --- 结果:140000