关于分类统计的，有点难度，请各位高手来看看，在线等待ing

以前我做过类似的，方法是这样的：
对kxtc_sort创建一个新列NewID, 根据数据的上下级关系，填充改列的值。该列的编码严格按照分级的概念设计的：
-文具(01)
|   |---钢笔(0101)
|    |----学生系列(010101)
|    |----精品系列(010102)
|
-服装(02)
    |---童装(0201)
    |    |----休闲系列(020101)
    |    |----运动服(020102)
    |    |----时尚系列(020103)
    |
    |---女装(0202)
   |----淑女系列(020201)
... ...
查询的时候，采用：
select s.kname, sum(kprice * tmuch) as tsum, sum(zk_price * tmuch) as tnew_sum, sum(tmuch)
  from (
select a.ksort, a.kprice, b.zk_price, b.many_mcuch as tmuch, c.NewID as tstNID
  from kitem a, Trade_List b, kxtc_sort c
where a.kcode = b.kcode and a.ksort = c.kname
) as t, kxtc_sort s
where left(t.tstNID, 9) = s.newid and s.kdeep = 3
group by s.kname其中：
where left(t.tstNID, 9) = s.newid and s.kdeep = 3里面的
left(t.tstNID, 9) <--> s.kdeep = 3
left(t.tstNID, 6) <--> s.kdeep = 2
left(t.tstNID, 3) <--> s.kdeep = 1
分别来查询3、2、1级我的统计中，由于特殊原因，采用了临时表。你也可以根据情况，考虑临时表。

to sankis：
也不多呀，只是提供了测试数据嘛

写的烦琐，试试这个：
--统计一级
select name3 as ksort,sum(tsum) as tsum,sum(tnew_sum) as tnew_sum,sum(t_much) as t_much from (
select * from (select x.kname,y.kname as name2,z.kname as name3 from (select a.kname,a.parent_id as id1,b.parent_id as id2 from kxtc_sort a ,(select kname,parent_id,db_id from kxtc_sort where kdeep=2) b where a.kdeep=3 and a.[parent_id]=b.db_id) x
,(select * from kxtc_sort) y,(select * from kxtc_sort) z where x.id1=y.[db_id] and x.id2=z.[db_id]) t1 --列出三级和二级、一级商品
left join
(select ksort,sum(tsum) as tsum,sum(tnew_sum) as tnew_sum,sum(t_much) as t_much from (
select b.ksort,b.kprice*many_mcuch as tsum,c.zk_price*many_mcuch as tnew_sum,c.many_mcuch as t_much from kitem b left join trade_list c on b.kcode=c.kcode) a
group by ksort) t2 --统计三级
on t1.kname=t2.ksort) grade3 group by name3
union
--统计二级
select name2 as ksort,sum(tsum) as tsum,sum(tnew_sum) as tnew_sum,sum(t_much) as t_much from (
select * from (select x.kname,y.kname as name2,z.kname as name3 from (select a.kname,a.parent_id as id1,b.parent_id as id2 from kxtc_sort a ,(select kname,parent_id,db_id from kxtc_sort where kdeep=2) b where a.kdeep=3 and a.[parent_id]=b.db_id) x
,(select * from kxtc_sort) y,(select * from kxtc_sort) z where x.id1=y.[db_id] and x.id2=z.[db_id]) t1 --列出三级和二级、一级商品
left join
(select ksort,sum(tsum) as tsum,sum(tnew_sum) as tnew_sum,sum(t_much) as t_much from (
select b.ksort,b.kprice*many_mcuch as tsum,c.zk_price*many_mcuch as tnew_sum,c.many_mcuch as t_much from kitem b left join trade_list c on b.kcode=c.kcode) a
group by ksort) t2 --统计三级
on t1.kname=t2.ksort) grade2 group by name2
union--统计三级
select ksort,sum(tsum) as tsum,sum(tnew_sum) as tnew_sum,sum(t_much) as t_much from (
select b.ksort,b.kprice*many_mcuch as tsum,c.zk_price*many_mcuch as tnew_sum,c.many_mcuch as t_much from kitem b left join trade_list c on b.kcode=c.kcode) a
group by ksort

TO wanyingsong(豌豆)：
我试了，按你的方法，只取 --统计一级得到的结果如下：ksort tsum tnew_sum tmuch
---------------------------------------
文具 115.00 110.00 4但这不是正确的

