关于产品，分类，品牌之间的查询！！！急

Question

产品属于某个品牌，同时也属于产品分类的叶子分类。  产品分类有上下级关系。是一个树。现在要查询某个品牌下的产品的分类树结构 ？有啥好方法没有，Product 表ProductID    BrandID    CategoryID
Category表CategoryID  ParentID      
1            0
2            0
3            1
4            3现在比如要查找BrandID= 3的产品的分类的树结构

Answer 1

/*
Micsosoft Windows 7.0 7600
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 
小弟愿和大家共同进步、共同学习！
    如有雷同、实属巧合
●●●●●2009-09-28 18:48:04.267●●●●●
★★★★★soft_wsx★★★★★
*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[zzjg]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
drop table [dbo].[zzjg] 
GO CREATE TABLE [dbo].[zzjg] ( 
[ZzjgID] [int]  NOT NULL , 
[ParentID] [int] NULL , 
[LayID] [int] NULL , 
[orderpx] [int] NULL , 
[Zg_name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY] 
GO insert into [dbo].[zzjg]
select 
277, 0, 1, 0,   '山西省'  union all select 
292, 277, 2, 2, '大同市' union all select 
293, 277, 2, 6, '朔州市' union all select 
294, 277, 2, 4, '长治市' union all select 
295, 277, 2, 7, '忻州市' union all select   
296, 277, 2, 11, '运城市' union all select 
307, 294, 3, 0, '城区' union all select 
308, 292, 3, 0, '太谷县' union all select   --手工改
309, 292, 3, 0, '祁县' union all select 
310, 294, 3, 0, '郊区' union all select 
311, 303, 3, 0, '平遥' if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Jg_renyuan]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
drop table [dbo].[Jg_renyuan] 
GO CREATE TABLE [dbo].[Jg_renyuan] ( 
[Ry_id] [int] NOT NULL , 
[ZzjgID] [int] NULL , 
[Ry_name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , 
) ON [PRIMARY] 
GO 
insert [dbo].[Jg_renyuan]    
select7, 277, '王栋'  union all select 
8, 277, '李丽'  union all select 
9 ,277, '姚小琴'  union all select 
10 ,305, '赵建文'  union all select 
11 ,296, '曹波'  union all select 
12 ,295, '冯学飞'  union all select 
13 ,296, '马小军'  union all select 
14 ,305, '张龙富'  union all select 
15 ,305, '张小苇'  union all select 
16 ,381, '赵峰'  union all select 
17 ,381, '崔国彬'  union all select 
18 ,381, '张旭东'  union all select 
19 ,381, '郭永平'  union all select 
20, 277, '王栋'  union all select 
21, 277, '李丽'  union all select 
22 ,277, '姚小琴'  union all select 
23 ,305, '赵建文'  union all select 
24 ,307, '曹波'  union all select 
25 ,307, '冯学飞'  union all select 
26 ,308, '马小军'  union all select 
27 ,308, '张龙富'  union all select 
28 ,308, '张小苇'  union all select 
29 ,309, '赵峰'  union all select 
30 ,310, '崔国彬'  union all select 
31 ,310, '张旭东'  union all select 
32 ,311, '郭永平'  drop table #aselect a.*,b.score  into #a from zzjg a 
  full join  
    (select zzjgid,COUNT(1) as score from Jg_renyuan
      group by zzjgid)b
   on a.ZzjgID=b.zzjgid
  
  declare @level_tt table([ZzjgID] nvarchar(1000),[ParentID] nvarchar(1000),level int)
  declare @level int
  set @level=0
  insert @level_tt([ZzjgID],[ParentID],level)
  select [ZzjgID],[ZzjgID],@level from [dbo].[zzjg] where [ParentID]=0
  while @@ROWCOUNT>0
  begin 
          set @level=@level+1
          insert @level_tt([ZzjgID],[ParentID],level)
          select a.[ZzjgID],cast(b.[ParentID] as nvarchar)+cast(a.[ZzjgID] as nvarchar),@level
            from [dbo].[zzjg] a,@level_tt b
            where a.[ParentID]=b.[ZzjgID] and b.level=@level-1
 end   
select space(b.level*4)+'-->>'+a.[Zg_name] as 层次排序结果,a.zzjgid,a.parentID,SUM(isnull(c.score,0)) as socre
  from #a a,
       @level_tt b,
       #a c,
       @level_tt d
  where a.ZzjgID=b.ZzjgID and c.ZzjgID=d.ZzjgID
        and d.parentID like b.parentID+'%'
  group by a.ZzjgID,a.parentID,b.level,a.zg_name,b.parentID
  order by b.parentID
 select space(b.level*4)+'-->>'+a.[Zg_name] as 层次排序结果,a.ZzjgID,a.ParentID,a.LayID,b.level
  from [dbo].[zzjg] a,@level_tt b
  where a.[ZzjgID]=b.[ZzjgID]
  order by b.[ParentID]
  
/*
层次排序结果 zzjgid parentID socre
-->>山西省 277 0 17
    -->>大同市 292 277 4
        -->>太谷县 308 303 3
        -->>祁县 309 303 1
    -->>朔州市 293 277 0
    -->>长治市 294 277 4
        -->>城区 307 294 2
        -->>郊区 310 294 2
    -->>忻州市 295 277 1
    -->>运城市 296 277 2
 */ 
  是不是像这样的！

