聚合函数应用

Question

表结构如下：ID P_Name            Beg_Time End_Time    Ins_Row State
64 PAOS_Trim_regs 2010/11/15 08:13 2010/11/15 08:13 5000 FALSE
66 PAOS_Trim_regs 2010/11/15 08:18 2010/11/15 08:18 5000 FALSE
68 PAOS_Trim_regs 2010/11/15 08:23 2010/11/15 08:23 5000 FALSE
70 PAOS_qc_inspitems 2010/11/15 08:26 2010/11/15 08:26 5000 FALSE
72 PAOS_proc_items 2010/11/15 08:26 2010/11/15 08:27 5000 FALSE
73 PAOS_Trim_regs 2010/11/15 08:28 2010/11/15 08:28 5000 FALSE
76 PAOS_proc_items 2010/11/15 08:31 2010/11/15 08:32 5000 FALSE
79 PAOS_proc_timeregc 2010/11/15 08:33 2010/11/15 08:34 5000 FALSE
82 PAOS_proc_timeregs 2010/11/15 08:36 2010/11/15 08:36 2 FALSE
84 PAOS_proc_timeregc 2010/11/15 08:38 2010/11/15 08:39 5000 FALSE
85 PAOS_qc_inspitems 2010/11/15 08:41 2010/11/15 08:41 5000 FALSE
87 PAOS_proc_timeregs 2010/11/15 08:41 2010/11/15 08:41 1 FALSE
88 PAOS_Trim_regs 2010/11/15 08:43 2010/11/15 08:43 5000 FALSE
90 PAOS_qc_inspitems 2010/11/15 08:46 2010/11/15 08:46 5000 FALSE
91 PAOS_proc_timeregs 2010/11/15 08:46 2010/11/15 08:46 1 FALSEselect  p_name,
min(datediff(s,beg_Time,end_time)) as 最小运行间隔,
max(datediff(s,beg_Time,end_time)) as 最大运行间隔,
avg(datediff(s,beg_Time,end_time)) as 平均运行间隔,
max(CONVERT(varchar(20),end_time,120))from ins_log  ---我想让这一行显示，最大运行间隔的end_time 或beg_time
group by p_name

Answer 1

select t.p_name,
min(datediff(s,t.beg_Time,t.end_time)) as 最小运行间隔,
max(datediff(s,t.beg_Time,t.end_time)) as 最大运行间隔,
avg(datediff(s,t.beg_Time,t.end_time)) as 平均运行间隔,
CONVERT(varchar(20),t.end_time,120) as 最大运行间隔的end_time 
from ins_log t
where datediff(s,t.beg_Time,t.end_time)=
(select max(datediff(s,beg_Time,end_time)) from ins_log where p_name=t.p_name)
group by p_name,CONVERT(varchar(20),t.end_time,120)

Answer 2

select t.p_name,
min(datediff(s,t.beg_Time,t.end_time)) as 最小运行间隔,
max(datediff(s,t.beg_Time,t.end_time)) as 最大运行间隔,
avg(datediff(s,t.beg_Time,t.end_time)) as 平均运行间隔,
CONVERT(varchar(20),tt.end_time,120) as 最大运行间隔的end_time 
from ins_log t
left join ins_log tt on t.p_name=tt.p_name
where datediff(s,tt.beg_Time,tt.end_time)=(select max(datediff(s,beg_Time,end_time)) from ins_log where p_name=tt.p_name)
group by p_name,CONVERT(varchar(20),tt.end_time,120)

Answer 3

--try
select p_name,
min(datediff(s,beg_Time,end_time)) as 最小运行间隔,
max(datediff(s,beg_Time,end_time)) as 最大运行间隔,
avg(datediff(s,beg_Time,end_time)) as 平均运行间隔,
max(end_time))from ins_log ---我想让这一行显示，最大运行间隔的end_time 或beg_time
group by p_name

Answer 4

select t.p_name,
min(datediff(s,t.beg_Time,t.end_time)) as 最小运行间隔,
max(datediff(s,t.beg_Time,t.end_time)) as 最大运行间隔,
avg(datediff(s,t.beg_Time,t.end_time)) as 平均运行间隔,
max(case when datediff(s,t.beg_Time,t.end_time)=
(select max(datediff(s,beg_Time,end_time)) from ins_log where p_name=t.p_name) then t.end_time else '' end ) as 最大运行间隔的end_time 
from ins_log t
group by p_name