MySQL查询获取字段特定值所在行数据

最新推荐文章于 2024-06-18 17:28:10 发布

吾道孤矣

最新推荐文章于 2024-06-18 17:28:10 发布

阅读量5.8k

点赞数 1

分类专栏： MySQL 文章标签： MySQL 查询最大值所在行的数据

本文链接：https://blog.csdn.net/z394642048/article/details/85322133

版权

MySQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

例如，当topicId相同时，查询bookStatus为2所在行的数据，若不存在，则查询bookStatus为1中missionId最大所在行的数据。

SELECT
	rm.id missionId,
	rm.cover,
	rm.mission_type bookStatus,
	rm.`name`,
	DATE_FORMAT( rm.end_time, '%Y-%m-%d %H:%i:%s' ) endTime,
	rm.topic_id topicId,
	rm.create_user_id createUserId
FROM
	re_mission_0 rm 
WHERE
	(
	( rm.class_id = 1121681 AND rm.mission_type = 2 ) 
	OR ( rm.create_user_id = 63878210 AND rm.mission_type = 1 ) 
	) 
	AND rm.publish_status IN ( 1, 3 ) 
	AND rm.fid = 7179

结果：

首先要按bookStatus倒叙，其次按missionId倒叙。修改为：

SELECT
	* 
FROM
	(
SELECT
	rm.id missionId,
	rm.cover,
	rm.mission_type bookStatus,
	rm.`name`,
	DATE_FORMAT( rm.end_time, '%Y-%m-%d %H:%i:%s' ) endTime,
	rm.topic_id topicId,
	rm.create_user_id createUserId,
	1 missionStatus,
	rr.`level` 
FROM
	re_mission_0 rm
	JOIN re_res rr ON rr.fanya_res_id = rm.topic_id 
WHERE
	(
	( rm.class_id = 1121681 AND rm.mission_type = 2 ) 
	OR ( rm.create_user_id = 63878210 AND rm.mission_type = 1 ) 
	) 
	AND rm.publish_status IN ( 1, 3 ) 
	AND rm.fid = 7179 
ORDER BY
	rm.mission_type DESC,
	rm.id DESC 
	) t 
GROUP BY
	t.topicId 
ORDER BY
	t.missionId DESC;

结果：