时间戳

首页 > 解决方案 > 购物车 - 左连接 - 想限制到最接近购买日期的一行商品

mysql - 购物车 - 左连接 - 想限制到最接近购买日期的一行商品

问题描述

我试图向客户展示 - 所有以前的购买

我有 3 张桌子：

shopping_cart
shopping_orders
audit_shopping_items

我想选择最接近苹果购买日期（订单日期）的苹果价格 - 来自 - audit_shopping_items 表

ISSUE : 问题是当我进行 LEFT JOIN 时，我以相同的订单 ID 获得 2 行 Apples，代码如下

我只想要一个，最接近产品购买日期的

表：audit_shopping_items

+-----+------------+--------------+--------------+-------------------+----------------+---------------------+--------------+
| id  | item_code  |  item_name   | item_weight  | item_weight_unit  | item_quantity  | item_selling_price  | item_status  |
+-----+------------+--------------+--------------+-------------------+----------------+---------------------+--------------+
|   1 |  200000001 | apple        |          500 | gm                |              1 |                  10 | active       |
|   2 |  200000002 | apple_green  |          500 | gm                |              1 |                  10 | active       |
|   3 |  200000003 | avocado      |          500 | gm                |              1 |                  10 | active       |
|   4 |  200000001 | apple        |          500 | gm                |              1 |                  18 | active       |
+-----+------------+--------------+--------------+-------------------+----------------+---------------------+--------------+

我的 Mysql 查询

SELECT 
  sc.item_code, 
  so.order_id, 
  si.item_name, 
  si.item_selling_price, 
FROM 
  shopping_cart as sc 
  LEFT JOIN shopping_orders AS so ON sc.order_id = so.order_id 
  LEFT JOIN audit_shopping_items AS si ON si.item_code (
    SELECT 
      pi.item_code 
    FROM 
      shopping_cart AS pi 
    WHERE 
      pi.sys_m_date <= so.sys_m_date 
      AND pi.item_code = sc.item_code 
    LIMIT 
      1
  ) 
LIMIT 
  500

表：shopping_cart

+-----+----------------------+----------------------+------------+----------------+-------------+-
| id  |     sys_c_date       |     sys_m_date       | item_code  | item_quantity  |  order_id   | 
+-----+----------------------+----------------------+------------+----------------+-------------+-
|  1  | 2019-12-09 22:05:05  | 2019-12-09 22:05:15  | 200000001  |             2  | 1869460212  | 
|  2  | 2019-12-09 22:05:08  | 2019-12-09 22:05:15  | 200000003  |             2  | 1869460212  | 
|  3  | 2019-12-09 22:06:16  | 2019-12-09 22:06:24  | 200000001  |             4  | 2891252193  | 
|  4  | 2019-12-09 22:06:18  | 2019-12-09 22:06:24  | 200000004  |             2  | 2891252193  | 
+-----+----------------------+----------------------+------------+----------------+-------------+-

表：shopping_orders

+-----+----------------------+----------------------+-------------+---------------+------------------+----------------+--------------+
| id  |     sys_c_date       |     sys_m_date       |  order_id   | order_amount  |   order_owner    | order_status   | order_method |
+-----+----------------------+----------------------+-------------+---------------+------------------+----------------+--------------+
|  1  | 2019-12-09 22:05:15  | 2019-12-09 22:05:15  | 1869460212  |           40  | abc@example.com  | created        | cod          |
|  2  | 2019-12-09 22:06:24  | 2019-12-09 22:06:24  | 2891252193  |           92  | abc@example.com  | created        | cod          |
+-----+----------------------+----------------------+-------------+---------------+------------------+----------------+--------------+

标签： mysqlsql

解决方案

LEFT JOINING audit_shopping_items AS si ON -- si.id -- 而不是 -- si.item_code -- 到 pi-id 解决了问题

            SELECT 
              sc.item_code, 
              so.order_id, 
              si.item_name, 
              si.item_selling_price, 
            FROM 
              shopping_cart as sc 
              LEFT JOIN shopping_orders AS so ON sc.order_id = so.order_id 
              LEFT JOIN audit_shopping_items AS si ON si.id (
                SELECT 
                  pi.id 
                FROM 
                  shopping_cart AS pi 
                WHERE 
                  pi.sys_m_date <= so.sys_m_date 
                  AND pi.item_code = sc.item_code 
                LIMIT 
                  1
              ) 
            LIMIT 
              500

推荐阅读