请教大家一个关于 mysql 查询问题，

比如这张表叫 post 表

  `id` int(11) NOT NULL AUTO_INCREMENT,
  `member_id` int(11) NOT NULL DEFAULT '0' COMMENT '用户 ID',
  `category_id` int(11) NOT NULL DEFAULT '0' COMMENT '分类 ID',
  `is_cover` tinyint(1) DEFAULT '1' COMMENT '1-默认 2-封面',
  `content` text  COMMENT '内容',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

我现在需要查询出用户封面为 2 的内容，如果封面不是 2 ，则查询出为默认的最后创建的内容。

查询结果中每个分类只有一个内容

文章内容比如
用户 1 分类 1 封面 1 内容 2 2022
用户 1 分类 1 封面 1 内容 2 2022
用户 1 分类 1 封面 1 内容 1 2022
用户 2 分类 1 封面 1 内容 1 2022

也就是用户按分类+封面为 2 进行 group ，如果封面为 1 ，则取时间最大的一条记录。

封面

null

default

comment

17 replies • 2023-02-24 09:39:14 +08:00

corcre

Feb 23, 2023

(平时用的 sqlserver, 问题也没咋看懂, 说错了莫怪
但是你这个直接按 is_cover 分成 1 和 2 分别按要求查询最后 union 出来不就好了吗, 还是说用户-分类-封面这三个条件组合查询出来的记录是唯一的?但是你第 1 行和第 2 行数据是重复的, 所以我是按照可重复的前提来考虑的

opengps

Feb 23, 2023

看起来是两个简单的语句，op 是不是非要用一句 sql 达到最终目的？

awanganddong

Feb 23, 2023

相当于 is_cover=1 与 is_cover=2 有一部分重合了。
会存在重复的情况
比如
ID=1 用户 A 分类 1 封面 1 内容 123
ID=5 用户 A 分类 1 默认内容 123

awanganddong

Feb 23, 2023

@opengps

现在这个是查询列表，往外返回数据。

F281M6Dh8DXpD1g2

Feb 23, 2023

mysql 8 之前没有窗口函数，洗洗睡吧
或者你可以用那个恶心死人的临时变量

wetalk

Feb 23, 2023

先分别查，再用 union ，union 自带去重，嫌 union 性能差，也可以再嵌套一层
select t.* from post t join
(
select distinct t2.id as id from
(
select member_id, max(id) as id from post where is_cover != 2 group by member_id
union all
select member_id, id from post where is_cover = 2
) t2
) t1 on t.id = t1.id;

LeegoYih

Feb 23, 2023

union all 性能不差，如果数量少是可以用这种方案的

awanganddong

Feb 23, 2023

@wetalk 这个查询会出现一个问题，就是出现重复情况

比如一个用户，一个分类下，有多个文章。然后文章下有一个封面。
这时候就会展示出来一个封面+一个默认的。这样是不允许的。

awanganddong

Feb 23, 2023

用户在分类下只能展示一个记录。

wetalk

Feb 23, 2023

@awanganddong 所以实际上，你的需求应该是 [查询每个分类下的一条数据，取用户封面为 2 的内容，如果封面不是 2 ，则查询出为默认的最后创建的内容。] 。如果分类下不止一条封面为 2 的内容，怎么取

awanganddong

Feb 23, 2023

@wetalk. 取最后时间创建的一条记录。

yesterdaysun

Feb 23, 2023

按 member_id 和 category_id 分组, 然后用 case when, 如果 exist select is_cover=2 的数据, 则返回 id,否则 select is_cover=1 order by create_time desc limit 1 的 id, 以这个结果作为子查询再套一层 join id 把其他字段查出来

awanganddong

Feb 23, 2023

select
t1.id ,t1.category_id,t1.is_cover,t1.member_id
from post_resource t1
where
t1.id = if(
(select max(t2.id) from post_resource t2 where t1.member_id = t2.member_id and t1.category_id = t2.category_id
and t2.is_cover = 2),
(select max(t2.id) from post_resource t2 where t1.member_id = t2.member_id and t1.category_id = t2.category_id
and t2.is_cover = 2),
(select max(t2.id) from post_resource t2 where t1.member_id = t2.member_id and t1.category_id = t2.category_id
and t2.is_cover = 1)
) and
t1.member_id = 1
order by id desc;

wiix

Feb 23, 2023

这表是一条封面对应多条 post ？
还要记录的 post 的修改记录？
然后想同时取出用户在所有分类的 post 的最新修改记录和对应的封面？

与其绞尽脑汁写拧巴的 sql ，不如把 is_cover 删了，封面放另外一张表。

数据库范式虽然很教条，但也不能一点不看啊！

ktqFDx9m2Bvfq3y4

Feb 23, 2023

系统不要设计这么复杂，可以适当冗余。比如每个文章需要显示某个东西，如果这个东西没有，则自动取最后回复的。那么你应该直接设置一个字段用来显示，然后使用事件的方式实现异步更新，使用消息队列监听。文章有回复或修改后，检查是否需要更新这个字段。

awanganddong

Feb 24, 2023

select
t1.id ,t1.category_id,t1.is_cover
from post_resource t1
where
t1.id =
(
select t2.id from post_resource t2 where t1.member_id = t2.member_id and t1.category_id = t2.category_id
order by t2.is_cover desc, t2.id desc limit 1
)
and t1.member_id = 1
order by t1.id desc;

awanganddong

Feb 24, 2023

这是另外优化的 sql

能解决这个问题。
但是我这个接口是一个核心接口，所以不能用复杂查询。

@Chad0000 你这个方案非常好，我准备走你这条路子。
冗余字段处理

谢谢大家吧