SQL查询来获取最小,最大行数 [英] SQL query to get min, max rows
问题描述
我有以下样本数据,我想获取每个连续状态的最小和最大时间。
cat子状态logtime
水果苹果0 30-10-2017 06:00
水果苹果0 30-10-2017 06:03
水果苹果0 30-10-2017 06:06
水果苹果0 30-10-2017 06:09
水果苹果0 30-10-2017 06:12
水果苹果0 30-10-2017 06:15
水果苹果0 30-10 -2017 06:18
水果苹果0 30-10-2017 06:21
水果苹果0 30-10-2017 06:24
水果苹果0 30-10-2017 06:27
水果苹果0 30-10-2017 06:30
水果苹果0 30-10-2017 06:33
水果苹果0 30-10-2017 06:36
水果苹果0 30-10-2017 06:39
水果苹果0 30-10-2017 06:42
水果苹果0 30-10-2017 06:45
水果苹果0 30-10 -2017 06:48
水果苹果0 30-10-2017 06:51
水果苹果0 30-10-2017 06:54
水果苹果0 30-10-2017 06:57
fru它的草莓1 30-10-2017 06:00
水果草莓1 30-10-2017 06:03
水果草莓1 30-10-2017 06:06
水果草莓2 30- 10-2017 06:09
水果草莓1 30-10-2017 06:12
水果草莓1 30-10-2017 06:15
水果草莓1 30-10-2017 06: 18
水果草莓1 30-10-2017 06:21
水果草莓1 30-10-2017 06:24
水果草莓1 30-10-2017 06:27
水果草莓1 30-10-2017 06:30
水果草莓1 30-10-2017 06:33
水果草莓1 30-10-2017 06:36
水果草莓1 30- 10-2017 06:39
水果草莓2 30-10-2017 06:42
水果草莓1 30-10-2017 06:45
水果草莓1 30-10-2017 06: 48
水果草莓1 30-10-2017 06:51
水果草莓1 30-10-2017 06:54
水果草莓1 30-10-2017 06:57
水果蓝莓0 30-10-2017 06:00
水果蓝莓0 30-10-2017 06 :03
水果蓝莓0 30-10-2017 06:06
水果蓝莓2 30-10-2017 06:09
水果蓝莓0 30-10-2017 06:12
水果蓝莓0 30-10-2017 06:15
水果蓝莓0 30-10-2017 06:18
水果蓝莓0 30-10-2017 06:21
水果蓝莓0 30 -10-2017 06:24
水果蓝莓0 30-10-2017 06:27
水果蓝莓0 30-10-2017 06:30
水果蓝莓0 30-10-2017 06 :33
水果蓝莓0 30-10-2017 06:36
水果蓝莓0 30-10-2017 06:39
水果蓝莓2 30-10-2017 06:42
水果蓝莓0 30-10-2017 06:45
水果蓝莓0 30-10-2017 06:48
水果蓝莓0 30-10-2017 06:51
水果蓝莓0 30 -10-2017 06:54
水果蓝莓0 30-10-2017 06:57
水果guaua 1 30-10-2017 06:00
水果guaua 1 30-10-2017 06 :03
水果guaua 1 30-10-2017 06:06
水果guaua 2 30-10-2017 06:09
水果guaua 1 30-10-2017 06:12
水果guaua 1 30-10-2017 06:15
水果guaua 1 30-10-2017 06:18
水果guaua 1 30-10-2017 06:21
果子guaua 1 30-10-2017 06:24
果子guaua 1 30-10-2017 06:27
果子guaua 1 30-10 -2017 06:30
水果guaua 1 30-10-2017 06:33
水果guaua 1 30-10-2017 06:36
水果guaua 1 30-10-2017 06:39
水果guaua 2 30-10-2017 06:42
水果guaua 1 30-10-2017 06:45
水果guaua 1 30-10-2017 06:48
水果guaua 1 30-10-2017 06:51
果子guaua 1 30-10-2017 06:54
果子guaua 1 30-10-2017 06:57
我试图获得cat和subcat状态下每个状态的最小和最大时间。
输出结果应该如下:
cat子状态状态mintime maxtime
fruits apple 0 30-10-2017 06 :00 30-10-2017 06:57
水果草莓1 3 0-10-2017 06:00 30-10-2017 06:06
水果草莓2 2 30-10-2017 06:09 30-10-2017 06:09
水果草莓1 30-10- 2017 06:12 30-10-2017 06:39
水果草莓2 30-10-2017 06:42 30-10-2017 06:42
水果草莓1 30-10-2017 06:45 30-10-2017 06:57
水果蓝莓0 30-10-2017 06:00 30-10-2017 06:06
水果蓝莓2 30-10-2017 06:09 30-10- 2017 06:09
水果蓝莓0 30-10-2017 06:12 30-10-2017 06:39
水果蓝莓2 30-10-2017 06:42 30-10-2017 06:42
水果蓝莓0 30-10-2017 06:45 30-10-2017 06:57
水果guaua 1 30-10-2017 06:00 30-10-2017 06:06
水果guaua 2 30-10-2017 06:09 30-10-2017 06:09
水果guaua 1 30-10-2017 06:12 30-10-2017 06:39
水果guaua 2 30 -10-2017 06:42 30-10-2017 06:42
fruits guaua 1 30-10-2017 06:45 30-10-2017 06:57
尝试过与下面的SQL查询,但无法得到上面的输出,而不是它的最小值,最大总小时。
SELECT cat,
subcat,
status,
min(logtime)作为最小日志,
max(logtime)作为maxlog
FROM fruits_log
WHERE
cat ='fruits'
AND date_format(logtime,'%Y-%m-%d%H)='2017-10-30 06'
AND状态IN('0','1','2')
GROUP BY cat,subcat,date_format(logtime,'%Y-%m-%d%H),status
ORDER BY cat,subcat,minlog;
请帮助我在上面的查询中缺少的内容
解决方案您可以尝试类似这样的方法
select t.cat,t .subcat,t.status,min(t.logdate),max(t.logdate)
from
(
select t1.cat,t1.subcat,t1.status,t1.logdate ,从数据t1开始计数(t2.cat)grpnum
在t1.cat = t2.cat和t1.subcat = t2.subcat和
上保留加入数据t2 t1.status!= t2 .status和
t1.logdate< t2.logdate
group by t1.cat,t1.subcat,t1.status,t1.logdate
)t
group by t。 cat,t.subcat,t.status,t.grpnum
我已经在很小的<您的数据的示例,但似乎没有问题。内部查询使用自连接来隔离具有相同
cat
,subcat
和状态
值。一旦准备就绪,您可以简单地通过组为数据中的每组行执行一个组。
I have following sample data, I want to get min and max time of every consecutive status.
cat subcat status logtime fruits apple 0 30-10-2017 06:00 fruits apple 0 30-10-2017 06:03 fruits apple 0 30-10-2017 06:06 fruits apple 0 30-10-2017 06:09 fruits apple 0 30-10-2017 06:12 fruits apple 0 30-10-2017 06:15 fruits apple 0 30-10-2017 06:18 fruits apple 0 30-10-2017 06:21 fruits apple 0 30-10-2017 06:24 fruits apple 0 30-10-2017 06:27 fruits apple 0 30-10-2017 06:30 fruits apple 0 30-10-2017 06:33 fruits apple 0 30-10-2017 06:36 fruits apple 0 30-10-2017 06:39 fruits apple 0 30-10-2017 06:42 fruits apple 0 30-10-2017 06:45 fruits apple 0 30-10-2017 06:48 fruits apple 0 30-10-2017 06:51 fruits apple 0 30-10-2017 06:54 fruits apple 0 30-10-2017 06:57 fruits strawberry 1 30-10-2017 06:00 fruits strawberry 1 30-10-2017 06:03 fruits strawberry 1 30-10-2017 06:06 fruits strawberry 2 30-10-2017 06:09 fruits strawberry 1 30-10-2017 06:12 fruits strawberry 1 30-10-2017 06:15 fruits strawberry 1 30-10-2017 06:18 fruits strawberry 1 30-10-2017 06:21 fruits strawberry 1 30-10-2017 06:24 fruits strawberry 1 30-10-2017 06:27 fruits strawberry 1 30-10-2017 06:30 fruits strawberry 1 30-10-2017 06:33 fruits strawberry 1 30-10-2017 06:36 fruits strawberry 1 30-10-2017 06:39 fruits strawberry 2 30-10-2017 06:42 fruits strawberry 1 30-10-2017 06:45 fruits strawberry 1 30-10-2017 06:48 fruits strawberry 1 30-10-2017 06:51 fruits strawberry 1 30-10-2017 06:54 fruits strawberry 1 30-10-2017 06:57 fruits blueberry 0 30-10-2017 06:00 fruits blueberry 0 30-10-2017 06:03 fruits blueberry 0 30-10-2017 06:06 fruits blueberry 2 30-10-2017 06:09 fruits blueberry 0 30-10-2017 06:12 fruits blueberry 0 30-10-2017 06:15 fruits blueberry 0 30-10-2017 06:18 fruits blueberry 0 30-10-2017 06:21 fruits blueberry 0 30-10-2017 06:24 fruits blueberry 0 30-10-2017 06:27 fruits blueberry 0 30-10-2017 06:30 fruits blueberry 0 30-10-2017 06:33 fruits blueberry 0 30-10-2017 06:36 fruits blueberry 0 30-10-2017 06:39 fruits blueberry 2 30-10-2017 06:42 fruits blueberry 0 30-10-2017 06:45 fruits blueberry 0 30-10-2017 06:48 fruits blueberry 0 30-10-2017 06:51 fruits blueberry 0 30-10-2017 06:54 fruits blueberry 0 30-10-2017 06:57 fruits guaua 1 30-10-2017 06:00 fruits guaua 1 30-10-2017 06:03 fruits guaua 1 30-10-2017 06:06 fruits guaua 2 30-10-2017 06:09 fruits guaua 1 30-10-2017 06:12 fruits guaua 1 30-10-2017 06:15 fruits guaua 1 30-10-2017 06:18 fruits guaua 1 30-10-2017 06:21 fruits guaua 1 30-10-2017 06:24 fruits guaua 1 30-10-2017 06:27 fruits guaua 1 30-10-2017 06:30 fruits guaua 1 30-10-2017 06:33 fruits guaua 1 30-10-2017 06:36 fruits guaua 1 30-10-2017 06:39 fruits guaua 2 30-10-2017 06:42 fruits guaua 1 30-10-2017 06:45 fruits guaua 1 30-10-2017 06:48 fruits guaua 1 30-10-2017 06:51 fruits guaua 1 30-10-2017 06:54 fruits guaua 1 30-10-2017 06:57
I'm trying get min, max time of each status for cat and subcat status.
Output result should be following:
cat subcat status mintime maxtime fruits apple 0 30-10-2017 06:00 30-10-2017 06:57 fruits strawberry 1 30-10-2017 06:00 30-10-2017 06:06 fruits strawberry 2 30-10-2017 06:09 30-10-2017 06:09 fruits strawberry 1 30-10-2017 06:12 30-10-2017 06:39 fruits strawberry 2 30-10-2017 06:42 30-10-2017 06:42 fruits strawberry 1 30-10-2017 06:45 30-10-2017 06:57 fruits blueberry 0 30-10-2017 06:00 30-10-2017 06:06 fruits blueberry 2 30-10-2017 06:09 30-10-2017 06:09 fruits blueberry 0 30-10-2017 06:12 30-10-2017 06:39 fruits blueberry 2 30-10-2017 06:42 30-10-2017 06:42 fruits blueberry 0 30-10-2017 06:45 30-10-2017 06:57 fruits guaua 1 30-10-2017 06:00 30-10-2017 06:06 fruits guaua 2 30-10-2017 06:09 30-10-2017 06:09 fruits guaua 1 30-10-2017 06:12 30-10-2017 06:39 fruits guaua 2 30-10-2017 06:42 30-10-2017 06:42 fruits guaua 1 30-10-2017 06:45 30-10-2017 06:57
tried with following sql query but unable to get above output instead its giving min, max total hour.
SELECT cat, subcat, status, min(logtime) as minlog, max(logtime) as maxlog FROM fruits_log WHERE cat = 'fruits' AND date_format(logtime,'%Y-%m-%d %H) = '2017-10-30 06' AND status IN ('0','1','2') GROUP BY cat,subcat,date_format(logtime,'%Y-%m-%d %H),status ORDER BY cat,subcat,minlog ;
Please help what i'm missing in above query
解决方案You may try something like this
select t.cat, t.subcat, t.status, min(t.logdate), max(t.logdate) from ( select t1.cat, t1.subcat, t1.status, t1.logdate, count(t2.cat) grpnum from data t1 left join data t2 on t1.cat = t2.cat and t1.subcat = t2.subcat and t1.status != t2.status and t1.logdate < t2.logdate group by t1.cat, t1.subcat, t1.status, t1.logdate ) t group by t.cat, t.subcat, t.status, t.grpnum
I have tested on just very small sample of your data, but it seems to be ok. The inner query use a self-join to isolate consecutive rows with the same
cat
,subcat
andstatus
values. Once this is ready, you can simply do agroup by
for each group of rows in the data.这篇关于SQL查询来获取最小,最大行数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!