MYSQL限制JOIN行 [英] MYSQL limit the JOIN rows

问题描述

我有4个涉及查询的表:

I'v got 4 tables involve in the query:

主表items保存项目列表 suppliers通过ID持有物料供应商INNER JOIN item_categories通过ID保存项目类别"INNER JOIN"

the main table items holds items list suppliers that holds the item supplier INNER JOIN by id item_categories that holds the item category INNER JOIN by id

和orders表在以下结构中保存订单: id . . . items => JSON，用于按顺序保存items数组

and orders table that hold orders in the follow struct: id . . . items => a JSON that hold the items array in the order

项目JSON示例:

{
"10": {
    "name": "item 1",
    "step": "1",
    "price": "140",
    "amount": "4"
},
"24": {
    "name": "item 2",
    "step": "1",
    "price": "6.2",
    "amount": "1"
},
"35": {
    "name": "item 3",
    "step": "1",
    "price": "2.9",
    "amount": "3"
},
"37": {
    "name": "item 4",
    "step": "1",
    "price": "3.9",
    "amount": "2"
}}

我想获取最近4个订单金额的所有商品的表格

i want to get table of all the item with the last 4 orders amount

我要进行以下查询:

SELECT 
`items`.`id`, 
`items`.`part_number`, 
`item_categories`.`name`    AS category, 
`suppliers`.`name`          AS supplier, 
`items`.`supplier_id`, 
`items`.`name`, 
`items`.`inventory`, 
`items`.`package_items`, 
`items`.`order_step`, 
`items`.`price`, 
`items`.`discount`, 
`items`.`scale`, 
`items`.`by_scale`, 
`items`.`has_tax`, 
`items`.`category_id`, 
`items`.`enable`, 
Group_concat(Json_extract(`orders`.`items`, Concat('$."', `items`.`id`, '".amount')) ORDER BY 
`orders`.`createdate` DESC) AS last_orders_amount 
FROM   `items` 
       INNER JOIN `suppliers` 
               ON `items`.`supplier_id` = `suppliers`.`id` 
       INNER JOIN `item_categories` 
               ON `items`.`category_id` = `item_categories`.`id` 
       LEFT JOIN `orders` 
              ON 
Json_extract(`orders`.`items`, Concat('$."', `items`.`id`, '"')) IS 
NOT NULL 
WHERE  1 
GROUP  BY `items`.`id` 

这给了我所有最后订单的物品

which give me all the items with all the last orders

所以我的最后一个问题是，是否可以通过LEFT JOIN限制我得到的结果?

so my final question is , is there a way that i could LIMIT the results i get by the LEFT JOIN ?

谢谢:)

推荐答案

一种简单的方法，但可能并不理想，方法是:

An easy way, but probably not ideal would be to:

       LEFT JOIN (SELECT * FROM `orders` LIMIT 1000) ordrs 
              ON 
Json_extract(`ordrs`.`items`, Concat('$."', `items`.`id`, '"')) IS 
NOT NULL 
WHERE  1 
GROUP  BY `items`.`id` 

因此，基本上只嵌套一个select语句作为您的联接表.如果我认为有更好的方法，将进行编辑.

So basically just nest a select statement as your join table. Will edit if I think of a better way.

如果您有权排名，那么看起来也可以使用.来源:

If you have access to rank, it looks like that will also work. Source:

如何限制左联接的结果

如果您可以使用排名，那可能是更好的选择

If rank is available to you that is probably the better option

这篇关于MYSQL限制JOIN行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！