mysql如何通过当前排序字段获取相邻数据项

mysql如何通过当前排序字段获取相邻数据项,第1张

mysql如何通过当前排序字段获取相邻数据项 目录
  • 通过当前排序字段获取相邻数据项
    • 1.业务场景
    • 2.思路
    • 3.sql
  • 同表相邻数据查询或计算
    • 用户下相邻订单的时间差举例

通过当前排序字段获取相邻数据项

1.业务场景

(1)需要专门以一个弹窗页面展示一项数据的所有字段值.其中一些字段值长度较大。

(2)能够左右切换上一项下一项数据

(3)存在可排序的字段,如以id进行排序

2.思路

2.1 sql

1>查询前一项,查询小于当前id的项逆序取第一个

2>查询后一项,查询大于当前id的项正序取第一个

3>连接两项结果

2.2 页面逻辑

(1)在展示当前项时获取好两相邻的数据,在做切换时直接填充数据

(2)切换数据展示时同样再次获取当前项两相邻数据

以此(1)(2)往复

3.sql

例:查询id为40两相邻的数据

( SELECT * FROM [表名] WHERE id < 40 ORDER BY id DESC LIMIT 1 ) UNION
(
    SELECT
        * 
    FROM
        [表名]
    WHERE
        id > 40 
    ORDER BY
        id 
    LIMIT 1
)

同表相邻数据查询或计算

用户下相邻订单的时间差举例

这里主要介绍一下,在一张数据表下对相邻的数据进行一个相关查询和计算;

拿一个在电商中最常见的情况,计算一下用户首单和第二单的时间间隔这样的数据来举例,如下:

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 < m.created_at
WHERE
	(
		SELECT
			count(*)
		FROM
			sales_flat_order n
		WHERE
			m.customer_id = n.customer_id
			AND m.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 < m.created_at
WHERE
	(
		SELECT
			count(*)
		FROM
			sales_flat_order n
		WHERE
			m.customer_id = n.customer_id
			AND m.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语句进行查询的方法。 

以上为个人经验,希望能给大家一个参考,也希望大家多多支持脚本之家。

欢迎分享,转载请注明来源:内存溢出

原文地址:https://outofmemory.cn/sjk/2996844.html

(0)
打赏 微信扫一扫微信扫一扫 支付宝扫一扫支付宝扫一扫
上一篇 2022-09-26
下一篇2022-09-26

随机推荐

  • 敦伦尽分是什么意思

    敦伦尽分的意思是:思想行为要完全以崇敬的心理,遵守社会的伦理道德,尽职尽责地主动承担好自己本份的义务。伦理道德是我们的行为轨范,该作的一定要去作,不该作的一定要坚决不犯。法律只是伦理道德的最低底线,触

    2022-12-08
    000
  • 冬至长夏至短是什么意思

    冬至长夏至短是什么意思

    2022-12-08
    000
  • 销声匿迹的意思

    销声匿迹的意思

    2022-12-08
    000
  • 越俎代庖的意思

    越俎代庖的意思是:越:跨过;俎:古代祭祀时摆祭品的礼器;庖:厨师。主祭的人跨过礼器去代替厨师办席。比喻超出自己业务范围去处理别人所管的事。详细解释:1.【解释】:越:跨过;俎:古代祭祀时摆祭品的礼器;

    2022-12-08
    000
  • 原神棋谭哪关怪最多

    原神机关棋谭怪最多的是第七关。《原神》是由上海米哈游制作发行的一款开放世界冒险游戏,游戏发生在一个被称作“提瓦特”的幻想世界,在这里,被神选中的人将被授予“神之眼”,导引元素之力。玩家将扮演一位名为“

    2022-12-08
    000
  • 地段医院国庆节放假几号开诊

    在国庆节期间医院也是属于放假的,而不是正常的上班。不过因为医院是属于一个比较特殊的部门,所以在国庆期间虽然说是放假,但是并不代表医院就是没有医生和护士的存在的。为了保证假期期间人民群众的看病就医问题,

    2022-12-08
    000
  • 第一次世界粮食调查是在哪一年

    第一次世界粮食调查是在1946年。内容拓展:1946年的“第一次世界粮食调查”,以第二次世界大战前的1935~1939年的70个国家(占世界总人口的90%)为对象,按“每天平均摄取热量低于2250卡界

    2022-12-08
    000
  • 丰收指的什么生肖

    丰收指的是生肖羊。羊是一种温驯动物,与世无争,也没有战斗力,只求青草过日子即可,所以,都是别的动物来攻击它。在羊群中,会服从领导的羊,它们尊敬领袖,相当服从,因此耶苏曾把自己喻为善良的牧羊人。在中国的

    2022-12-08
    000
  • 考国家公务员需要什么学历

    国家公务员考试要求(一)具有中华人民共和国国籍;(二)18周岁以上、35周岁以下,应届硕士研究生和博士研究生(非在职)人员年龄可放宽到40周岁以下;(三)拥护中华人民共和国宪法,拥护中国共产党领导和社

    2022-12-08
    000

发表评论

登录后才能评论

评论列表(0条)

    保存