select bc,gh,AVG(chaji) as avg_chaji from ( select BC,gh,chaji=case when tjsj< kjsj then DATEDIFF(minute,cast('1999-01-01 '+kjsj AS datetime),CAST('1999-01-02 '+tjsj as datetime)) else DATEDIFF(minute,cast('1999-01-01 '+kjsj AS datetime),CAST('1999-01-01 '+tjsj as datetime)) end from tbname ) k group by bc,gh
/* ************************************* * T-MAC 小编 * * -->努力成长中 * * -->梦想DBA * ************************************* */ if OBJECT_ID('tb') is not null drop table tb go create table tb (bc varchar(10) , gh varchar(10), kjsj varchar(10),tjsj varchar(10)) insert tb select '丙', 'NHRB400S', '11:34' , '12:00' union select '丁', 'Q195' , '19:18' , '19:54' union select '丁', 'Q195' , '23:45' , '00:28' union select '丁', 'Q195' , '00:30' , '01:04' union select '甲', 'Q195' , '01:07' , '01:44' union select '甲', 'Q195' , '01:47' , '02:27' union select '甲', 'Q195' , '02:30' , '03:04' union select '甲', 'Q195' , '03:07' , '03:43' union select '乙', '20MnK', '13:02' , '13:29' union select '乙', '20MnK', '13:01' , '14:00' union select '乙', '20MnK', '14:02' , '14:34' union select '乙', '20MnK', '14:35' , '15:06' goselect BC,gh,avg_cha=cast(avg(case when tjsj< kjsj then DATEDIFF(minute,cast('1999-01-01 '+kjsj AS datetime),CAST('1999-01-02 '+tjsj as datetime)) else DATEDIFF(minute,cast('1999-01-01 '+kjsj AS datetime),CAST('1999-01-01 '+tjsj as datetime)) end) as decimal(18,2)) from tb group by bc,gh /* BC gh avg_cha ---------- ---------- --------------------------------------- 乙 20MnK 37.00 丙 NHRB400S 26.00 丁 Q195 37.00 甲 Q195 36.00*/
精度的地方修改下 select BC,gh,avg_cha=cast(avg(1.0*case when tjsj< kjsj then DATEDIFF(minute,cast('1999-01-01 '+kjsj AS datetime),CAST('1999-01-02 '+tjsj as datetime)) else DATEDIFF(minute,cast('1999-01-01 '+kjsj AS datetime),CAST('1999-01-01 '+tjsj as datetime)) end) as decimal(18,2)) from tb group by bc,gh /* BC gh avg_cha ---------- ---------- --------------------------------------- 乙 20MnK 37.25 丙 NHRB400S 26.00 丁 Q195 37.67 甲 Q195 36.75*/
bc gh kjsj tjsj
丙 NHRB400S 11:34 12:00
丁 Q195 19:18 19:54
丁 Q195 23:45 00:28
甲 Q195 01:07 01:44
甲 Q195 01:47 02:27
甲 Q195 02:30 03:04
甲 Q195 03:07 03:43
乙 20MnK 13:02 13:29
乙 20MnK 13:01 14:00
乙 20MnK 14:02 14:34
乙 20MnK 14:35 15:06 根据bc 和 gh 进行Group By 分组
其中 kjsj 和 tjsj 类型为char(10)
想要的结果是
bc gh tjsj和kjsj之间的分钟数的平均值
丙 NHRB400S 26
丁 Q195 39.5
甲 Q195 36.75
乙 20MnK 37.25
/**** 测试数据如下: ****/
/**
丙 NHRB400S 11:34 12:00
丁 Q195 19:18 19:54
丁 Q195 23:45 00:28
丁 Q195 00:30 01:04
甲 Q195 01:07 01:44
甲 Q195 01:47 02:27
甲 Q195 02:30 03:04
甲 Q195 03:07 03:43
乙 20MnK 13:02 13:29
乙 20MnK 13:01 14:00
乙 20MnK 14:02 14:34
乙 20MnK 14:35 15:06
**//**** 要查出下面的结果: ****//**
bc gh kjbj和tjsj的差值(分钟)的平均值
丙 NHRB400S 26
丁 Q195 41
甲 Q195 36.75
乙 20MnK 37.25
**/
from (
select BC,gh,chaji=case when tjsj< kjsj then DATEDIFF(minute,cast('1999-01-01 '+kjsj AS datetime),CAST('1999-01-02 '+tjsj as datetime))
else DATEDIFF(minute,cast('1999-01-01 '+kjsj AS datetime),CAST('1999-01-01 '+tjsj as datetime)) end
from tbname ) k
group by bc,gh
*************************************
* T-MAC 小编 *
* -->努力成长中 *
* -->梦想DBA *
*************************************
*/
if OBJECT_ID('tb') is not null
drop table tb
go
create table tb (bc varchar(10) , gh varchar(10), kjsj varchar(10),tjsj varchar(10))
insert tb select
'丙', 'NHRB400S', '11:34' , '12:00' union select
'丁', 'Q195' , '19:18' , '19:54' union select
'丁', 'Q195' , '23:45' , '00:28' union select
'丁', 'Q195' , '00:30' , '01:04' union select
'甲', 'Q195' , '01:07' , '01:44' union select
'甲', 'Q195' , '01:47' , '02:27' union select
'甲', 'Q195' , '02:30' , '03:04' union select
'甲', 'Q195' , '03:07' , '03:43' union select
'乙', '20MnK', '13:02' , '13:29' union select
'乙', '20MnK', '13:01' , '14:00' union select
'乙', '20MnK', '14:02' , '14:34' union select
'乙', '20MnK', '14:35' , '15:06'
goselect BC,gh,avg_cha=cast(avg(case when tjsj< kjsj then DATEDIFF(minute,cast('1999-01-01 '+kjsj AS datetime),CAST('1999-01-02 '+tjsj as datetime))
else DATEDIFF(minute,cast('1999-01-01 '+kjsj AS datetime),CAST('1999-01-01 '+tjsj as datetime)) end) as decimal(18,2))
from tb
group by bc,gh
/*
BC gh avg_cha
---------- ---------- ---------------------------------------
乙 20MnK 37.00
丙 NHRB400S 26.00
丁 Q195 37.00
甲 Q195 36.00*/
select BC,gh,avg_cha=cast(avg(1.0*case when tjsj< kjsj then DATEDIFF(minute,cast('1999-01-01 '+kjsj AS datetime),CAST('1999-01-02 '+tjsj as datetime))
else DATEDIFF(minute,cast('1999-01-01 '+kjsj AS datetime),CAST('1999-01-01 '+tjsj as datetime)) end) as decimal(18,2))
from tb
group by bc,gh
/*
BC gh avg_cha
---------- ---------- ---------------------------------------
乙 20MnK 37.25
丙 NHRB400S 26.00
丁 Q195 37.67
甲 Q195 36.75*/