create table tb(time decimal(10,2)) insert into tb select 3.20 union all select 4.18 union all select 2.45 go --运算:取整数部分和小数部分分别求和,小数部分的和除以60再取整后与前面和相加得到小时数,小数部分求和后取余60得未进位分钟数 select (sum(floor(time))+floor(sum(time%1*100)/60))*1.00+sum(time%1*100)%60/100 from tb /* --------------------------------------- 10.23(1 行受影响)*/ go drop table tb
或者: create table tb(time decimal(10,2)) insert into tb select 3.20 union all select 4.18 union all select 2.45 go --获得精确的小时数,小数部分是小时,不是分 select sum(floor(time)+(time%1)/60*100) from tb /* --------------------------------------- 10.383300(1 行受影响)*/ go drop table tb
select sum(floor(time))+floor(sum(time%1*100)/60) +(sum(time%1*100)%60)/100 from tb;
from table
where 员工姓名='张三'
group by 员工姓名
insert tb select 5.30
insert tb select 2.20
select sum(col),
cast(
ltrim(sum(isnull(cast(PARSENAME(col, 2) AS int),0))
+sum(isnull(cast(PARSENAME(col,1) AS int),0))/60)
+'.'+ltrim(sum(isnull(cast(PARSENAME(col,1) AS int),0))%60) as decimal(10,2))
from tb /*
---------------------------------------- ------------
10.70 11.10(所影响的行数为 1 行)
*/
/*
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
19小时36分(1 行受影响)*/
insert into tb select 3.20
union all select 4.18
union all select 2.45
go
--运算:取整数部分和小数部分分别求和,小数部分的和除以60再取整后与前面和相加得到小时数,小数部分求和后取余60得未进位分钟数
select (sum(floor(time))+floor(sum(time%1*100)/60))*1.00+sum(time%1*100)%60/100 from tb
/*
---------------------------------------
10.23(1 行受影响)*/
go
drop table tb
create table tb(time decimal(10,2))
insert into tb select 3.20
union all select 4.18
union all select 2.45
go
--获得精确的小时数,小数部分是小时,不是分
select sum(floor(time)+(time%1)/60*100) from tb
/*
---------------------------------------
10.383300(1 行受影响)*/
go
drop table tb