MySQL同表相邻数据查询或计算（用户下相邻订单的时间差举例） |
您所在的位置：网站首页 › mysql查询一个字段有两条以上的记录怎么查 › MySQL同表相邻数据查询或计算（用户下相邻订单的时间差举例） |
|
这里主要介绍一下，在一张数据表下对相邻的数据进行一个相关查询和计算； 拿一个在电商中最常见的情况，计算一下用户首单和第二单的时间间隔这样的数据来举例，如下： idcustomer_idcreated_at112017-07-21 09:43:022122017-07-25 11:37:483102017-07-25 11:43:41412017-07-27 01:27:225102017-07-27 07:46:45612017-07-27 10:21:377122017-07-27 13:26:19查询用户首单和第二单的时间间隔： SELECT m.customer_id, sfo.created_at as '首单时间', m.created_at as '第二单时间', (unix_timestamp(m.created_at) - unix_timestamp(sfo.created_at))/86400 as '两单相差天数' FROM sales_flat_order m LEFT JOIN sales_flat_order sfo on m.customer_id = sfo.customer_id and sfo.created_at n.created_at ) = 1 GROUP BY m.customer_id查询结果是： customer_id首单时间第二单时间两单时间差12017-07-21 09:43:022017-07-27 01:27:225.6558122017-07-25 11:37:482017-07-27 13:26:192.0754102017-07-25 11:43:412017-07-27 07:46:451.8355整个原理如下： 将一张表查询两次得到两组数据，分别为别名m和别名n的两组数据；以m为主，用n的数据和m的数据作对比，通过created_at的判断过滤掉一些无用数据；使用count()函数统计满足条件的数据个数；统计数为1时说明n表中比m表中时间小的只有1条，m中的该条数据也就是该用户下的第二笔订单；通过LEFT JOIN联表，通过created_at找到比第二单更早的一单也就是用户的首单；利用unix_timestamp把得到的两条数据的created_at做差，得到了两笔订单的时间间隔；下面做了一下拓展，可以查询任意相连的两笔订单的时间间隔： SELECT m.customer_id, m.created_at as '后一单时间', SUBSTRING_INDEX( GROUP_CONCAT(sfo.created_at ORDER BY sfo.created_at DESC), ',', 1 ) as '前一单时间', (unix_timestamp(m.created_at) - unix_timestamp( SUBSTRING_INDEX( GROUP_CONCAT(sfo.created_at ORDER BY sfo.created_at DESC), ',', 1 ) ))/86400 as '两单相差天数' FROM sales_flat_order m LEFT JOIN sales_flat_order sfo on m.customer_id = sfo.customer_id and sfo.created_at n.created_at ) = 2 GROUP BY m.customer_id;得到数据如下： customer_id后一单时间前一单时间两单时间差12017-07-27 10:21:372017-07-27 01:27:220.3710这里判断的是统计数为2的，也就是用户的第二单和第三单的时间间隔计算，因为用户10和12只有两单所以结果中无这两个用户； 整个原理如下： 将一张表查询两次得到两组数据，分别为别名m和别名n的两组数据；以m为主，用n的数据和m的数据作对比，通过created_at的判断过滤掉一些无用数据；使用count()函数统计满足条件的数据个数；筛选之后m中得到的是第三笔订单；通过LEFT JOIN联表，通过created_at找到比第三笔订单时间早的订单，这里会从sfo中得到两笔订单；利用GROUP_CONCAT函数每组订单中各得到的两笔订单利用created_at进行降序排序，然后得到通过‘，’连接的两条数据的时间，如下： 2017-07-27 01:27:22,2017-07-21 09:43:02 使用SUBSTRING_INDEX函数通过’，'将数据拆分再拿到第一条数据，也就是第二笔订单的时间了；利用unix_timestamp对created_at作差，得到两笔订单的时间间隔；这只是我想到的应对这种场景通过SQL语句进行查询的方法，如果有小伙伴有更好的方法或者改进的方法，欢迎小伙伴留言，谢谢啦！ |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |