-- 创建视图,分部分求和
Create View viewTimeLen As
Select Sum(Convert(int, SubString(TimeLen, 1, 2))) as H,
Sum(Convert(int, SubString(TimeLen, 4, 2))) as M,
Sum(Convert(int, SubString(TimeLen, 7, 2))) as S
From xxTable
-- 再创建个视图,按60进制进位
Create View viewTimeLen60 As
Select H + M / 60 + S / 3600 as H,
M % 60 + (S % 3600) / 60 as M,
S % 60 as S
From viewTimeLen
-- 查询时从视图中查询
Select Convert(varchar(2), H) + ':' + Convert(varchar(2), M) + ':' + Convert(varchar(2), S) as TimeLen
From viewTimeLen60
SELECT
MAX(DeptName) AS DeptNameMax,
COUNT(PhoneCode) AS PhoneCodeCount,
MAX(PhoneCode) AS PhoneCodeMax,
CONVERT(char(8),dateadd(ss,sum(datediff(ss,0,cast(TimeLen as datetime))),0),108) AS TimeLenSum
FROM dbo.v_Report_ZJ
GROUP BY DeptName, PhoneCode
SELECT MAX(DeptName) AS DeptNameMax, COUNT(PhoneCode) AS PhoneCodeCount,
MAX(PhoneCode) AS PhoneCodeMax, SUM(TimeLen) AS TimeLenSum
FROM dbo.v_Report_ZJ
GROUP BY DeptName, PhoneCode