如何使用 case 和 limit 语句处理这个 mysql 查询 [英] How to handle this mysql query with case and limit Statement

问题描述

我已关注此用户类别按钮.当我单击该按钮时，它将将该记录保存到following_follower_categories 中.我希望他们对该类别的记录进入我的类别

I have follow this user category button. When i click on that button, it will save that record into following_follower_categories. i want their records for that category into my category

有名字的表类别、following_follower_categories、书签、user_bookmarks有他们的领域像

tables having their name Categories, following_follower_categories, bookmarks, user_bookmarks having their fields like

1) 书签包含id 和其他详细信息

2) user_bookmarks 表

2) user_bookmarks table

user_id、bookmark_id、category_id

3) following_follower_categories

3) following_follower_categories

****user_id(我关注的其他 user_id)**

****user_id( other user_id to whom i am following)**

follower_user_id(当前用户ID)

follower_category_id(我的书签会去哪里)

category_id(其他用户category_id)

4) 类别

id ,cat_name , user_id

现在我想要实现的是当我关注某人时我希望他们的文章在限制和条件下归入我的那个类别.

Now i want to achieve is when i am following someone cateogory I want their artilce into my that category with limit and in conditions.

这是我尝试执行的 mysql 查询.

Here is mysql query which i tried to do .

SELECT t.*
  FROM (SELECT f.category_id cat_id,
               `user_bookmarks`.`id` AS `user_bookmark_id`,
               `bookmark_id`,
               `user_bookmarks`.`user_id`,
               `user_bookmarks`.`category_id`,
               `bookmark_website`,
               `bookmark_title`,
               `bookmark_preview_image`,
               `pdf_txt_flag`,
               `youtube_video`,
               `snapshot_preview_image`,
               (CASE
                    WHEN `user_bookmarks`.`category_id` =
                                               f.follower_category_id
                        THEN (SELECT *
                                FROM `user_bookmarks` ub
                              WHERE ub.category_id= f.follower_category_id)
                        ELSE 0
               END) `mode`,
               @r:= CASE
                        WHEN `user_bookmarks`.`category_id` = @g THEN @r+1
                        ELSE @r:=1
                    END `rank`,
               @g:=category_id
          FROM `user_bookmarks`
               LEFT JOIN `following_follower_categories` f
                   ON f.follower_category_id =`user_bookmarks`.`category_id`
               LEFT JOIN `bookmarks`
                   ON `user_bookmarks`.`bookmark_id` = `bookmarks`.`id`
               LEFT JOIN `categories`
                   ON `user_bookmarks`.`category_id` = `categories`.`id`
               JOIN (SELECT @r:=0,@g:=0) t1
         WHERE `user_bookmarks`.`category_id` IN (161,
                                                  83,
                                                  107,
                                                  73)
      ORDER BY `user_bookmarks`.`created_at` DESC) t
WHERE t.rank <=10

我知道上面的查询是错误的.任何人都可以让我知道如何做到这一点.我们可以在 mysql 本身中处理这个问题.

I know above query is wrong. Can anybody gives me idea how to do this. Can we handle this in mysql itself.

这是我的预期输出.我需要它in and limit 子句

here is my expected output. i need it within in and limit clause

谢谢

推荐答案

一个问题类似于 mode 列的表达式.CASE 表达式中的子查询应返回一个表达式和一行.而不是 SELECT * 应该是 SELECT <expr>，如果有可能返回多行，应该有一个 LIMIT 1 子句.

One issue looks like the expression for the mode column. The subquery in the CASE expression should return one expression, and one row. Rather than SELECT * it should be SELECT <expr>, and there should be a LIMIT 1 clause if there's a possibility that it returns more than one row.

如果您需要多个表达式，那么您要么需要使用不同的子查询重复该 CASE 表达式，要么您可能需要一个 JOIN 操作.

If you need more than one expression, then you'd either need to repeat that CASE expression, with a different subquery, or you might want a JOIN operation.

这篇关于如何使用 case 和 limit 语句处理这个 mysql 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！