使用Sql Query绑定树视图 [英] Bind Tree view with Sql Query

问题描述

类别表



Id |姓名



1 | MCA



2 | MBA





课程表



Id |名称| CategoryId | ParentId



1 | Asp.net | 1 | 0

2 | C＃| 1 | 1

3 |财务| 2 | 0









所以我想要sql查询显示输出像



Id名称ParentId



1 MCA 0

2 MBA 0

3 Asp.net 1

4 C＃3

5财务2

Category Table

Id | Name

1 | MCA

2 | MBA


Course Table

Id | Name | CategoryId | ParentId

1 | Asp.net | 1 | 0
2 | C# | 1 | 1
3 | Finance | 2 | 0




so i want sql query for display output like

Id Name ParentId

1 MCA 0
2 MBA 0
3 Asp.net 1
4 C# 3
5 Finance 2

推荐答案

DECLARE @category AS TABLE (catid INT, Catname VARCHAR(20))
INSERT INTO @category
VALUES(1,'MCA'),(2,'MBA')
DECLARE @Course AS TABLE (ID INT,  coursename VARCHAR(20), catid INT, Parentid INT)
INSERT INTO @Course
VALUES
(1,'Asp.NET',1,0),
(2,'C#',1,1),
(3,'Finance',2,0)
;
WITH cte (id, Name, ParentID, catid ) AS
(
    SELECT c.catid, c.Catname, 0 ,0
    FROM @category c
    UNION ALL
    SELECT cu.ID,cu.coursename, cu.Parentid, cu.catid
    FROM @Course cu INNER JOIN @category ca ON ca.catid = cu.catid
), Res AS (
SELECT ROW_NUMBER() OVER (ORDER BY catid,id) AS rn, *
FROM cte
)
SELECT r1.rn AS id, NAME,
CASE
 WHEN r1.catid =0 AND r1.Parentid =0 THEN 0
 ELSE (SELECT r.rn FROM res r WHERE r.id = (CASE WHEN r1.parentid<>0 THEN r1.Parentid ELSE r1.catid END) AND r.catid =(CASE WHEN r1.parentid<>0 THEN r1.catid ELSE 0 END))
END as Parentid
FROM res r1

不确定这是你期望的

not sure is this what you expect

WITH  CourseCTE
 AS
 (
 SELECT   CourseId AS Id , Name AS NAME , ParentId AS ParentId ,
( CAST(CategoryId AS VARCHAR(MAX)) + ',' + CAST(courseId AS VARCHAR(MAX)) ) AS PathId 
FROM   dbo.Course WHERE    ParentId IS NULL UNION ALL SELECT   c.CourseId AS Id , c.Name AS Name , c.ParentId AS ParentId ,  CAST(( cte.PathId + ',' + CAST(c.CategoryId AS VARCHAR(MAX)) ) AS VARCHAR(MAX)) AS PathId FROM     dbo.Course AS c INNER JOIN CourseCTE cte ON cte.Id = c.ParentId

)
 SELECT  Id, Name, ParentId, PathId FROM    CourseCTE UNION SELECT  CategoryId AS Id , Name AS NAME , 0 AS ParentId , CAST(CategoryId AS VARCHAR(MAX)) AS PathId FROM     dbo.Category  ORDER BY PathId

运行此命令并检查结果格式：

Run this and check the Result Format:

Create table ##Catagory(id int identity(1,1),course nvarchar(max))


Create table ##Group(id int identity(1,1),course nvarchar(max),catagory_id int,parent_id int)

 
 insert into ##Catagory(course) values('MCA')
 insert into ##Catagory(course) values('MBA')
  insert into ##Catagory(course) values('B.Tech')
  
 insert into ##group(course,catagory_id,parent_id) values('ASP.net',1,0)
 insert into ##group(course,catagory_id,parent_id) values('C#',1,0)
 insert into ##group(course,catagory_id,parent_id) values('Finance',2,0)
  insert into ##group(course,catagory_id,parent_id) values('Java',2,0)
   insert into ##group(course,catagory_id,parent_id) values('Ajax',1,0)
   insert into ##group(course,catagory_id,parent_id) values('Jquery',3,0)

查询

Query

select  course ,(0)as parent_id from ##catagory
union all
select  course ,catagory_id as parent_id from ##group

这篇关于使用Sql Query绑定树视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！