Mysql 去重取最近一条数据

背景：一个秒杀活动对应多个活动场次，需要通过sql语句查询出最接近当前时间的活动场次

SELECT
	a.id activityId,
	a. NAME activityName,
	s.validity_start_time activityStartTime,
	s.validity_end_time activityEndTime
FROM
	activity a,
	activity_session s,
	activity_rel_session ars
WHERE
	a.id = ars.activity_id
AND ars.activity_session_id = s.id
AND s.validity_end_time > now()
AND s.master_org_id = 21332293952180224
AND s.org_id = 21363336000278528 ORDER BY
	a.id DESC,
	s.validity_start_time ASC; 

activity ：秒杀活动 表  activity_session ：活动场次表 activity_rel_session ：活动场次关联表

 当前时间为16：00需要去重取出18：00这个场次数据

SELECT
	*
FROM
	(
		SELECT
			*,
		IF (
			a.activityId =@activityId,
			1,
			0
		) AS num,
		@activityId := a.activityId
	FROM
		(
			SELECT
				a.id activityId,
				a. NAME activityName,
				s.validity_start_time activityStartTime,
				s.validity_end_time activityEndTime
			FROM
				activity a,
				activity_session s,
				activity_rel_session ars
			WHERE
				a.id = ars.activity_id
			AND ars.activity_session_id = s.id
			AND s.validity_end_time > now()
			AND s.master_org_id = 21332293952180224
			AND s.org_id = 21363336000278528
			ORDER BY
				a.id DESC,
				s.validity_start_time ASC
		) a
	) t
WHERE
	t.num = 0;

实现过程：查询结果按照活动ID,有效期开始时间排序，将查询结果当作子表，定义变量@activityId，当查询结果活动ID与变量相等num记为1反之记为0，每行数据再为@activityId变量赋值。

看上去打完收工，当事实结果并不不是想象得那么美好。第一次执行结果num都是0，第二次再执行结果就是对的。去掉tt.num=0条件查看执行结果：

SELECT
	*
FROM
	(
		SELECT
			*,@activityId,
		IF (
			a.activityId =@activityId,
			1,
			0
		) AS num,
		@activityId := a.activityId
	FROM
		(
			SELECT
				a.id activityId,
				a. NAME activityName,
				s.validity_start_time activityStartTime,
				s.validity_end_time activityEndTime
			FROM
				activity a,
				activity_session s,
				activity_rel_session ars
			WHERE
				a.id = ars.activity_id
			AND ars.activity_session_id = s.id
			AND s.validity_end_time > now()
			AND s.master_org_id = 21332293952180224
			AND s.org_id = 21363336000278528
			ORDER BY
				a.id DESC,
				s.validity_start_time ASC
		) a
	) t

执行结果：

 发现@activityId变量每行都是null，@activityId := a.activityId每行赋值不起作用。

再次执行结果：

发现@activityId变量值正确，num也是正常。问题出在@activityId在执行sql之前没有先申明。正常情况每次执行查询sql语句时都需要将@activityId初始化。SET @activityId=1;

完整的SQL

SET @activityId=1;
SELECT
	*
FROM
	(
		SELECT
			*,@activityId,
		IF (
			a.activityId =@activityId,
			1,
			0
		) AS num,
		@activityId := a.activityId
	FROM
		(
			SELECT
				a.id activityId,
				a. NAME activityName,
				s.validity_start_time activityStartTime,
				s.validity_end_time activityEndTime
			FROM
				activity a,
				activity_session s,
				activity_rel_session ars
			WHERE
				a.id = ars.activity_id
			AND ars.activity_session_id = s.id
			AND s.validity_end_time > now()
			AND s.master_org_id = 21332293952180224
			AND s.org_id = 21363336000278528
			ORDER BY
				a.id DESC,
				s.validity_start_time ASC
		) a
	) t;