Answer 2

ProductID    BrandID    CategoryID 
1             3            4
2             3            4
3             4            5
CategoryID  ParentID      
1            0 
2            0 
3            1 
4            3 
5            2现在查出一级产品分类，即ParentID=0的子分类，但是一个大前提的限定条件就是这个子分类的叶子分类必须有品牌BrandID=3的产品

Answer 3

DECLARE @Dept_name nvarchar(20)
SET @ParentID = 3
;WITH
f AS(
 -- 定位点成员
 SELECT * FROM f
 WHERE name = @Dept_name
 UNION ALL
 SELECT A.*
 FROM f A, Category c
 WHERE A.CategoryID = c.ParentID
)
SELECT * FROM Product p  ,Category c  where p.ProductID=c.ProductID
 

Answer 4

---修改下，2005上用CTE
DECLARE @Dept_name nvarchar(20)
SET @ParentID = 3
;WITH
f AS(
 -- 定位点成员
 SELECT * FROM f
 WHERE ParentID = @Dept_name
 UNION ALL
 SELECT A.*
 FROM f A, Category c
 WHERE A.CategoryID = c.ParentID
)
SELECT * FROM Product p  ,Category c  where p.ProductID=c.ProductID

Answer 5

---修改下，2005上用CTE 
DECLARE @Dept_name nvarchar(20) 
SET @ParentID = 3 
;WITH 
f AS( 
-- 定位点成员 
SELECT * FROM f 
WHERE ParentID = @Dept_name 
UNION ALL 
SELECT A.* 
FROM f A, Category c 
WHERE A.CategoryID = c.ParentID 
) 
SELECT * FROM Product p  ,Category c  where p.ProductID=c.ProductID具体参考：
http://blog.csdn.net/fredrickhu/archive/2009/09/19/4569529.aspx

Answer 6

ProductID    BrandID    CategoryID 
1            3            4 
2            3            4 
3            4            5 
CategoryID  ParentID      
1            0 
2            0 
3            1 
4            3 
5            2 现在查出一级产品分类，即ParentID=0的子分类，但是一个大前提的限定条件就是这个子分类的叶子分类必须有品牌BrandID=3的产品结果就是 条件为ParentID=0,BrandID=3时，列出来的产品分类是  CagtegoryID=1的数据
因为CategoryID=2的叶子分类是5,他的产品列表里面没有BrandID=3的产品

Answer 7

能够用CTE实现产品分类的树查询，现在的条件是要这个产品分类的树查询，要在这个品牌的前提下，也就是这些产品分类必须是这个品牌的产品所有的你这个c里不会有ProductID的

Answer 8

if object_id('Product')is not null drop table Product
go
create table Product( ParentID int, BrandID int,CategoryID int ) 
insert Product select
1   ,         3     ,       4 union all select 
2   ,         3     ,       4 union all select 
3   ,         4     ,       5 
if object_id('Category')is not null drop table Category
go
create table Category(CategoryID int, ParentID int) 
insert Category select
1,            0 union all select 
2,            0 union all select
3,            1 union all select
4,            3  union all select
5,   2
if object_id('pro')is not null drop proc pro
go
create proc pro
(@id int,
@BrandID int)
as 
begin
declare @t table(CategoryID int, ParentID int)
declare @level int
set @level=@id insert @t select CategoryID,@level from Category where ParentID=@level
while @@rowcount>0
begin
set @level=@level+1
insert @t 
select a.CategoryID,@level from Category a,@t t
where a.ParentID=@level and a.ParentID=t.CategoryID
end delete  from @t
where CategoryID not in (select CategoryID from product where BrandID=@BrandID )

