MySQL 为每个 category_id 获取 X 列 [英] MySQL fetch X columns for each category_id

问题描述

我不知道这在 mysql 中是否可以通过 ONE 查询实现.

I don't know if this is possible in mysql via ONE query .

假设我有一个表products"，其中包含id"、category_id"、product_name"、price"

assuming I have a table "products" that has "id","category_id","product_name","price"

案例 1:我想从每个类别中提取价格超过 100 美元的 5 个产品

Case 1 : I want to fetch 5 products from each category where price is more than 100$

案例2:我想获取

1-类别 1 中的 3 个产品"

1-"3 products from category 1"

2-第 2 类的 5 个产品"

2- "5 products from category 2"

3-类别 2 中价格超过 100 且未在上述第 2 点中提取的 3 种产品"

3- "3 products from category 2 where the price is more than 100 and is not fetched in point 2 above"

一个查询中的每种情况，这可能吗?

each case in ONE query , is that possible ?

PS:该表大约有 10 万行......

PS : The table has about 100K rows ...

推荐答案

我发现了这个方法:它非常快并且给了我我想要的东西:

I found this method : it is VERY fast and gave me exactly what i wanted :

SELECT  l.*
FROM    (
        SELECT  category,
                COALESCE(
                (
                SELECT  id
                FROM    writings li
                WHERE   li.category= dlo.category 
                ORDER BY
                        li.category, li.id
                LIMIT 15, 1
                ), CAST(0xFFFFFFFF AS DECIMAL)) AS mid
        FROM    (
                SELECT  id as category
                FROM    cats dl 
                ) dlo
        ) lo, writings l
WHERE   l.category>= lo.category
        AND l.category<= lo.category
        AND l.id <= lo.mid

这篇关于MySQL 为每个 category_id 获取 X 列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！