不是呀，每一级都是可以的呀
--统计二级
select name2 as ksort,sum(tsum) as tsum,sum(tnew_sum) as tnew_sum,sum(t_much) as t_much from (
select * from (select x.kname,y.kname as name2,z.kname as name3 from (select a.kname,a.parent_id as id1,b.parent_id as id2 from kxtc_sort a ,(select kname,parent_id,db_id from kxtc_sort where kdeep=2) b where a.kdeep=3 and a.[parent_id]=b.db_id) x
,(select * from kxtc_sort) y,(select * from kxtc_sort) z where x.id1=y.[db_id] and x.id2=z.[db_id]) t1 --列出三级和二级、一级商品
left join
(select ksort,sum(tsum) as tsum,sum(tnew_sum) as tnew_sum,sum(t_much) as t_much from (
select b.ksort,b.kprice*many_mcuch as tsum,c.zk_price*many_mcuch as tnew_sum,c.many_mcuch as t_much from kitem b left join trade_list c on b.kcode=c.kcode) a
group by ksort) t2 --统计三级
on t1.kname=t2.ksort) grade2 group by name2

豌豆的思路是对的，楼主你把你的kxtc_sort表的内容贴出来，你先说发的测试数据不太对，然后更正后的分类还有问题，把服装搞成了3类，干脆重发一贴吧

Functions(方程) ( ) 信誉：100 方法应该是可以的，我遇到类似的问题也是利用类似的方法的

to netcoder(朱二):
表ksort的数据如下：ksort db_id parent_id kdeep
----------------------------------------------------------------------------
文具 2004071313214070555 0 1
钢笔 2004071313215853343 2004071313214070555 2
学生系列 2004071313235230195 2004071313215853343 3
精品系列 2004071418244753343 2004071313215853343 3
服装 2004071418261657952 0 1
童装 2004071418274230195 2004071418261657952 2
休闲系列 2004071418282777475 2004071418274230195 3
运动服 200407141828531402 2004071418274230195 3
时尚系列 2004071418415353343 2004071418274230195 3
女装 2004071420494570555 2004071418261657952 2
淑女系列 2004071420495553343 2004071420494570555 3感觉 Functions(方程) 那个对表的设计思想蛮好理！
谢谢各位的参与！

楼上发的和你最初给的测试数据ksort表的数据不一样啊，结构也有点不一样给一份kxtc_sort的测试数据
insert ...