--以上获得根节点
----------------
--以下获得数目录
set @level=(select max(ParentID) from @t)
insert @t select @level,ParentID from Category where CategoryID=@level

while exists(select 1 from Category c,@t t where c.CategoryID=t.ParentID and 
c.CategoryID not in (select CategoryID from @t))
begin
insert @t select c.* from Category c,@t t where c.CategoryID=t.ParentID and 
c.CategoryID not in (select CategoryID from @t)
end select * from @t order by CategoryID

end
goexec pro 0,3

Answer 9

----结果如下
CategoryID  ParentID
----------- -----------
1           0
3           1
4           3(3 行受影响)

Answer 10

-- 楼上结果是正解 。但封装成函数更利于调用。
-- 引用了楼上的成果，做封装，具体实现，没有更改。
if object_id('Product')is not null drop table Product
go
create table Product( ParentID int, BrandID int,CategoryID int ) 
insert Product select
1   ,         3     ,       4 union all select 
2   ,         3     ,       4 union all select 
3   ,         4     ,       5 
if object_id('Category')is not null drop table Category
go
create table Category(CategoryID int, ParentID int) 
insert Category select
1,            0 union all select 
2,            0 union all select
3,            1 union all select
4,            3  union all select
5,            2
if object_id('funtest')is not null drop function funtest
go
create function funtest
(@id int,
@BrandID int) returns  @t table(CategoryID int, ParentID int)
as 
begin    declare @level int
    set @level=@id    insert @t select CategoryID,@level from Category where ParentID=@level
    while @@rowcount>0
    begin
        set @level=@level+1
        insert @t 
        select a.CategoryID,@level from Category a,@t t
        where a.ParentID=@level and a.ParentID=t.CategoryID
    end    delete  from @t
    where CategoryID not in (select CategoryID from product where BrandID=@BrandID )
    
--以上获得根节点
----------------
--以下获得数目录
    set @level=(select max(ParentID) from @t)
    insert @t select @level,ParentID from Category where CategoryID=@level
    
    while exists(select 1 from Category c,@t t where c.CategoryID=t.ParentID and 
                c.CategoryID not in (select CategoryID from @t))
    begin
        insert @t select c.* from Category c,@t t where c.CategoryID=t.ParentID and 
        c.CategoryID not in (select CategoryID from @t)
    end    return 
    
endgoselect * from dbo.funtest(0,3) order by CategoryID--exec pro 0,3

Answer 11

/*
标题：查询指定节点及其所有子节点的函数
作者：爱新觉罗·毓华(十八年风雨,守得冰山雪莲花开) 
时间：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)
as
begin
  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
  return
end
go--调用函数查询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 tb
drop function f_cid@@ROWCOUNT：返回受上一语句影响的行数。
返回类型：integer。
注释：任何不返回行的语句将这一变量设置为 0 ，如 IF 语句。
示例：下面的示例执行 UPDATE 语句并用 @@ROWCOUNT 来检测是否有发生更改的行。UPDATE authors SET au_lname = 'Jones' WHERE au_id = '999-888-7777'
IF @@ROWCOUNT = 0
   print 'Warning: No rows were updated'结果：(所影响的行数为 0 行)
Warning: No rows were updated

Answer 12

[Quote=引用 4 楼 soft_wsx 的回复:]
你是不是山西人哇

Answer 13

用cte也可以实现的，如下：;
with cte as
(
select distinct p.CategoryID,c.ParentID,0 as Layer
from Product p join Category c on p.CategoryID=c.CategoryID     
where p.BrandID=3  
union all    
 select  cte.CategoryID,c.ParentID,cte.Layer+1
 From cte join Category c on cte.ParentID=c.CategoryID
   where c.ParentID<>0
  )
 
 select *
 from cte c where not exists
   (select * from cte where
    layer>c.layer)