简单的 没分了！

[hanbing_zjp]

CREATE TABLE tb
(
deptid INT ,
subdeptid INT ,
amount DECIMAL
)

INSERT INTO tb SELECT 1,NULL ,100 UNION ALL

SELECT 2, NULL ,200 UNION ALL
SELECT 3, 1, 500 UNION ALL

SELECT 4, 1 , 600 UNION ALL

SELECT 5, 2 , 300 UNION ALL

SELECT 6, 2 , 400



结果：

deptid amount
1， 1200
2， 900

就是把部门下的子部门也统计到主部门下来

[hanbing_zjp]

顶！

[lg3605119]

 CREATE TABLE tb 
      ( 
      deptid INT  , 
      subdeptid INT , 
      amount DECIMAL 
      ) 
      
      INSERT INTO tb SELECT 1,NULL ,100 UNION ALL 
      
                    SELECT 2, NULL ,200 UNION ALL 
                    SELECT 3,  1,  500 UNION ALL 
                    
                    SELECT 4,  1 ,  600 UNION ALL 
                    
                    SELECT 5,  2 ,  300 UNION ALL 
                    
                    SELECT 6,  2 ,  400 

select * from tb


with liang as 
(
  select *,typeid = deptid from tb where subdeptid is null
  union all 
  select a.*,typeid = b.typeid from tb a ,liang b where a.subdeptid = b.deptid
)
select typeid,sum(amount) as amount from liang  group by typeid

[lg3605119]

少写个分号

 CREATE TABLE tb 
      ( 
      deptid INT  , 
      subdeptid INT , 
      amount DECIMAL 
      ) 
      
      INSERT INTO tb SELECT 1,NULL ,100 UNION ALL 
      
                    SELECT 2, NULL ,200 UNION ALL 
                    SELECT 3,  1,  500 UNION ALL 
                    
                    SELECT 4,  1 ,  600 UNION ALL 
                    
                    SELECT 5,  2 ,  300 UNION ALL 
                    
                    SELECT 6,  2 ,  400 

select * from tb


;with liang as 
(
  select *,typeid = deptid from tb where subdeptid is null
  union all 
  select a.*,typeid = b.typeid from tb a ,liang b where a.subdeptid = b.deptid
)
select typeid,sum(amount) as amount from liang  group by typeid

[htl258_Tony]

[Quote=引用 3 楼 lg3605119 的回复:]
少写个分号

SQL code
CREATE TABLE tb
(
deptid INT ,
subdeptid INT ,
amount DECIMAL
)

INSERT INTO tb SELECT 1,NULL ,100 UNION ALL

SELECT 2, NULL ,200 UNION ALL
SELECT 3, 1, 500 UNION ALL

SELECT 4, 1 , 600 UNION ALL
…
[/Quote]

.net小鸟,你也姓梁?

[lg3605119]

tony 偶确实姓梁 不过是 神人 P梁 呵呵～～～

[hanbing_zjp]

sql 2000 的怎么写啊！

[ChinaJiaBing]

drop table tb
      CREATE TABLE tb 
      ( 
      deptid INT  , 
      subdeptid INT , 
      amount DECIMAL 
      ) 
      
      INSERT INTO tb SELECT 1,NULL ,100 UNION ALL 
      
                    SELECT 2, NULL ,200 UNION ALL 
                    SELECT 3,  1,  500 UNION ALL 
                    
                    SELECT 4,  1 ,  600 UNION ALL 
                    
                    SELECT 5,  2 ,  300 UNION ALL 
                    
                    SELECT 6,  2 ,  400
 select a.deptid,(a.amount+b.amount) amount from                  
 (select deptid,sum(amount)amount from tb 
   where subdeptid is null 
    group by deptid) a join
(select subdeptid,SUM(amount) amount from tb
  where subdeptid is not null
      group by subdeptid) b  
      
      on  a.deptid=b.subdeptid



deptid      amount
----------- ---------------------------------------
1           1200
2           900

(2 行受影响)