请教SQLSERVER查询(100分)

求一SQLSERVER查询语句：<br>表：<br>姓名 &nbsp; &nbsp; 状态 &nbsp; &nbsp; 金额 &nbsp; 学科<br>张三 &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; &nbsp;50 &nbsp; 数学<br>李四 &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; &nbsp;30 &nbsp; 语文<br>王二 &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp;40 &nbsp; 数学<br>小宋 &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp;20 &nbsp; 语文<br>王五 &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp;30 &nbsp; 语文<br>&nbsp; <br><br><br>查询后：<br>状态0 &nbsp; 状态1 &nbsp; &nbsp;状态0金额合计 &nbsp; &nbsp;状态1金额合计 &nbsp; &nbsp;学科<br>&nbsp; &nbsp;2 &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;30 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;50 &nbsp; &nbsp; &nbsp; &nbsp; 语文<br>&nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;40 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;50 &nbsp; &nbsp; &nbsp; &nbsp; 数学

这个貌似一条SQL语句很难搞定,要么写Store Procedure 或都用内存表...

仅限于 SQL Server<br><br>完整测试代码：<br><br><br>create table #tbl(<br>&nbsp; 姓名 varchar(10),<br>&nbsp; 状态 int,<br>&nbsp; 金额 int,<br>&nbsp; 学科 varchar(10)<br>)<br><br><br>insert #tbl values('张三', &nbsp; &nbsp; &nbsp;1, &nbsp; &nbsp; &nbsp; &nbsp;50, &nbsp; '数学')<br>insert #tbl values('李四', &nbsp; &nbsp; &nbsp;1, &nbsp; &nbsp; &nbsp; &nbsp;30, &nbsp; '语文')<br>insert #tbl values('王二', &nbsp; &nbsp; &nbsp;0, &nbsp; &nbsp; &nbsp; &nbsp;40, &nbsp; '数学')<br>insert #tbl values('小宋', &nbsp; &nbsp; &nbsp;0, &nbsp; &nbsp; &nbsp; &nbsp;20, &nbsp; '语文')<br>insert #tbl values('王五', &nbsp; &nbsp; &nbsp;0, &nbsp; &nbsp; &nbsp; &nbsp;30, &nbsp; '语文')<br><br>select * from #tbl<br><br><br>select sum(是否状态0) as 状态0,<br>&nbsp; &nbsp; &nbsp; &nbsp;sum(是否状态1) as 状态1,<br>&nbsp; &nbsp; &nbsp; &nbsp;sum(状态0金额) as 状态0金额合计,<br>&nbsp; &nbsp; &nbsp; &nbsp;sum(状态1金额) as 状态1金额合计,<br>&nbsp; &nbsp; &nbsp; &nbsp;学科<br><br>from ( &nbsp; &nbsp; &nbsp;<br>&nbsp;<br>&nbsp; select case when 状态=0 then 1<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; else 0<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;end as 是否状态0,<br><br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;case when 状态=1 then 1<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; else 0<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;end as 是否状态1,<br><br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;case when 状态=0 then 金额 <br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; else 0 <br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;end as 状态0金额,<br><br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;case when 状态=1 then 金额 <br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; else 0 <br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;end as 状态1金额, &nbsp;<br>&nbsp; <br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;学科<br>&nbsp; &nbsp; from #tbl<br>) AA<br>group by 学科<br><br>drop table &nbsp;#tbl

select sum(是否状态0) as 状态0,<br>&nbsp; &nbsp; &nbsp; &nbsp;sum(是否状态1) as 状态1,<br>&nbsp; &nbsp; &nbsp; &nbsp;sum(状态0金额) as 状态0金额合计,<br>&nbsp; &nbsp; &nbsp; &nbsp;sum(状态1金额) as 状态1金额合计,<br>&nbsp; &nbsp; &nbsp; &nbsp;学科<br><br>from ( &nbsp; &nbsp; &nbsp;<br>&nbsp;<br>&nbsp; select case when 状态=0 then 1<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; else 0<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;end as 是否状态0,<br><br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;case when 状态=1 then 1<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; else 0<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;end as 是否状态1,<br><br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;case when 状态=0 then 金额 <br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; else 0 <br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;end as 状态0金额,<br><br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;case when 状态=1 then 金额 <br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; else 0 <br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;end as 状态1金额, &nbsp;<br>&nbsp; <br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;学科<br>&nbsp; &nbsp; from 表<br>) AA<br>group by 学科

多谢参与。特别是plenilune。