SQL父项子项查询,该怎么解决

SQL父项子项查询

SQL code

if object_id('[tb]') is not null drop table [tb]create table [tb] (de01 varchar(20),Expr1 varchar(60),de02 varchar(20),Expr2 varchar(60),de03 float,de04 float,da24 varchar(6))insert into [tb]select '1L334-ACS-0101','334小包用黑色包邊帶標准裁切','2Z-334BBD-0101','334小包用黑色包邊帶標准裁切',1,1,'個' union allselect '1L334-ACS-0101','334小包內裡用牛津布標准裁切','2Z-334NJB-0101','334小包內裡用牛津布標准裁切',2,1,'個' union allselect '1L334-ACS-0101','334小包用納帕PU皮標准裁切空軍藍','2Z-334PT-0100','334小包用納帕PU皮標准裁切空軍藍',2,1,'個' union allselect '1L334-ACS-0101','0.25mm透明PVC膠片 48"','3P-PVC-2401','0.25mm透明PVC膠片',0.004,1,'碼' union allselect '1L334-ACS-0101','車線 灰色 2股40# 8309#','3Z-CX-0601','車線 灰色 2股40# 8309#',1.8,1,'米' union allselect '2Z-334BBD-0101','黑色尼龍包邊帶 好 41# 偏硬','3Z-BBD-0101','黑色尼龍包邊帶 好 41# 偏硬',0.33,1,'碼' union allselect '1L334-ACS-0101','黑色1"人字紋3#拉鏈布 400碼/卷581A','3Z-LLB-0101','黑色1"人字紋3#拉鏈布',0.15,1,'碼' union allselect '1L334-ACS-0101','白叻色拉鏈夾','3Z-LLJ-0401','白叻色拉鏈夾',1,1,'個' union allselect '1L334-ACS-0101','3#黑色烤漆短排拉鏈頭','3Z-LLT-0101','3#黑色烤漆短排拉鏈頭',1,1,'個' union allselect '2Z-334NJB-0101','牛津布 黑色150D 55','3Z-NJB-0101','牛津布 黑色150D',0.017,2,'碼' union allselect '1L334-ACS-0101','103粉膠 皮套粘合用','6Z-103FJ-01','103粉膠 皮套粘合用',1,1,'克' union allselect '1L334-ACS-0101','477號藥水膠 皮套粘合用','6Z-477JS-01','477號藥水膠 皮套粘合用',1.5,1,'克' union allgo

这个table的de01是父项料号，de02是子项料号，例如父项'1L334-ACS-0101'下有子项'2Z-334NJB-0101'，然后这个子项又作为一个父项目，他下面又有‘3Z-NJB-0101'这个子项，这是BOM的结构表，我如果要查询这个物料号为项'1L334-ACS-0101'的子项孙子项..这个语句该如何写呢？

[解决办法]

SQL code

