有一个类别表,形如
cagegoryid parent desn
1 0 例子1
2 0 例子2
3 1 例子3
4 1 例子4
5 3 例子5
6 5 例子6
7 2 例子7
8 2 例子8
9 3 例子9
10 9 例子10
一个商品表,最普通的那种,商品表里有一个外键categoryid,这个categoryid就是记录这个商品属于何种类别,这个类别应该是类别表的最底的一层的类别,现在我想知道用什么样的方法可以统计成形如
cagegoryid parent desn 商品数量
1 0 例子1 100
2 0 例子2 80
3 1 例子3 60
4 1 例子4 40
5 3 例子5 30
6 5 例子6 60
7 2 例子7 40
8 2 例子8 40
9 3 例子9 30
10 9 例子10 30意思就是我想知道分类表中的每一个类别下面的商品数目有多少,比如说我点击例子1的分类,那么例子1下面的例子3跟例子4下面的商品总数分别就应该为60跟40,我在点击例子3,那么例子3下面的例子5跟例子9的商品数量就应该为30跟30,类别表是无限分类的,可以一直分类下去,现在就是2个表想合并成第二个图式那样,不知道哪位大哥大姐可以赐教~想要一个存储过程~~谢谢!!
cagegoryid parent desn
1 0 例子1
2 0 例子2
3 1 例子3
4 1 例子4
5 3 例子5
6 5 例子6
7 2 例子7
8 2 例子8
9 3 例子9
10 9 例子10
一个商品表,最普通的那种,商品表里有一个外键categoryid,这个categoryid就是记录这个商品属于何种类别,这个类别应该是类别表的最底的一层的类别,现在我想知道用什么样的方法可以统计成形如
cagegoryid parent desn 商品数量
1 0 例子1 100
2 0 例子2 80
3 1 例子3 60
4 1 例子4 40
5 3 例子5 30
6 5 例子6 60
7 2 例子7 40
8 2 例子8 40
9 3 例子9 30
10 9 例子10 30意思就是我想知道分类表中的每一个类别下面的商品数目有多少,比如说我点击例子1的分类,那么例子1下面的例子3跟例子4下面的商品总数分别就应该为60跟40,我在点击例子3,那么例子3下面的例子5跟例子9的商品数量就应该为30跟30,类别表是无限分类的,可以一直分类下去,现在就是2个表想合并成第二个图式那样,不知道哪位大哥大姐可以赐教~想要一个存储过程~~谢谢!!
insert into tb
select 1, 0, '例子1', 100 union all
select 2, 0, '例子2', 80 union all
select 3, 1, '例子3', 60 union all
select 4, 1, '例子4', 40 union all
select 5, 3, '例子5', 30 union all
select 6, 5, '例子6', 60 union all
select 7, 2, '例子7', 40 union all
select 8, 2, '例子8', 40 union all
select 9, 3, '例子9', 30 union all
select 10, 9, '例子10', 30 gocreate function fnGetChildren(@cagegoryid int)
returns int
as
begin
declare @tmp table(cagegoryid int,商品数量 int)
declare @sum int
insert @tmp select cagegoryid,商品数量 from tb where cagegoryid = @cagegoryid
while @@rowcount > 0
insert @tmp select a.cagegoryid,a.商品数量 from tb as a inner join @tmp as b
on a.parent = b.cagegoryid where a.cagegoryid not in(select cagegoryid from @tmp)
select @sum=sum(商品数量) from @tmp
return (@sum)
endgoselect *,dbo.fnGetChildren(cagegoryid) as 分类下的商品总数 from tb
/*
cagegoryid parent desn 商品数量 分类下的商品总数
----------------------------------------------------------
1 0 例子1 100 350
2 0 例子2 80 160
3 1 例子3 60 210
4 1 例子4 40 40
5 3 例子5 30 90
6 5 例子6 60 60
7 2 例子7 40 40
8 2 例子8 40 40
9 3 例子9 30 60
10 9 例子10 30 30
*/drop table tb
drop function dbo.fnGetChildren
returns int
as
begin
declare @tmp table(cagegoryid int,商品数量 int)
declare @sum int
insert @tmp select cagegoryid,商品数量 from tb where cagegoryid = @cagegoryid
while @@rowcount > 0
insert @tmp select a.cagegoryid,a.商品数量 from tb as a inner join @tmp as b
on a.parent = b.cagegoryid where a.cagegoryid not in(select cagegoryid from @tmp)
select @sum=sum(商品数量) from @tmp
return (@sum)
endgo
AS
(
SELECT cagegoryid, SUM(商品数量) AS 商品数量 FROM 商品表 GROUP BY cagegoryid UNION ALL SELECT parent AS cagegoryid, 商品数量
FROM 类别表 INNER JOIN CteToTal ON CteToTal.cagegoryid = 类别表.cagegoryid
)
SELECT A.cagegoryid, A.parent, A.desn, B.商品数量 FROM 类别表 AS A
LEFT JOIN (SELECT cagegoryid, SUM(商品数量) AS 商品数量 FROM CteToTal GROUP BY cagegoryid) AS B
ON A.cagegoryid = B.cagegoryid
Create Table tb
(Cagegoryid Int,
Parent Int,
Desn Nvarchar(10),
Q Int
)
Insert tb Values(1,0,'例子1',100 )
Insert tb Values(2,0,'例子2',80)
Insert tb Values(3,1,'例子3',60)
Insert tb Values(4,1,'例子4',40)
Insert tb Values(5,3,'例子5',30)
Insert tb Values(6,5,'例子6',60)
Insert tb Values(7,2,'例子7',40)
Insert tb Values(8,2,'例子8',40)
Insert tb Values(9,3,'例子9',30)
Insert tb Values(10,9,'例子10',30)*/
/*
select Cagegoryid,Parent,Desn,
Q=(select sum(Q) from
(select * from tb
where Parent=(select Cagegoryid from tb where Desn='例子3')
or Cagegoryid=(select Cagegoryid from tb where Desn='例子3'))a) from tb
where Desn='例子3'*/
Alter Procedure SP_tb
@sDesn Nvarchar(10)=''
AS
Set @sDesn=Isnull(@sDesn,'')Set Nocount OnDeclare @sql Nvarchar(2000)If @sDesn<>''
Set @sql='select Cagegoryid,Parent,Desn,
Q=(select sum(Q) from
(select * from tb
where Parent=(select Cagegoryid from tb where Desn='''+@sDesn+''')
or Cagegoryid=(select Cagegoryid from tb where Desn='''+@sDesn+'''))a) from tb
where Desn='''+@sDesn+''''
Else Set @sql='select A=''请输入参数'''
exec(@sql)Set NOcount Off--SP_tb @sDesn='例子3'
create table tb(id int, name varchar(10), pid int, px int)
insert into tb values(0 , '栏目分类', 0 , 1)
insert into tb values(1 , '动物' , 0 , 1)
insert into tb values(2 , '视频' , 0 , 2)
insert into tb values(3 , '老虎' , 1 , 1)
insert into tb values(4 , '狮子' , 1 , 2)
insert into tb values(5 , '搞笑' , 2 , 1)
go--查询指定节点及其所有子节点的函数
CREATE FUNCTION f_Cid(@ID int) RETURNS @t_Level TABLE(ID int,Level int)
AS
BEGIN
DECLARE @Level int
SET @Level=1
INSERT @t_Level SELECT @ID,@Level
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @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 --调用函数查询id = 1及其所有子节点
SELECT a.* FROM tb a,f_Cid(1) b WHERE a.ID=b.ID
/*
id name pid px
----------- ---------- ----------- -----------
1 动物 0 1
3 老虎 1 1
4 狮子 1 2
(所影响的行数为 3 行)
*/
我现在是一个类别表,类别表里面没有商品数量这个字段,
cagegoryid parent desn
1 0 例子1
2 0 例子2
3 1 例子3
4 1 例子4
5 3 例子5
6 5 例子6
7 2 例子7
8 2 例子8
9 3 例子9
10 9 例子10 还有一个商品表,想得到
cagegoryid parent desn 商品数量
1 0 例子1 100
2 0 例子2 80
3 1 例子3 60
4 1 例子4 40
5 3 例子5 30
6 5 例子6 60
7 2 例子7 40
8 2 例子8 40
9 3 例子9 30
10 9 例子10 30
这样形式的一个表