时间戳

首页 > 解决方案 > 选择没有对应支付订单的重复支付挂单

mysql - 选择没有对应支付订单的重复支付挂单

问题描述

我的表结构是：

订单

+------+-------------+----------------+-------------+
| id   | customer_id | payment_status |   created_on| 
+------+-------------+----------------+-------------+
| 1    |      1      |      unpaid    | 2018-12-28  |
| 2    |      1      |      unpaid    | 2018-12-29  |
| 3    |      2      |      unpaid    | 2018-12-29  |
| 4    |      2      |      unpaid    | 2018-12-29  |
| 5    |      4      |      paid      | 2018-12-30  |
| 6    |      3      |      unpaid    | 2018-12-30  |
+------+-------------+----------------+-------------+

order_items

+------+-----------+-------------+----------+-------+
| id   | order_id  |  product_id | quantity | price |
+------+-----------+-------------+----------+-------+
| 1    |   1       |      4      |  2       | 20.50 |
| 2    |   1       |      5      |  2       | 25.00 |
| 3    |   2       |      4      |  2       | 20.50 |
| 4    |   2       |      5      |  2       | 25.00 |
| 5    |   3       |      1      |  1       | 20.00 |
| 6    |   3       |      2      |  1       | 25.00 |
| 7    |   4       |      1      |  1       | 20.00 |
| 8    |   4       |      2      |  1       | 25.00 |
| 9    |   5       |      4      |  2       | 20.50 |
| 10   |   5       |      5      |  2       | 25.00 |
| 11   |   6       |      3      |  4       | 15.00 |
+------+-----------+-------------+----------+-------+

顾客

+-----+---------------+----------+
| id  | email         |  name    |
+-----+---------------+----------+
| 1   | abc@mail.com  |  user 1  |
| 2   | xyz@mail.com  |  user 2  |
| 3   | pqr@mail.com  |  user 3  |
| 4   | abc@mail.com  |  user 4  |
+-----+---------------+----------+

问：我希望数据是在一封客户电子邮件下的订单，状态为待处理，而该客户在一周内没有付款状态订单

预期产出：一周内没有对应已付款订单的 1 个单笔订单

+------+-------------+----------------+-------------+
| id   | customer_id | payment_status |   created_on| 
+------+-------------+----------------+-------------+
| 3    |      2      |      unpaid    | 2018-12-29  |
| 4    |      2      |      unpaid    | 2018-12-29  |
| 6    |      3      |      unpaid    | 2018-12-30  |
+------+-------------+----------------+-------------+

问：我想要数据，好像在一个客户电子邮件下有 2 个具有相同产品和相同数量的订单，具有待处理状态，而该客户在一周内没有支付状态订单

预期输出：2 两个订单，一周内没有相应的已付款订单

+------+-------------+----------------+-------------+
| id   | customer_id | payment_status |   created_on| 
+------+-------------+----------------+-------------+
| 3    |      2      |      unpaid    | 2018-12-29  |
| 4    |      2      |      unpaid    | 2018-12-29  |
+------+-------------+----------------+-------------+

标签： mysql

解决方案

第一个查询是可疑的——你真的是指emailorcustomer_id吗？后者应该是您设计架构以将一个“客户”与另一个区分开来的方式。想通了。（并修复数据以使其清晰。）同时，我将假设customer_id区分客户。

我无法理解第一个查询的目的。您正在寻找为较晚的订单付款但尚未为较早的订单付款的客户？或者在数据库中寻找错误的帖子？无论如何，这是一个镜头：

SELECT  Unpd.id, Unpd.customer_id, Unpd.payment_status, Unpd.created_on
    FROM  Orders AS Pd  ON Pd.customer_id = C.id
      AND  payment_status = 'paid'
    WHERE  NOT EXISTS 
    (
        SELECT  1
            FROM  Orders AS Pd
            WHERE  Pd.customer_id = C.id
              AND  Pd.payment_status = 'paid'
              AND  Pd.created_on > NOW() - INTERVAL 1 WEEK 
    )

第二次查询。我将其改写为：在同一天找到同一客户的两个（或更多）订单（已付款或未付款）（但不检查商品是否相同）：

SELECT  O2.id, O2.customer_id, O2.payment_status, O2.created_on
    FROM  
    (
        SELECT  O.customer_id, O.created_on
            FROM  Orders AS O
            GROUP BY  O.customer_id, O.created_on
            HAVING  COUNT(*) >= 2
    ) AS MultipleInOneDay
    JOIN  Orders AS O2  USING (customer_id, created_on)

推荐阅读