Mysql获取的行受另一个表列值的限制

v64noz0r 于 5个月前发布在 Mysql

关注(0)|答案(2)|浏览(59)

我有产品和卖方谁添加了这个产品的列表每个卖方有一个限制显示的产品。我需要写一个查询，列出所有的产品与分页100只显示卖方产品限于该卖方

`Seller Table`
| seller id| Seller Name |  limit |
|:-------- |:-----------:| ------:|
| 1        | Seller One  | 1     |
| 2        | Seller Two  | 3    |
| 3        | Seller Three| 2    |

`Products Table
| product id| seller id | Product Name |
|:-------- |:----------:| ------------:|
| 1        | 1          | Product 1    |
| 2        | 2          | Product 2    |
| 3        | 1          | Product 3    |
| 4        | 1          | Product 4    |
| 5        | 2          | Product 5    |
| 6        | 2          | Product 6    |
| 7        | 2          | Product 7    |
| 8        | 3          | Product 8    |
| 9        | 3          | Product 9    |
| 9        | 3          | Product 10   |
| 9        | 3          | Product 11   |

(Output or Result I am expecting)
| product id| seller id | Product Name |
|:-------- |:----------:| ------------:|
| 1        | 1          | Product 1    |
| 2        | 2          | Product 2    |
| 5        | 2          | Product 5    |
| 6        | 2          | Product 6    |
| 8        | 3          | Product 8    |
| 9        | 3          | Product 9    |

字符串
卖家1 =产品1
卖方2 =产品2、产品5和产品6
卖方3 =产品8和产品9
我想得到这个产品
如何写查询？
并且还可以选择卖方的随机产品
我有查询只取产品。

$products = Products::paginate(100);

I need modify it based on Seller Model

型

mysql

来源：https://stackoverflow.com/questions/68832170/mysql-fetch-rows-limited-by-another-table-column-value

2条答案

按热度按时间

anauzrmj1#

在搜索特定卖家和产品时启用WHERE子句，否则禁用它。使用LIMIT和OFFSET进行分页。请尝试此操作

-- MySQL (v5.8)
SELECT t.product_id, s.seller_id, t.product_name
FROM seller s
INNER JOIN (SELECT product_id
                 , seller_id
                 , product_name
                 , ROW_NUMBER() OVER (PARTITION BY seller_id ORDER BY product_id) row_num
            FROM Products
            -- WHERE seller_id = 1
            -- AND product_id = 1
           ) t
      ON s.seller_id = t.seller_id
     AND t.row_num <= s.t_limit;

字符串
请检查URL https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=d142f50fb24763ec9fc1e937f8b8d6a7
如果需要逗号分隔的product_name，请尝试此选项

SELECT  CONCAT(MAX(s.seller_name), ' = ', GROUP_CONCAT(t.product_name)) expected_output
FROM seller s
INNER JOIN (SELECT product_id
                 , seller_id
                 , product_name
                 , ROW_NUMBER() OVER (PARTITION BY seller_id ORDER BY product_id) row_num
            FROM Products
            -- WHERE seller_id = 1
            -- AND product_id = 1
           ) t
      ON s.seller_id = t.seller_id
     AND t.row_num <= s.t_limit
GROUP BY s.seller_id;

型
请检查https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=0e4cfa7dbb744e7aa416b79d6f964775

赞(0）回复(0）举报 5个月前

bjp0bcyl2#

根据@RahulBiswas的SQL代码，我写了一个Laravel查询。它工作正常

$subq  = Products::select(DB::raw('ROW_NUMBER() OVER (PARTITION BY seller_id ORDER BY id) row_num, *'));
                 $query = Sellers::select('t.*',)->join(\DB::raw('('.$subq->toSql().') as t'), function ($join) use ($subq) { 
                            $join->on('sellers.user_id','t.seller_id')
                            $join->on('t.row_num', '<=', "sellers.limit")
                        })

字符串

赞(0）回复(0）举报 5个月前

我来回答

Mysql获取的行受另一个表列值的限制

2条答案

相关问题

热门标签

最新问答