USE tempdbGO-- 建立演示环境CREATE TABLE Dept( id int PRIMARY KEY,  parent_id int, name nvarchar(20))INSERT DeptSELECT 0, 0, N'<全部>' UNION ALLSELECT 1, 0, N'财务部' UNION ALLSELECT 2, 0, N'行政部' UNION ALLSELECT 3, 0, N'业务部' UNION ALLSELECT 4, 0, N'业务部' UNION ALLSELECT 5, 4, N'销售部' UNION ALLSELECT 6, 4, N'MIS' UNION ALLSELECT 7, 6, N'UI' UNION ALLSELECT 8, 6, N'软件开发' UNION ALLSELECT 9, 8, N'内部开发'GO-- 查询指定部门下面的所有部门DECLARE @Dept_name nvarchar(20)SET @Dept_name = N'MIS';WITHDEPTS AS( -- 定位点成员 SELECT * FROM Dept WHERE name = @Dept_name UNION ALL -- 递归成员, 通过引用CTE自身与Dept基表JOIN实现递归 SELECT A.* FROM Dept A, DEPTS B WHERE A.parent_id = B.id)SELECT * FROM DEPTSGO-- 删除演示环境DROP TABLE Dept----CTE的综合应用USE tempdbGO-- 建立演示环境CREATE TABLE Dept( id int PRIMARY KEY,  parent_id int, name nvarchar(20))INSERT DeptSELECT 0, 0, N'<全部>' UNION ALLSELECT 1, 0, N'财务部' UNION ALLSELECT 2, 0, N'行政部' UNION ALLSELECT 3, 0, N'业务部' UNION ALLSELECT 4, 0, N'业务部' UNION ALLSELECT 5, 4, N'销售部' UNION ALLSELECT 6, 4, N'MIS' UNION ALLSELECT 7, 6, N'UI' UNION ALLSELECT 8, 6, N'软件开发' UNION ALLSELECT 9, 8, N'内部开发'GO-- 查询指定部门下面的所有部门, 并汇总各部门的下级部门数DECLARE @Dept_name nvarchar(20)SET @Dept_name = N'MIS';WITHDEPTS AS(   -- 查询指定部门及其下的所有子部门 -- 定位点成员 SELECT * FROM Dept WHERE name = @Dept_name UNION ALL -- 递归成员, 通过引用CTE自身与Dept基表JOIN实现递归 SELECT A.* FROM Dept A, DEPTS B WHERE A.parent_id = B.id),DEPTCHILD AS(  -- 引用第1个CTE,查询其每条记录对应的部门下的所有子部门 SELECT   Dept_id = P.id, C.id, C.parent_id FROM DEPTS P, Dept C WHERE P.id = C.parent_id UNION ALL SELECT   P.Dept_id, C.id, C.parent_id FROM DEPTCHILD P, Dept C WHERE P.id = C.parent_id),DEPTCHILDCNT AS( -- 引用第2个CTE, 汇总得到各部门下的子部门数 SELECT   Dept_id, Cnt = COUNT(*) FROM DEPTCHILD GROUP BY Dept_id)SELECT    -- JOIN第1,3个CTE,得到最终的查询结果 D.*, ChildDeptCount = ISNULL(DS.Cnt, 0)FROM DEPTS D LEFT JOIN DEPTCHILDCNT DS  ON D.id = DS.Dept_idGO-- 删除演示环境DROP TABLE Dept类似问题，修改就行
[解决办法]
http://topic.csdn.net/u/20120312/16/86531a4c-fe5a-4e72-8d66-26903cc8aefe.html?95605

SQL code
/*标题：SQL SERVER 2000中查询指定节点及其所有子节点的函数(表格形式显示)作者：爱新觉罗·毓华(十八年风雨,守得冰山雪莲花开) 时间：2008-05-12地点：广东深圳*/create table tb(id varchar(3) , pid varchar(3) , name varchar(10))insert into tb values('001' , null  , '广东省')insert into tb values('002' , '001' , '广州市')insert into tb values('003' , '001' , '深圳市')insert into tb values('004' , '002' , '天河区')insert into tb values('005' , '003' , '罗湖区')insert into tb values('006' , '003' , '福田区')insert into tb values('007' , '003' , '宝安区')insert into tb values('008' , '007' , '西乡镇')insert into tb values('009' , '007' , '龙华镇')insert into tb values('010' , '007' , '松岗镇')go--查询指定节点及其所有子节点的函数create function f_cid(@ID varchar(3)) returns @t_level table(id varchar(3) , level int)asbegin  declare @level int  set @level = 1  insert into @t_level select @id , @level  while @@ROWCOUNT > 0  begin    set @level = @level + 1    insert into @t_level select a.id , @level    from tb a , @t_Level b    where a.pid = b.id and b.level = @level - 1  end  returnendgo--调用函数查询001(广东省)及其所有子节点select a.* from tb a , f_cid('001') b where a.id = b.id order by a.id/*id   pid  name       ---- ---- ---------- 001  NULL 广东省002  001  广州市003  001  深圳市004  002  天河区005  003  罗湖区006  003  福田区007  003  宝安区008  007  西乡镇009  007  龙华镇010  007  松岗镇（所影响的行数为 10 行）*/--调用函数查询002(广州市)及其所有子节点select a.* from tb a , f_cid('002') b where a.id = b.id order by a.id/*id   pid  name       ---- ---- ---------- 002  001  广州市004  002  天河区（所影响的行数为 2 行）*/--调用函数查询003(深圳市)及其所有子节点select a.* from tb a , f_cid('003') b where a.id = b.id order by a.id/*id   pid  name       ---- ---- ---------- 003  001  深圳市005  003  罗湖区006  003  福田区007  003  宝安区008  007  西乡镇009  007  龙华镇010  007  松岗镇（所影响的行数为 7 行）*/drop table tbdrop function f_cid 
 
