现有一个查询出来的结果如下姓名 时差 类型
A 1:01 相关
A 3:23 相关
A 2:09 无关
A 5:14 无关
B 6:03 相关
B 2:21 无关
B 3:01 相关
B 4:55 无关
显示为
姓名 相关 无关
A 4:24 7:23
B 9:04 7:16
谢谢大家了!!
A 1:01 相关
A 3:23 相关
A 2:09 无关
A 5:14 无关
B 6:03 相关
B 2:21 无关
B 3:01 相关
B 4:55 无关
显示为
姓名 相关 无关
A 4:24 7:23
B 9:04 7:16
谢谢大家了!!
case when 类型='无关' then sum(时差) else 0 end as 无关
from table
group by 姓名
insert into tb values('A', '1:01', '相关')
insert into tb values('A', '3:23', '相关')
insert into tb values('A', '2:09', '无关')
insert into tb values('A', '5:14', '无关')
insert into tb values('B', '6:03', '相关')
insert into tb values('B', '2:21', '无关')
insert into tb values('B', '3:01', '相关')
insert into tb values('B', '4:55', '无关')
goselect 姓名,
max(case when 类型 = '相关' then value else '' end) 相关,
max(case when 类型 = '无关' then value else '' end) 无关
from
(
select 姓名,类型,cast(sum(cast(left(时差,charindex(':',时差)-1) as int)) as varchar) + ':' +
cast(sum(cast(substring(时差,charindex(':',时差)+1,len(时差)) as int)) as varchar) value
from tb group by 姓名,类型
) t
group by 姓名
drop table tb/*
姓名 相关 无关
---------- ---- ----
A 4:24 7:23
B 9:4 6:76(所影响的行数为 2 行)
*/
insert @a select 'A' ,'1:01' ,'相关'
union all select 'A' ,'3:23' ,'相关'
union all select 'A' ,'2:09' ,'无关'
union all select 'A' ,'5:14' ,'无关'
union all select 'B' ,'6:03' ,'相关'
union all select 'B' ,'2:21' ,'无关'
union all select 'B' ,'3:01' ,'相关'
union all select 'B' ,'4:55', '无关'select 姓名,
相关=convert(varchar(10),dateadd(minute, sum(case when 类型='相关' then datediff(minute,'00:00:00',时差) end),'00:00:00'),108),
无关=convert(varchar(10),dateadd(minute, sum(case when 类型='无关' then datediff(minute,'00:00:00',时差) end),'00:00:00'),108)
from @a
group by 姓名
/*
姓名 相关 无关
---------- ---------- ----------
A 04:24:00 07:23:00
B 09:04:00 07:16:00(所影响的行数为 2 行)
*/
insert into tb values('A', '1:01', '相关')
insert into tb values('A', '3:23', '相关')
insert into tb values('A', '2:09', '无关')
insert into tb values('A', '5:14', '无关')
insert into tb values('B', '6:03', '相关')
insert into tb values('B', '2:21', '无关')
insert into tb values('B', '3:01', '相关')
insert into tb values('B', '4:55', '无关')
goselect 姓名,
max(case when 类型 = '相关' then value else '' end) 相关,
max(case when 类型 = '无关' then value else '' end) 无关
from
(
select 姓名,类型,cast(sum(cast(left(时差,charindex(':',时差)-1) as int)) +
sum(cast(substring(时差,charindex(':',时差)+1,len(时差)) as int))/60 as varchar) + ':' +
right('00'+cast(sum(cast(substring(时差,charindex(':',时差)+1,len(时差)) as int)) % 60 as varchar),2)
value
from tb group by 姓名,类型
) t
group by 姓名
drop table tb/*
姓名 相关 无关
---------- ----- -----
A 4:24 7:23
B 9:04 7:16(所影响的行数为 2 行)
*/
insert into t2 select 'A','1:01','相关'
UNION ALL SELECT 'A' , '3:23', '相关'
UNION ALL SELECT 'A' , '2:09' , '无关'
UNION ALL SELECT 'A' , '5:14' , '无关'
UNION ALL SELECT 'B' , '6:03' , '相关'
UNION ALL SELECT 'B' , '2:21' , '无关'
UNION ALL SELECT 'B' , '3:01' , '相关'
UNION ALL SELECT 'B', '4:55', '无关'select 姓名,(cast(sum(cast(substring(时差,1,charindex(':',时差)-1) as int)) as varchar)
+':'+
cast(sum(cast(substring(时差,charindex(':',时差)+1,len(时差)-charindex(':',时差)) as int)) as varchar)) as '时间',类型
from t2 group by 姓名,类型 order by 姓名
select 姓名,
max(case when 类型 = '相关' then 时间 else '' end) 相关,
max(case when 类型 = '无关' then 时间 else '' end) 无关
from
(
select 姓名,(cast(sum(cast(substring(时差,1,charindex(':',时差)-1) as int)) as varchar)
+':'+
cast(sum(cast(substring(时差,charindex(':',时差)+1,len(时差)-charindex(':',时差)) as int)) as varchar)) as 时间,类型
from t2 group by 姓名,类型
) t group by 姓名