mysql - 根据序列中另一列的 ID 引用列

Question

我正在尝试在特定条件下使用另一个表的列的 OID 更新(引用)一个表的列(oid)。

示例:

Customer Table :
------------------
CID  name   oid
-------------------
1  abc     null
2  abc     null
3  abc     null
4  xyz     null
--------------------

Order Table
--------------
OID  name
--------------
10   abc
11   abc
12   abc
13   xyz
--------------

输出应该是:

Customer Table :
------------------
CID  name   oid
-------------------
1    abc     10
2    abc     11
3    abc     12
4    xyz     13
--------------------

我试过以下方法

UPDATE customer as c, order as o
   SET c.oid = o.OID
   WHERE c.name = o.name;
-----------------------------
update customer INNER JOIN order on customer.name=Order.name
SET customer.oid=Order.OID
where customer.oid IS null;

但是客户表正在更新如下

Customer Table :
------------------
CID  name   oid
-------------------
1    abc     10
2    abc     10
3    abc     10
4    xyz     13
--------------------

Answer 1

想法是为Customer 表和Order 表中的每个条目分配一个行号。

因此，当在这两个表之间进行内部联接时，您现在有两个条件(而之前它是一个，即唯一的名称)。

一个条件是name，另一个是row_number

您可以使用此查询:

UPDATE Customer CT
INNER JOIN (
    SELECT
        customerTable.CID,
        orderTable.OID FROM 
        (
            SELECT
                *, 
                @rn1 := @rn1 + 1 AS row_number
            FROM
                Customer C
            CROSS JOIN (SELECT @rn1 := 0) var
            ORDER BY CID
        ) AS customerTable
    INNER JOIN (
        SELECT
            *, 
            @rn2 := @rn2 + 1 AS row_number
        FROM
            `Order` O
        CROSS JOIN (SELECT @rn2 := 0) var
        ORDER BY OID
    ) AS orderTable ON customerTable. NAME = orderTable. NAME
    AND customerTable.row_number = orderTable.row_number
) AS combinedTable ON CT.CID = combinedTable.CID
SET CT.oid = combinedTable.OID

注意:因为在匹配的 name 上连接这两个表不足以满足您的需求。这就是为什么除了匹配名称之外，还要为每一行分配一个 row_number(在 Customer 和 Order 表中)。然后在匹配的 name 上在这两个表之间进行内部连接 和 行号。因此，您限制一个条目与另一个表中的其他条目多次连接。

---

测试架构和数据:

_{无法添加 sql fiddle}

DROP TABLE IF EXISTS `customer`;
CREATE TABLE `customer` (
  `CID` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `oid` int(11) DEFAULT NULL,
  PRIMARY KEY (`CID`)
);
INSERT INTO `customer` VALUES ('1', 'abc', null);
INSERT INTO `customer` VALUES ('2', 'abc', null);
INSERT INTO `customer` VALUES ('3', 'abc', null);
INSERT INTO `customer` VALUES ('4', 'xyz', null);

DROP TABLE IF EXISTS `order`;
CREATE TABLE `order` (
  `OID` int(11) NOT NULL,
  `name` varchar(100) NOT NULL
);
INSERT INTO `order` VALUES ('10', 'abc');
INSERT INTO `order` VALUES ('11', 'abc');
INSERT INTO `order` VALUES ('12', 'abc');
INSERT INTO `order` VALUES ('13', 'xyz');

现在看看，Customer 表是怎样的:

SELECT
*
FROM Customer;

输出:

CID name  oid
1   abc   10
2   abc   11
3   abc   12
4   xyz   13