[解决办法]
SQL code
if object_id('[tb]') is not null drop table [tb]create table [tb] (de01 varchar(20),Expr1 varchar(60),de02 varchar(20),Expr2 varchar(60),de03 float,de04 float,da24 varchar(6))insert into [tb]select '1L334-ACS-0101','334小包用黑色包邊帶標准裁切','2Z-334BBD-0101','334小包用黑色包邊帶標准裁切',1,1,'個' union allselect '1L334-ACS-0101','334小包內裡用牛津布標准裁切','2Z-334NJB-0101','334小包內裡用牛津布標准裁切',2,1,'個' union allselect '1L334-ACS-0101','334小包用納帕PU皮標准裁切空軍藍','2Z-334PT-0100','334小包用納帕PU皮標准裁切空軍藍',2,1,'個' union allselect '1L334-ACS-0101','0.25mm透明PVC膠片 48"','3P-PVC-2401','0.25mm透明PVC膠片',0.004,1,'碼' union allselect '1L334-ACS-0101','車線 灰色 2股40# 8309#','3Z-CX-0601','車線 灰色 2股40# 8309#',1.8,1,'米' union allselect '2Z-334BBD-0101','黑色尼龍包邊帶 好 41# 偏硬','3Z-BBD-0101','黑色尼龍包邊帶 好 41# 偏硬',0.33,1,'碼' union allselect '1L334-ACS-0101','黑色1"人字紋3#拉鏈布 400碼/卷581A','3Z-LLB-0101','黑色1"人字紋3#拉鏈布',0.15,1,'碼' union allselect '1L334-ACS-0101','白叻色拉鏈夾','3Z-LLJ-0401','白叻色拉鏈夾',1,1,'個' union allselect '1L334-ACS-0101','3#黑色烤漆短排拉鏈頭','3Z-LLT-0101','3#黑色烤漆短排拉鏈頭',1,1,'個' union allselect '2Z-334NJB-0101','牛津布 黑色150D 55','3Z-NJB-0101','牛津布 黑色150D',0.017,2,'碼' union allselect '1L334-ACS-0101','103粉膠 皮套粘合用','6Z-103FJ-01','103粉膠 皮套粘合用',1,1,'克' union allselect '1L334-ACS-0101','477號藥水膠 皮套粘合用','6Z-477JS-01','477號藥水膠 皮套粘合用',1.5,1,'克'select * from tb;WITHt AS( -- 定位点成员 SELECT * FROM [tb] WHERE de01='1L334-ACS-0101' UNION ALL -- 递归成员, 通过引用CTE自身与Dept基表JOIN实现递归 SELECT A.* FROM tb A, t B WHERE A.de01 = B.de02)SELECT * FROM t/*de01    Expr1    de02    Expr2    de03    de04    da241L334-ACS-0101    334小包用黑色包邊帶標准裁切    2Z-334BBD-0101    334小包用黑色包邊帶標准裁切    1    1    個1L334-ACS-0101    334小包內裡用牛津布標准裁切    2Z-334NJB-0101    334小包內裡用牛津布標准裁切    2    1    個1L334-ACS-0101    334小包用納帕PU皮標准裁切空軍藍    2Z-334PT-0100    334小包用納帕PU皮標准裁切空軍藍    2    1    個1L334-ACS-0101    0.25mm透明PVC膠片 48"    3P-PVC-2401    0.25mm透明PVC膠片    0.004    1    碼1L334-ACS-0101    車線 灰色 2股40# 8309#    3Z-CX-0601    車線 灰色 2股40# 8309#    1.8    1    米1L334-ACS-0101    黑色1"人字紋3#拉鏈布 400碼/卷581A    3Z-LLB-0101    黑色1"人字紋3#拉鏈布    0.15    1    碼1L334-ACS-0101    白叻色拉鏈夾    3Z-LLJ-0401    白叻色拉鏈夾    1    1    個1L334-ACS-0101    3#黑色烤漆短排拉鏈頭    3Z-LLT-0101    3#黑色烤漆短排拉鏈頭    1    1    個1L334-ACS-0101    103粉膠 皮套粘合用    6Z-103FJ-01    103粉膠 皮套粘合用    1    1    克1L334-ACS-0101    477號藥水膠 皮套粘合用    6Z-477JS-01    477號藥水膠 皮套粘合用    1.5    1    克2Z-334NJB-0101    牛津布 黑色150D 55    3Z-NJB-0101    牛津布 黑色150D    0.017    2    碼2Z-334BBD-0101    黑色尼龍包邊帶 好 41# 偏硬    3Z-BBD-0101    黑色尼龍包邊帶 好 41# 偏硬    0.33    1    碼*/ 

 

查看更多 下一篇