求一个统计语句

Question

原表如下
开课学院  课程名称  上课学院      学时
aa         语文       bb           60
aa         语文       cc           60
aa         中文       bb           60
bb         数学       aa           50
cc         历史       aa           40
按开课学院求和统计到开课、按上课学院求和统计到上课、互开=开课-上课
得到如下的统计表
开课学院  开课 上课  互开
aa        180    90   90
bb         50    120  -70
cc         40     60  -20       

Answer 1

select a.开课学院,a.开课,b.上课,a.开课-b.上课 as 互开
from (
select 开课学院,sum(学时) as 开课
from 原表
group by 开课学院
) a inner jon (
select 上课学院,sum(学时) as 上课
from 原表
group by 上课学院
) b on a.开课学院=b.上课学院

Answer 2

或者select 开课学院,sum(开课),sum(上课),sum(开课)-sum(上课)
form (
select 开课学院,学时 as 开课,0 as 上课
from 原表
union all
select 上课学院,0,学时
from 原表
)
group by 开课学院

Answer 3

--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([开课学院] varchar(2),[课程名称] varchar(4),[上课学院] varchar(2),[学时] int)
insert [tb]
select 'aa','语文','bb',60 union all
select 'aa','语文','cc',60 union all
select 'aa','中文','bb',60 union all
select 'bb','数学','aa',50 union all
select 'cc','历史','aa',40
select 开课学院 ,
sum(学时) as 开课,
(select sum(学时) from [tb] where 上课学院=t.开课学院) as 上课,
sum(学时)-(select sum(学时) from [tb] where 上课学院=t.开课学院) as 互开
from [tb] t
group by 开课学院
-----------------------
aa 180 90 90
bb 50 120 -70
cc 40 60 -20

Answer 4

select 开课学院,
sum(case when type='开课' then 学时 esle 0 end) as 开课,
sum(case when type='上课' then -学时 esle 0 end) as 上课,
sum(学时) as 互开
from
(select '开课' as type,开课学院,学时 from tb
 union all
 select '上课' as type,开课学院,-学时 from tb
) t
group by 开课学院

Answer 5

IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB([开课学院] VARCHAR(10),  [课程名称] VARCHAR(10),  [上课学院] VARCHAR(10),      [学时] INT )
INSERT INTO TB
SELECT 'aa','语文','bb',          60 UNION ALL
SELECT 'aa','语文','cc',          60 UNION ALL
SELECT 'aa','中文','bb',          60 UNION ALL
SELECT 'bb','数学','aa',          50 UNION ALL
SELECT 'cc','历史','aa',          40
SELECT ISNULL(T1.KAI,T2.SHANG),ISNULL(T1.TOTAL,0),ISNULL(T2.TOTAL,0)
,ISNULL(T1.TOTAL,0)- ISNULL(T2.TOTAL,0)
FROM (
SELECT [开课学院] 'KAI',SUM([学时]) 'TOTAL' FROM TB
GROUP BY [开课学院]
) T1
FULL JOIN (
SELECT [上课学院] 'SHANG',SUM([学时]) 'TOTAL' FROM TB
GROUP BY [上课学院]
) T2 ON T1.KAI=T2.SHANG
/*
aa 180 90 90
bb 50 120 -70
cc 40 60 -20
*/

Answer 6

---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([开课学院] varchar(2),[课程名称] varchar(4),[上课学院] varchar(2),[学时] int)
insert [tb]
select 'aa','语文','bb',60 union all
select 'aa','语文','cc',60 union all
select 'aa','中文','bb',60 union all
select 'bb','数学','aa',50 union all
select 'cc','历史','aa',40
 
---查询---
select 开课学院,
sum(case when type='开课' then 学时 else 0 end) as 开课,
sum(case when type='上课' then -学时 else 0 end) as 上课,
sum(学时) as 互开
from
(select '开课' as type,开课学院,学时 from tb
 union all
 select '上课' as type,上课学院,-学时 from tb
) t
group by 开课学院---结果---
开课学院 开课          上课          互开          
---- ----------- ----------- ----------- 
aa   180         90          90
bb   50          120         -70
cc   40          60          -20（所影响的行数为 3 行）