V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
awanganddong
V2EX  ›  MySQL

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

  •  
  •   awanganddong · 2023-02-23 15:43:36 +08:00 · 1548 次点击
    这是一个创建于 624 天前的主题,其中的信息可能已经有所发展或是发生改变。
    比如这张表叫 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 ,则取时间最大的一条记录。

    17 条回复    2023-02-24 09:39:14 +08:00
    corcre
        1
    corcre  
       2023-02-23 15:55:13 +08:00
    (平时用的 sqlserver, 问题也没咋看懂, 说错了莫怪
    但是你这个直接按 is_cover 分成 1 和 2 分别按要求查询最后 union 出来不就好了吗, 还是说用户-分类-封面这三个条件组合查询出来的记录是唯一的?但是你第 1 行和第 2 行数据是重复的, 所以我是按照可重复的前提来考虑的
    opengps
        2
    opengps  
       2023-02-23 16:00:22 +08:00
    看起来是两个简单的语句,op 是不是非要用一句 sql 达到最终目的?
    awanganddong
        3
    awanganddong  
    OP
       2023-02-23 16:01:08 +08:00
    相当于 is_cover=1 与 is_cover=2 有一部分重合了。
    会存在重复的情况
    比如
    ID=1 用户 A 分类 1 封面 1 内容 123
    ID=5 用户 A 分类 1 默认 内容 123
    awanganddong
        4
    awanganddong  
    OP
       2023-02-23 16:02:01 +08:00
    @opengps


    现在这个是查询列表,往外返回数据。
    liprais
        5
    liprais  
       2023-02-23 16:03:27 +08:00
    mysql 8 之前没有窗口函数,洗洗睡吧
    或者你可以用那个恶心死人的临时变量
    wetalk
        6
    wetalk  
       2023-02-23 16:11:31 +08:00
    先分别查,再用 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
        7
    LeegoYih  
       2023-02-23 16:16:03 +08:00
    union all 性能不差,如果数量少是可以用这种方案的
    awanganddong
        8
    awanganddong  
    OP
       2023-02-23 16:22:30 +08:00
    @wetalk 这个查询会出现一个问题,就是出现重复情况

    比如一个用户,一个分类下,有多个文章。然后文章下有一个封面。
    这时候就会展示出来一个封面+一个默认的。这样是不允许的。
    awanganddong
        9
    awanganddong  
    OP
       2023-02-23 16:23:25 +08:00
    用户在分类下只能展示一个记录。
    wetalk
        10
    wetalk  
       2023-02-23 16:27:12 +08:00
    @awanganddong 所以实际上,你的需求应该是 [查询每个分类下的一条数据,取用户封面为 2 的内容,如果封面不是 2 ,则查询出为默认的最后创建的内容。] 。如果分类下不止一条封面为 2 的内容,怎么取
    awanganddong
        11
    awanganddong  
    OP
       2023-02-23 16:40:55 +08:00
    @wetalk. 取最后时间创建的一条记录。
    yesterdaysun
        12
    yesterdaysun  
       2023-02-23 16:50:07 +08:00
    按 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
        13
    awanganddong  
    OP
       2023-02-23 16:54:38 +08:00
    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
        14
    wiix  
       2023-02-23 18:13:22 +08:00
    这表是一条封面对应多条 post ?
    还要记录的 post 的修改记录?
    然后想同时取出用户在所有分类的 post 的最新修改记录和对应的封面?

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

    数据库范式虽然很教条,但也不能一点不看啊!
    Chad0000
        15
    Chad0000  
       2023-02-23 18:19:37 +08:00
    系统不要设计这么复杂,可以适当冗余。比如每个文章需要显示某个东西,如果这个东西没有,则自动取最后回复的。那么你应该直接设置一个字段用来显示,然后使用事件的方式实现异步更新,使用消息队列监听。文章有回复或修改后,检查是否需要更新这个字段。
    awanganddong
        16
    awanganddong  
    OP
       2023-02-24 09:37:50 +08:00
    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
        17
    awanganddong  
    OP
       2023-02-24 09:39:14 +08:00
    这是另外优化的 sql

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

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

    谢谢大家吧
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   2741 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 78ms · UTC 12:10 · PVG 20:10 · LAX 04:10 · JFK 07:10
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.