如何从不同行的mysql中的表中获取最小最大和平均数? [英] How to get min max and avg from table in mysql with different rows?
本文介绍了如何从不同行的mysql中的表中获取最小最大和平均数?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一个表，下面有记录.我想要的是单选择查询，该查询按媒体ID显示第一条记录的最小记录，第二条记录的平均记录和第三条记录的最大值?
i have a table with record given below. What i want is single select query which shows first record min second record avg and third record max value group by mediaid?
trans_id playtime mediaid(F.K.)
-------- -------- -------------
1 32 2
2 65 2
3 45 3
4 55 4
5 60 3
6 78 2
7 45 4
8 35 3
结果应类似于
playtime mediaid(F.K.)
-------- -------------
32 2 (Min value)
58.33 2 (avg value)
78 2 (Max value)
35 3 (Min value)
46.66 3 (avg value)
60 3 (Max value)
45 4 (Min value)
50 4 (avg value)
55 4 (max value)
这是我给出结果的真实查询，但这不是按标题排列的
this is my real query giving the result but this is not arranged by medtitle
select t.medTitle as 'Media',
min(TIMESTAMPDIFF(SECOND,s.dmtViewStart,s.dmtViewEnd))platime
from dbedetailing.tblmedia t
left outer join dbedetailing.tbldoctormediatracktest s on s.dmtMedId = t.medId
GROUP BY t.medTitle
union all
select t.medTitle as 'Media',
avg(TIMESTAMPDIFF(SECOND,s.dmtViewStart,s.dmtViewEnd))platime
from dbedetailing.tblmedia t
left outer join dbedetailing.tbldoctormediatracktest s on s.dmtMedId = t.medId
GROUP BY t.medTitle
union all
select t.medTitle as 'Media',
max(TIMESTAMPDIFF(SECOND,s.dmtViewStart,s.dmtViewEnd))platime
from dbedetailing.tblmedia t
left outer join dbedetailing.tbldoctormediatracktest s on s.dmtMedId = t.medId
GROUP BY t.medTitle ;
这是我给出结果的真实查询，但这不是按标题排列的
this is my real query giving the result but this is not arranged by medtitle
===数据库dbedetailing
===Database dbedetailing
==表tbldoctormediatracktest的表结构
== Table structure for table tbldoctormediatracktest
|------
|Column|Type|Null|Default
|------
|//**dmtId**//|int(10)|No|
|dmtDocId|int(10)|No|
|dmtMedId|int(10)|No|
|dmtViewingDate|datetime|No|
|dmtViewStart|datetime|Yes|NULL
|dmtViewEnd|datetime|Yes|NULL
|dmaIsActive|enum('0', '1')|No|
|insDate|datetime|No|
|modDate|datetime|No|
== Dumping data for table tbldoctormediatracktest
Medid dmtViewStart dmtViewEnd
|56|2012-08-24 11:41:14|2012-08-24 11:41:20
|52|2012-08-24 11:41:24|2012-08-24 11:42:12
|56|2012-08-24 11:41:14|2012-08-24 11:41:20
|52|2012-08-24 11:41:24|2012-08-24 11:42:00
|53|2012-08-25 12:32:27|2012-08-25 12:32:42
|55|2012-08-27 06:48:51|2012-08-27 06:49:19
|53|2012-08-27 06:49:25|2012-08-27 06:49:42
|53|2012-08-28 02:56:16|2012-08-28 02:56:47
|54|2012-08-28 02:57:58|2012-08-28 02:58:10
|52|2012-08-28 05:48:14|2012-08-28 05:49:03
|55|2012-08-28 07:16:00|2012-08-28 07:17:11
|52|2012-08-22 04:03:00|2012-08-22 04:03:55
|56|2012-08-28 07:16:00|2012-08-28 07:17:22
|56|2012-08-23 09:00:00|2012-08-23 09:02:00
|53|2012-08-16 04:00:00|2012-08-16 04:01:01
|54|2012-08-20 12:33:00|2012-08-20 12:34:14
|55|2012-08-16 04:00:20|2012-08-16 04:01:01
|52|2012-08-20 12:31:00|2012-08-20 12:31:14
|54|2012-08-20 12:33:14|2012-08-20 12:34:14
|55|2012-08-20 12:48:00|2012-08-20 12:49:14
|56|2012-08-20 12:33:00|2012-08-20 12:34:14
|53|2012-08-20 12:43:00|2012-08-20 12:44:14
|54|2012-08-20 12:33:00|2012-08-20 12:34:45
|55|2012-08-20 12:33:00|2012-08-20 12:34:34
|56|2012-08-20 12:33:00|2012-08-20 12:34:23
|52|2012-08-20 12:33:00|2012-08-20 12:34:18
|53|2012-08-20 12:33:00|2012-08-20 12:34:56
|54|2012-08-20 12:33:00|2012-08-20 12:33:56
|56|2012-08-20 12:33:00|2012-08-20 12:33:14
|56|2012-08-20 12:33:00|2012-08-20 12:34:14
===Database dbedetailing
== Table structure for table tblmedia
|------
|Column|Type|Null|Default
|------
|//**medId**//|int(10)|No|
|medTitle|varchar(250)|No|
|medDesc|varchar(250)|No|
|medPath|varchar(250)|No|
|medType|int(10)|No|
|medSpeciality|int(10)|No|
|medProduct|int(10)|No|
|medIsActive|enum('0', '1')|No|
|insDate|datetime|No|
|modDate|datetime|No|
== Dumping data for table tblmedia
===数据库dbedetailing
===Database dbedetailing
== Dumping data for table tblmedia
|51|Tplanin
|52|Casfung
|53|Knee Surgery
|54|Doriglen
|55|Colymonas
|56|inem
|57|Casfungtest
推荐答案
如果您希望将值放在一行中，则可以执行以下操作:
If you want the values in a single row, you can do this:
select max(playtime), min(playtime), avg(playtime)
from yourtable
group by mediaid;
如果要在单独的行中使用它们，请使用UNION ALL:
If you want them in separate rows, use a UNION ALL:
select max(playtime) playtime, mediaid, 'Max' Type
from yourtable
group by mediaid
union all
select min(playtime) playtime, mediaid, 'Min'
from yourtable
group by mediaid
union all
select avg(playtime) playtime, mediaid, 'Avg'
from yourtable
group by mediaid
order by mediaid, playtime;
请参见带有演示的SQL提琴
使用添加的示例数据编辑#1:
see SQL Fiddle with Demo
Edit #1, using your sample data added:
select m.medTitle,
min(TIMESTAMPDIFF(SECOND, s.dmtViewStart, s.dmtViewEnd)) playtime
from tbldoctormediatracktest s
left join tblmedia m
on s.MedId = m.MedId
group by m.medTitle
union all
select m.medTitle,
avg(TIMESTAMPDIFF(SECOND, s.dmtViewStart, s.dmtViewEnd)) playtime
from tbldoctormediatracktest s
left join tblmedia m
on s.MedId = m.MedId
group by m.medTitle
union all
select m.medTitle,
max(TIMESTAMPDIFF(SECOND, s.dmtViewStart, s.dmtViewEnd)) playtime
from tbldoctormediatracktest s
left join tblmedia m
on s.MedId = m.MedId
group by m.medTitle
order by medTitle, playtime
这篇关于如何从不同行的mysql中的表中获取最小最大和平均数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
