moferferi
شنبه 11 خرداد 1398, 13:56 عصر
با سلام.
من یه جدول برای دسته بندی دارم
CREATE TABLE [dbo].[AnbarCategory](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Title] [nvarchar](500) NOT NULL,
[ParentID] [int] NULL,
CONSTRAINT [PK_AnbarCategory] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
و با کد زیر تمامی parent های هر نود را نشون میدم
WITH departmentcte(deptid, department, ParentID, LEVEL, treepath) AS
( SELECT id AS deptid, Title, ParentID, 0 AS LEVEL,
CAST(Title AS nVARCHAR(1024)) AS treepath
FROM AnbarCategory
WHERE ParentID IS NULL
UNION ALL
SELECT d.id AS deptid, d.Title, d.ParentID,
departmentcte.LEVEL + 1 AS LEVEL,
CAST(departmentcte.treepath + ' -> ' +
CAST(d.Title AS nVARCHAR(1024))
AS nVARCHAR(1024)) AS treepath
FROM AnbarCategory d
INNER JOIN departmentcte
ON departmentcte.deptid = d.ParentID
)
SELECT *
FROM departmentcte
ORDER BY treepath;
ولی این کار وقتی جواب میده که بخوام همه نود ها را با parent هاش نشون بدم.
150286
واگه بخوام فقط یک نود (مثلا deptid 13 )را توی یک ردیف با parent هاش نشون بدم کار نمیده و خروجی زیر را داره
150287
من یه جدول برای دسته بندی دارم
CREATE TABLE [dbo].[AnbarCategory](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Title] [nvarchar](500) NOT NULL,
[ParentID] [int] NULL,
CONSTRAINT [PK_AnbarCategory] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
و با کد زیر تمامی parent های هر نود را نشون میدم
WITH departmentcte(deptid, department, ParentID, LEVEL, treepath) AS
( SELECT id AS deptid, Title, ParentID, 0 AS LEVEL,
CAST(Title AS nVARCHAR(1024)) AS treepath
FROM AnbarCategory
WHERE ParentID IS NULL
UNION ALL
SELECT d.id AS deptid, d.Title, d.ParentID,
departmentcte.LEVEL + 1 AS LEVEL,
CAST(departmentcte.treepath + ' -> ' +
CAST(d.Title AS nVARCHAR(1024))
AS nVARCHAR(1024)) AS treepath
FROM AnbarCategory d
INNER JOIN departmentcte
ON departmentcte.deptid = d.ParentID
)
SELECT *
FROM departmentcte
ORDER BY treepath;
ولی این کار وقتی جواب میده که بخوام همه نود ها را با parent هاش نشون بدم.
150286
واگه بخوام فقط یک نود (مثلا deptid 13 )را توی یک ردیف با parent هاش نشون بدم کار نمیده و خروجی زیر را داره
150287