有一张加班和调休表，结构如下
id int 自增
type varchar(10) 1为加班，2为调休
counttime decima(5,1) 总时间数(加班或调休的时间数)
startdate datetime 开始时间
enddate datetime 结束时间
数据如下：
type startdate enddate counttime id
1 2012-04-18 14:33:22.640 2012-04-21 14:33:22.640 3.0 1
1 2012-04-14 14:34:48.970 2012-04-17 14:34:48.970 3.0 2
2 2012-04-19 14:34:49.407 2012-04-21 14:34:49.407 2.0 3
3 2012-04-23 14:34:50.220 2012-04-24 14:34:50.220 1.0 4
现我要统计 加班总时数，调休总时数 本月结余数（调休总时数-加班总时数）
sql该怎么写，求高人。。。。
------解决方案--------------------
- SQL code
create table tb( [type] varchar(10), startdate datetime, enddate datetime, counttime decimal(5,1), id int identity(1,1))insert into tb select '1', '2012-04-18 14:33:22.640', '2012-04-21 14:33:22.640', 3.0 union allselect '1', '2012-04-14 14:34:48.970', '2012-04-17 14:34:48.970', 3.0 union allselect '2', '2012-04-19 14:34:49.407', '2012-04-21 14:34:49.407', 2.0 union allselect '2', '2012-04-23 14:34:50.220', '2012-04-24 14:34:50.220', 1.0select t1.加班时间 ,t2.调休时间 ,t1.加班时间 -t2.调休时间 AS 结余数 from(select [type],SUM(counttime) AS 加班时间 from tb where [type]='1' group by [type]) t1,(select [type],SUM(counttime) AS 调休时间 from tb where [type]='2' group by [type]) t2
------解决方案--------------------
- SQL code
--> 测试数据：[test]if object_id('[test]') is not null drop table [test]create table [test]([type] int,[startdate] datetime,[enddate] datetime,[counttime] numeric(2,1),[id] int)insert [test]select 1,'2012-04-18 14:33:22.640','2012-04-21 14:33:22.640',3.0,1 union allselect 1,'2012-04-14 14:34:48.970','2012-04-17 14:34:48.970',3.0,2 union allselect 2,'2012-04-19 14:34:49.407','2012-04-21 14:34:49.407',2.0,3 union allselect 2,'2012-04-23 14:34:50.220','2012-04-24 14:34:50.220',1.0,4select 加班总时数,调休总时数,调休总时数-加班总时数 as 本月结余 from(select SUM(case when [type]=1 then [counttime] else 0 end) as 加班总时数,SUM(case when [type]=2 then [counttime] else 0 end) as 调休总时数from test)a/*加班总时数 调休总时数 本月结余6.0 3.0 -3.0*/