有一个表
用户 时间
A 00:00:21
A 00:01:21
B 00:00:30
B 00:00:30
C 00:00:21
得到按时间总数排列的表
用户 次数 总计
A 2 00:01:42
B 2 00:01:00
C 1 00:00:21
用户 时间
A 00:00:21
A 00:01:21
B 00:00:30
B 00:00:30
C 00:00:21
得到按时间总数排列的表
用户 次数 总计
A 2 00:01:42
B 2 00:01:00
C 1 00:00:21
Create table myUser
(
[用户] nvarchar(10),
[时间] time
)
insert into myUser
select 'A','00:00:21'
union all
select 'A','00:00:21'
union all
select 'B','00:00:30'
union all
select 'B','00:00:30'
union all
select 'C','00:00:21'
select
[用户],
COUNT(1) 次数,
SUM(DATEPART(hh,[时间])*3600+DATEPART(mi,[时间])*60+DATEPART(ss,[时间])) 总计 --这里得到的是秒
from myUser group by [用户]
as (
select [用户], COUNT(1) 次数,
SUM(DATEPART(hh,[时间])*3600+DATEPART(mi,[时间])*60+DATEPART(ss,[时间])) 总计
--这里得到的是秒
from myUser group by [用户]
)
select [用户], 次数,
LTRIM(RTRIM(str(总计/3600)))+ ':'+LTRIM(RTRIM(str(总计%3600/60)))
+':'+LTRIM(RTRIM(str(总计%3600%60)))
from #a
with #a
as (
select [用户], COUNT(1) 次数,
SUM(DATEPART(hh,[时间])*3600+DATEPART(mi,[时间])*60+DATEPART(ss,[时间])) 总计
--这里得到的是秒
from #myUser group by [用户]
)
select [用户], 次数,
CONVERT(VARCHAR(12),CONVERT(DATETIME ,LTRIM(RTRIM(str(总计/3600)))
+ ':'+LTRIM(RTRIM(str(总计%3600/60)))
+':'+LTRIM(RTRIM(str(总计%3600%60)))),108)
from #a
CREATE TABLE t1
(
yonghu VARCHAR(2),
shijian TIME
)
INSERT INTO t1
SELECT 'A','01:00:21' UNION ALL
SELECT 'A','00:01:21' UNION ALL
SELECT 'B','00:00:30' UNION ALL
SELECT 'B','02:00:30' UNION ALL
SELECT 'C','00:00:21'
SELECT DATEPART(HOUR,shijian)*3600,DATEPART(MINUTE,shijian)*60,DATEPART(SECOND,shijian) FROM t1;WITH aaa AS
(
SELECT yonghu,
COUNT(1) AS acount,
SUM(DATEPART(HOUR,shijian)*3600+DATEPART(MINUTE,shijian)*60+DATEPART(SECOND,shijian)) AS miao
FROM t1
GROUP BY yonghu
)
--SELECT * FROM aaa
SELECT yonghu AS [用户],
acount AS [次数],
RIGHT('00'+LTRIM(miao/3600),2)+':'+RIGHT('00'+LTRIM(miao%3600/60),2)+':'+RIGHT('00'+LTRIM(miao%3600%60),2) AS [总计]
FROM aaa--------------------
用户 次数 总计
A 2 01:01:42
B 2 02:01:00
C 1 00:00:21