时间戳

首页 > 解决方案 > SQL - 从外部表分配值

mysql - SQL - 从外部表分配值

问题描述

我有一个包含客户 ID 和优惠券的两列表。我想通过将来自外部数据列表的凭证分配给每个客户的 ID 来填写“优惠券”列。更重要的是，一旦分配了代码，就不能再次分配给其他客户。

你能帮我写一个SQL查询吗？

Table:
| Customer ID | Coupon |
| -------- | -------------- |
| 100  |            |
| 200  |           |
| 300 |           |

Data list:
| Voucher ID | Voucher |
| -------- | -------------- |
| 1   |      j2h3h2      |
| 2  |      hfd7s8     |
| 3  |    fsdfs72       |

Final result
| Customer ID | Coupon |
| -------- | -------------- |
| 100   |      j2h3h2      |
| 200  |      hfd7s8     |
| 300  |    fsdfs72       |

标签： mysqlsqlmysql-8.0

解决方案

正如您所评论的，您使用的是 MySQL 8，我将使用 Common Table Expressions 并ROW_NUMBER()创建按顺序编号的行。

然后，我可以将它们加入到正在更新的表中，并相互连接，以将新的凭证 ID 分配给没有凭证 ID 的客户。

我还将使用术语voucher，因为您似乎交替使用了优惠券和优惠券。

WITH
  renumbered_customer AS
(
  -- Find all rows in the customer table that do Not have an assigned voucher
  -- Number those rows sequentially from 1 upwards
  SELECT
    *,
    ROW_NUMBER() OVER (ORDER BY customer_id)   AS row_id
  FROM
    customer
  WHERE
    voucher_id IS NULL
),
  renumbered_voucher AS
(
  -- Find all rows in the voucher table that have not bee assigned to a customer
  -- Number those rows sequentially from 1 upwards
  SELECT
    *,
    ROW_NUMBER() OVER (ORDER BY voucher_id)   AS row_id
  FROM
    voucher
  WHERE
    NOT EXISTS (
      SELECT *
        FROM customer
       WHERE customer.voucher_id = voucher.voucher_id
    )
)
UPDATE
  customer
INNER JOIN
  renumbered_customer  c
    ON c.customer_id = customer.customer_id
INNER JOIN
  renumbered_voucher   v
    ON c.row_id = v.row_id
SET
  customer.voucher_id = v.voucher_id
;

工作演示：https ://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=29875e64b3de3559ceea20d85d222f9b

推荐阅读