用于按类别顺序显示图像的Mysql查询

I need to display category wise one image in the view,but the below query collects all the category images collectively and displays each category items as the hole.

我需要在视图中显示类别明智的一个图像,但是下面的查询集中收集所有类别图像并将每个类别项目显示为孔。

SELECT *  
                        FROM (
                          (SELECT *, 
                                 IF( @prev <> category_id, 
                                     @rownum := 1, 
                                     @rownum := @rownum+1 
                                 ) AS rank, 
                                 @prev := category_id, 
                                 @rownum  
                          FROM (
                            SELECT * FROM products 
                            ORDER BY category_id, rand()
                          ) random_prodcts)
                        ) products_ranked 
                        WHERE rank <= 20000 AND parent_category_id = 3;

I want this query to be modified and display each category item as one until all the items are displayed one by one at a cycle.

我希望修改此查询并将每个类别项显示为一个,直到所有项目在一个周期逐个显示。

This is the table structure.

这是表结构。

Id  Parent_category_id  Category_id Product_position
1         3                    77   00
2         3                    77   00
3         3                    78   00
4         3                    78   00
5         3                    89   00
6         3                    89   00

1 个解决方案

#1

If you need rows returned in a particular order, then add an ORDER BY on the outer query.

如果需要按特定顺序返回的行,则在外部查询上添加ORDER BY。

For example, if you want to return all the rows with a rank of 1 first, then something like:

例如,如果要首先返回排名为1的所有行,则类似于:

 SELECT r.*
   FROM ( 
          SELECT @rownum := IF(@prev = r.category_id, @rownum+1, 1) AS rank
               , p.id
               , p.parent_category_id
               , @prev := p.category_id AS category_id
               , p.product_position
            FROM products p
           CROSS
            JOIN (SELECT @prev := NULL, @rownum := 0) i
           ORDER
              BY p.category_id
               , RAND()
        ) r
 WHERE r.parent_category_id = 3
   AND r.rank <= 20000
  ORDER
     BY r.rank
      , r.category_id

I'm not understanding why the equality predicate on the parent_category_id column is in the outer query, and not in the inner view query. If parent_category_id is dependent on category_id (the sample data doesn't show an example of a category_id value that is associated with more than one parent_category_id), then I don't see any reason it couldn't be relocated, for potentially better performance, by reducing the number or rows that need to be materialized into the inline view, and reducing the number of calls to the RAND() function, and reducing the size of the set that needs to be sorted.

我不明白为什么parent_category_id列上的等式谓词在外部查询中,而不在内部视图查询中。如果parent_category_id依赖于category_id(示例数据未显示与多个parent_category_id相关联的category_id值的示例),那么我认为没有任何理由无法重新定位,以获得更好的性能,通过将需要实现的数量或行减少到内联视图中,减少对RAND()函数的调用次数,并减小需要排序的集合的大小。

#1