求SQL语句一条!

[wumylove1234]

select 部门名称 as 合计,sum(已交金额),sum(未交金额)
from
(SELECT
A.BMMC AS 部门名称,
sum(case when B.bmbh is null or B.jk=0 then 0 else B.je end) as 已交金额,
sum(case when B.BMBH is null or b.jk=1 then 0 else B.je end) as 未交金额
FROM 部门表 A left JOIN 工作证书表 B
ON A.BMBH = B.BMBH and (B.jdrq between '2004-03-01' and '2004-03-18')
group by A.bmbh,A.bmmc,A.bmlb having A.bmlb='A' )
这条语句有语法问题!

初学SQL,想实现对子查询的结果生成汇总行.注意,是行,不是列!

如果生成汇总行可以实现,那怎么样才能做到即有分类汇总的数据,又有对分类汇总再进行汇总(即生成合计)的记录集?

[pigsanddogs]

select 部门名称 as 合计,sum(已交金额),sum(未交金额)
from
(SELECT
A.BMMC AS 部门名称,
sum(case when B.bmbh is null or B.jk=0 then 0 else B.je end) as 已交金额,
sum(case when B.BMBH is null or b.jk=1 then 0 else B.je end) as 未交金额
FROM 部门表 A left JOIN 工作证书表 B
ON A.BMBH = B.BMBH and (B.jdrq between '2004-03-01' and '2004-03-18')
group by A.bmbh,A.bmmc,A.bmlb having A.bmlb='A' ) as a

[wumylove1234]

刚才试了一下,改成这样:
select 部门名称 as 合计,sum(已交金额),sum(未交金额)
from
(SELECT
A.BMMC AS 部门名称,
sum(case when B.bmbh is null or B.jk=0 then 0 else B.je end) as 已交金额,
sum(case when B.BMBH is null or b.jk=1 then 0 else B.je end) as 未交金额
FROM 部门表 A left JOIN 工作证书表 B
ON A.BMBH = B.BMBH and (B.jdrq between '2004-03-01' and '2004-03-18')
group by A.bmbh,A.bmmc,A.bmlb) as tmptable
group by 部门名称,已交金额,未交金额

生成的是按部门分类汇总的,呵呵,晕了.直接就是子查询的结果.

[wumylove1234]

哇,朋友回帖好快啊.
你的那种方法我试过了.因为我的父查询中用汇总函数,所以要求有group by,但对部门名称分类汇总的话?那还有什么意义啦?跟子查询是一样的.

[wumylove1234]

compute这个语句不知能否达到我的要求!不会用,所以没有试验!