怎么写这样的Sql语句，在线等！

[lzol]

学生信息表
CREATE TABLE [dbo].[student] (
[xh] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[bj] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[xm] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
成绩表
CREATE TABLE [dbo].[cj] (
[xh] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[bj] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[km] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[cj] [float] NULL ,
[begindate] [datetime] NULL ,
[enddate] [datetime] NULL
) ON [PRIMARY]
GO
班级表
CREATE TABLE [dbo].[bj] (
[bjbh] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[bjmc] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

想要的结果
班级　学号　姓名　语文　数学　
1班　　11　　张三　93　　92
1班　　12　　李四　90　　97
...........................
合计 321 323
2班 21 王五 95 80
...........................
...........................
...........................
总计 987 954

[了缘]

select 班级,　学号,　姓名,　case cj.km when '语文' then '语文'end,　
case cj.km when '数学' then '数学' end
from student
left join cj on student.xh=cj.xh(student與cj連接條件）
left join bj on (student與bj連接條件）

[lzol]

wutao411(了缘) 兄，这样算不出合计的数据呀。

[csyu85]

用语句级的函数可以实现!!

[zjcxc]

--示例数据
CREATE TABLE [dbo].[student] (
[xh] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[bj] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[xm] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
insert student select 1,1,'张三'
union all select 2,1,'李四'
union all select 1,2,'王五'
GO

CREATE TABLE [dbo].[cj] (
[xh] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[bj] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[km] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[cj] [float] NULL ,
[begindate] [datetime] NULL ,
[enddate] [datetime] NULL
) ON [PRIMARY]
insert cj select 1,1,'语文',93,'2003-1-1','2003-11-1'
union all select 1,1,'数学',92,'2003-1-1','2003-11-1'
union all select 1,2,'语文',90,'2003-1-1','2003-11-1'
union all select 1,2,'数学',97,'2003-1-1','2003-11-1'
union all select 2,1,'语文',95,'2003-1-1','2003-11-1'
union all select 2,1,'数学',80,'2003-1-1','2003-11-1'
GO

CREATE TABLE [dbo].[bj] (
[bjbh] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[bjmc] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
insert bj select 1,'1班'
union all select 2,'2班'
GO

--查询的存储过程
create proc p_qry
@begindate datetime,
@enddate datetime
as
select 班级=case
when grouping(a.bjmc)=1
then '总计'
when grouping(b.xh)=1
then '合计'
else a.bjmc end
,学号=isnull(cast(b.xh as varchar),'')
,姓名=isnull(b.xm,'')
,语文=cast(sum(case c.km when '语文' then cj else 0 end) as decimal(10,2))
,数学=cast(sum(case c.km when '数学' then cj else 0 end) as decimal(10,2))
from bj a,student b,cj c
where a.bjbh=b.bj
and a.bjbh=c.bj
and b.xh=c.xh
and c.begindate>=@begindate
and c.enddate<=@enddate
group by a.bjmc,b.xh,b.xm with rollup
having grouping(b.xm)=0
or grouping(a.bjmc)=1
or grouping(b.xh)=1
go

--调用
exec p_qry '2003-1-1','2003-11-1'
go

--删除测试
drop table cj,bj,student
drop proc p_qry

/*--测试结果

班级 学号 姓名 语文 数学
---------- ------ --------- ----------- ----------
1班 1 张三 93.00 92.00
1班 2 李四 95.00 80.00
合计 188.00 172.00
2班 1 王五 90.00 97.00
合计 90.00 97.00
总计 278.00 269.00

（所影响的行数为 6 行）
--*/