Mysql读取行限制为另一个表列的值

Question

我有添加此产品的产品和卖家列表每个卖家都有展示产品的限制。我需要编写一个查询，列出所有页码为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

Answer 1

在搜索特定卖家和产品时启用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查看

Answer 2

根据@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")
})