SQL查询来获取最小，最大行数 [英] SQL query to get min, max rows
本文介绍了SQL查询来获取最小，最大行数的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有以下样本数据，我想获取每个连续状态的最小和最大时间。
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:57I'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:57tried 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.grpnumI 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,subcatandstatusvalues. Once this is ready, you can simply do agroup byfor each group of rows in the data.这篇关于SQL查询来获取最小，最大行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
