MySQL查询指定用户的当前排行

说明

当前表为记录顾客体重表，每日记录。

需求查询每日减重排行

前后两天体重差的排行（直接的排行同理更简单）

思路

1. 字表查询今天和昨天都有记录了体重的用户，取得用户ID（虽然用两次这里就不建立视图了）
2. 分别查出昨天和今天的对比数据，建立子表
3. 数据对比，得到体重差，并且排序，建立子表【未排行集合】
4. 加入行编号的数据，生成子表 【order by会在自增后才执行，只能嵌套，不知道为什么】
5. 最外层，加入customer_id条件，得到自增变量为需求结果。。

---

表结构

CREATE TABLE `ims_qiahoo_customer_weight_scale` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) DEFAULT NULL COMMENT '用户id',
  `weight` decimal(11,2) DEFAULT NULL COMMENT '体重',
  `log_date` date DEFAULT NULL COMMENT '记录日期',
  `update_time` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `用户` (`customer_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

查询语句

例如查询 customer_id 为 27811的当前排行为

-- SET @rowNum := 0;
SELECT
	* 
FROM
	(
	SELECT
		*,
		( @rowNum := @rowNum + 1 ) AS rowNo 
	FROM
		(
		SELECT
			`今天`.create_time,
			`昨天`.customer_id,
			`今天`.weight - `昨天`.weight AS `体重差` 
		FROM
			(
			SELECT DISTINCT
				customer_id,
				weight,
				log_date,
				create_time 
			FROM
				ims_qiahoo_customer_weight_scale 
			WHERE
				( `log_date` = DATE_SUB( curdate( ), INTERVAL 1 DAY ) ) 
				AND customer_id IN (
				SELECT
					customer_id 
				FROM
					ims_qiahoo_customer_weight_scale 
				WHERE
					( `log_date` = DATE_SUB( curdate( ), INTERVAL 1 DAY ) OR `log_date` = DATE_SUB( curdate( ), INTERVAL 0 DAY ) ) 
				GROUP BY
					customer_id 
				HAVING
					count( customer_id ) > 1 
				) 
			GROUP BY
				customer_id 
			ORDER BY
				customer_id DESC 
			) AS `昨天`,
			(
			SELECT DISTINCT
				customer_id,
				weight,
				log_date,
				create_time 
			FROM
				ims_qiahoo_customer_weight_scale 
			WHERE
				( `log_date` = DATE_SUB( curdate( ), INTERVAL 0 DAY ) ) 
				AND customer_id IN (
				SELECT
					customer_id 
				FROM
					ims_qiahoo_customer_weight_scale 
				WHERE
					( `log_date` = DATE_SUB( curdate( ), INTERVAL 1 DAY ) OR `log_date` = DATE_SUB( curdate( ), INTERVAL 0 DAY ) ) 
				GROUP BY
					customer_id 
				HAVING
					count( customer_id ) > 1 
				) 
			GROUP BY
				customer_id 
			ORDER BY
				customer_id ASC 
			) AS `今天` 
		WHERE
			`昨天`.customer_id = `今天`.customer_id 
		ORDER BY
			`体重差` ASC,
			`今天`.create_time ASC  
		) AS `未排行集合`,
		( SELECT ( @rowNum := 0 ) ) l 
	) AS `已排行结果` 
WHERE
	customer_id = 27811

总结

SQL写的很复杂。子表嵌套太多。希望有大神可以给优化一下。感激不尽。。