表ksort的测试数据如下： if exists (select * from sysobjects where id = OBJECT_ID('[kxtc_sort]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE [kxtc_sort]
CREATE TABLE [kxtc_sort] ( [id] [int] IDENTITY (1, 1) NOT NULL , [kname] [nvarchar] (50) NULL , [db_id] [nvarchar] (50) NOT NULL , [parent_id] [nvarchar] (50) NOT NULL , [ktype] [nvarchar] (50) NULL , [kflag] [nvarchar] (50) NULL , [kdeep] [int] NULL , [kother] [nvarchar] (50) NULL )
ALTER TABLE [kxtc_sort] WITH NOCHECK ADD CONSTRAINT [DF_kxtc_sort_kdeep] DEFAULT (1) FOR [kdeep],CONSTRAINT [PK_kxtc_sort] PRIMARY KEY NONCLUSTERED ( [db_id] ) SET IDENTITY_INSERT [kxtc_sort] ON INSERT [kxtc_sort] ( [id] , [kname] , [db_id] , [parent_id] , [ktype] , [kflag] , [kdeep] , [kother] ) VALUES ( 25 , '文具' , '2004071313214070555' , '0' , 'xm' , 'no' , 1 , 'no' )
INSERT [kxtc_sort] ( [id] , [kname] , [db_id] , [parent_id] , [ktype] , [kflag] , [kdeep] , [kother] ) VALUES ( 26 , '钢笔' , '2004071313215853343' , '2004071313214070555' , 'xm' , 'yes' , 2 , 'no' )
INSERT [kxtc_sort] ( [id] , [kname] , [db_id] , [parent_id] , [ktype] , [kflag] , [kdeep] , [kother] ) VALUES ( 28 , '学生系列' , '2004071313235230195' , '2004071313215853343' , 'xm' , 'yes' , 3 , 'no' )
INSERT [kxtc_sort] ( [id] , [kname] , [db_id] , [parent_id] , [ktype] , [kflag] , [kdeep] , [kother] ) VALUES ( 32 , '精品系列' , '2004071418244753343' , '2004071313215853343' , 'xm' , 'yes' , 3 , 'no' )
INSERT [kxtc_sort] ( [id] , [kname] , [db_id] , [parent_id] , [ktype] , [kflag] , [kdeep] , [kother] ) VALUES ( 33 , '服装' , '2004071418261657952' , '0' , 'xm' , 'no' , 1 , 'no' )
INSERT [kxtc_sort] ( [id] , [kname] , [db_id] , [parent_id] , [ktype] , [kflag] , [kdeep] , [kother] ) VALUES ( 34 , '童装' , '2004071418274230195' , '2004071418261657952' , 'xm' , 'yes' , 2 , 'no' )
INSERT [kxtc_sort] ( [id] , [kname] , [db_id] , [parent_id] , [ktype] , [kflag] , [kdeep] , [kother] ) VALUES ( 35 , '休闲系列' , '2004071418282777475' , '2004071418274230195' , 'xm' , 'yes' , 3 , 'no' )
INSERT [kxtc_sort] ( [id] , [kname] , [db_id] , [parent_id] , [ktype] , [kflag] , [kdeep] , [kother] ) VALUES ( 36 , '运动服' , '200407141828531402' , '2004071418274230195' , 'xm' , 'yes' , 3 , 'no' )
INSERT [kxtc_sort] ( [id] , [kname] , [db_id] , [parent_id] , [ktype] , [kflag] , [kdeep] , [kother] ) VALUES ( 37 , '时尚系列' , '2004071418415353343' , '2004071418274230195' , 'xm' , 'yes' , 3 , 'no' )
INSERT [kxtc_sort] ( [id] , [kname] , [db_id] , [parent_id] , [ktype] , [kflag] , [kdeep] , [kother] ) VALUES ( 38 , '女装' , '2004071420494570555' , '2004071418261657952' , 'xm' , 'yes' , 2 , 'no' )
INSERT [kxtc_sort] ( [id] , [kname] , [db_id] , [parent_id] , [ktype] , [kflag] , [kdeep] , [kother] ) VALUES ( 39 , '淑女系列' , '2004071420495553343' , '2004071420494570555' , 'xm' , 'yes' , 3 , 'no' ) SET IDENTITY_INSERT [kxtc_sort] OFF

newid 可以通过触发器来填充，也可以通过存储过程来重新分配。
使用触发器需要充分考虑许多情况：数据删除、数据增加、父节点改变、节点数据的级别改变等等，对NewID这个列的维护要考虑周全。
使用存储过程重新分配，可以足够保证它的正确性。NewID不必每次都是固定的，只要能够体现上下级关系，并且保证唯一即可。
也可以用你的开发语言（VB/Delphi/PB...）来进行处理。不过，我的处理由原来的程序处理修改为大量使用临时表，时间缩短了几乎一半。大体想法是：对每个parent_id ，生成一个临时表#tmp_1, #tmp_2, ...,这些临时表结构是完全相同的，每个表中的数据都是相同的父编号，每行数据都有一个IDENTITY列，从1开始编号。然后将这些编号前面加用‘0’补齐，赋值给NewID。
此时，每个数据（不管是哪一级），都是'00n'的NewID，然后使用循环，对每级的NewID加上父节点的NewID，即可。
比如当前
服装(02)
童装(01)(在兄弟里面，“排行第一”)
运动服(02)
童装的NewID(01)前面加上父节点服装的NewID(02)，就变为0201
运动服NewID(02)，加上父节点的NewID（0201,处理过的），变为020102

在原来那个表kxtc_sort的基础上，有个字段kflag,现在改用来写入节点所在位置(路径) -文具(0715174917354)
|   |---钢笔(0715174917354@0715174957492)
|    |----学生系列(0715174917354@0715174957492@0715175031581)
|    |----精品系列(0715174917354@0715174957492@0715175121162)加了这项数据，又会有什么好的办法呢？再次期待！另外，请教 wanyingsong(豌豆) ,你那个方法可以实现，如果数据特多的时候，效率会怎么样呢？

我重新开了一帖，请继续关注和回复：
http://community.csdn.net/Expert/topic/3179/3179432.xml?temp=1.659793E-02

见楼上帖
http://community.csdn.net/Expert/topic/3179/3179432.xml?temp=1.659793E-02

/*
newid 可以通过触发器来填充，也可以通过存储过程来重新分配。
使用触发器需要充分考虑许多情况：数据删除、数据增加、父节点改变、节点数据的级别改变等等，对NewID这个列的维护要考虑周全。
使用触发器重新分配，可以足够保证它的正确性。
NewID不必每次都是固定的，只要能够体现上下级关系，并且保证唯一即可。
也可以用你的开发语言（VB/Delphi/PB...）来进行处理。
不过，我的处理由原来的程序处理修改为大量使用临时表，时间缩短了几乎一半。
*/
-- 增加NewID列
alter table kxtc_sort add newid varchar(9)
go
select id, kname, db_id, parent_id, ktype, kflag, kdeep, kother, newid
from kxtc_sort

-- 生成一个临时表，用于构建生成个相关临时表的数据
if object_id('#tmp_1') is not null
drop table #tmp_1
select identity(int) as pk, 'select IDENTITY(int) as id ,db_id ' as col1, ' from kxtc_sort where parent_id = ''' + parent_id + '''' as col2
into #tmp_1
from (select distinct parent_id from kxtc_sort) as t

-- 下面语句可以看看数据
--select * from #tmp_1

declare @SqlCmd varchar(4000)

set @sqlcmd = ''
select @sqlcmd = @sqlcmd + col1 + 'into #tmp_' + cast(pk + 1 as varchar(3)) + col2 + char(10) from #tmp_1

print @sqlcmd
--exec (@sqlcmd)

/* 生成如下数据
lect IDENTITY(int) as id ,db_id into #tmp_2 from kxtc_sort where parent_id = '0'
select IDENTITY(int) as id ,db_id into #tmp_3 from kxtc_sort where parent_id = '071313214070555'
select IDENTITY(int) as id ,db_id into #tmp_4 from kxtc_sort where parent_id = '071313215853343'
select IDENTITY(int) as id ,db_id into #tmp_5 from kxtc_sort where parent_id = '071313373070555'
select IDENTITY(int) as id ,db_id into #tmp_6 from kxtc_sort where parent_id = '071313380253343'
select IDENTITY(int) as id ,db_id into #tmp_7 from kxtc_sort where parent_id = '071418261657952'
select IDENTITY(int) as id ,db_id into #tmp_8 from kxtc_sort where parent_id = '071418274230195'
*/

-- 下面的语句将上面临时表联合起来, 存放到#tmp_0中
Declare @SqlCmdUnion varchar(4000)
Set @SqlCmdUnion = 'select 0 as id, '' '' as Code into #tmp_0 '+ char(10)
select @SqlCmdUnion = @SqlCmdUnion + 'union select * from #tmp_' + cast(pk + 1 as varchar(3)) + char(10) from #tmp_1
print @SqlCmdUnion
set @SqlCmdUnion = @sqlcmd + char(10) + @SqlCmdUnion

/* 生成如下数据
select 0 as id, ' ' as Code into #tmp_0
union select * from #tmp_2
union select * from #tmp_3
union select * from #tmp_4
union select * from #tmp_5
union select * from #tmp_6
union select * from #tmp_7
union select * from #tmp_8
*/

-- 更新kxtc_sort中的NewID
Declare @SqlUpdate varchar(4000)
Declare @SqlAllRun varchar(4000)

Set @SqlUpdate = 'Update kxtc_sort set newid = right(''00'' + cast(#tmp_0.id as varchar(3)), 3) from kxtc_sort, #tmp_0 where db_id =#tmp_0.code'
print @SqlCmdUnion
set @SqlAllRun = @SqlCmdUnion + char(10) + @SqlUpdate
exec (@SqlAllRun)
print @SqlAllRun

--select * from #tmp_0

select * from kxtc_sort
-- 将NewID前面加上父节点的NewID
update kxtc_sort set newid = p.newid + kxtc_sort.newid from kxtc_sort, kxtc_sort p
where kxtc_sort.parent_id = p.db_id and kxtc_sort.kdeep = 2

update kxtc_sort set newid = p.newid + kxtc_sort.newid from kxtc_sort, kxtc_sort p
where kxtc_sort.parent_id = p.db_id and kxtc_sort.kdeep = 3

感谢 wanyingsong(豌豆) ,Functions(方程) ... 能力抽出时间帮调试！

调试易

关于分类统计的，有点难度，请各位高手来看看，在线等待ing

解决方案 »