代码之家 › 专栏 › 技术社区 › Michi

如果至少满足一次条件,则在表中插入值

mysql-workbench sql mysql

Michi · 技术社区 · 6 年前

我创建了以下内容 DataModel :

表顾客

INSERT INTO test.customer
(CustomerName, CustomerCountry, RegistrationDate)
VALUES 
("Customer A","DE","2015-05-03"),
("Customer B","US","2015-07-25"), 
("Customer C","US","2016-02-15"), 
("Customer D","DE","2017-09-21"), 
("Customer E","AU","2018-12-07");

表命令

INSERT INTO test.orders
(idCustomer, PaymentMethod, OrderDate)
VALUES 
("1","CreditCard","2015-05-04"),
("1","PayPal","2015-11-18"), 
("3","PayPal","2017-09-04"), 
("2","Invoice","2018-04-30");

到目前为止,一切都很好。

现在我想填这张桌子 SpecialCustomers 表中的值 Customer 和 Orders 基于 WHERE 条件。因此,我尝试了以下代码:

INSERT INTO test.specialcustomers
(idCustomer, CustomerName)
SELECT idCustomer, CustomerName
FROM test.customer
LEFT JOIN test.orders ON test.customer.idCustomer = test.orders.idCustomer
WHERE PaymentMethod ="PayPal";

导致错误:

Error Code: 1052. Column 'idCustomer' in field list is ambiguous

据我所知,问题是 Customer A 两者都有订单 Paypal 和 CreditCard 所以 PaymentMethod 分配给 客户A 不是唯一的。

但是,我的目标是将每个客户插入到表中 特殊客户 尽快 条件至少满足一次 .
因此,-在上述情况下-如果至少有一个订单是用 PayPal 应将客户插入表中 SpeicalCustomers .

我需要在代码中更改什么才能使此工作正常?

4 回复 | 直到 6 年前

Siva Rahul Sharma 6 年前

idCustomer 两个表中都有列,因此在select查询中添加表名。

INSERT INTO test.specialcustomers
(idCustomer, CustomerName)
SELECT test.customer.idCustomer, CustomerName
FROM test.customer
LEFT JOIN test.orders ON test.customer.idCustomer = test.orders.idCustomer
WHERE PaymentMethod ="PayPal";

Tim Biegeleisen 6 年前

我认为您只是在选择中缺少别名:

INSERT INTO test.specialcustomers (idCustomer, CustomerName)
SELECT c.idCustomer, c.CustomerName
FROM test.customer c
LEFT JOIN test.orders o
    ON c.idCustomer = o.idCustomer
WHERE o.PaymentMethod = 'PayPal';

Hero1587 6 年前

如果插入列和select语句不明确,则必须在它们中指定完整的限定名。提示:在FROM子句中使用别名

Daniel E. 6 年前

在这里,您只需指定要插入的wich idcustomer,正如您所做的 left join ,我想是桌上的idcustomer customer :

INSERT INTO test.specialcustomers
 (idCustomer, CustomerName)
SELECT c.idCustomer, c.CustomerName
FROM test.customer c
 LEFT JOIN test.orders o ON tc.idCustomer = o.idCustomer
WHERE o.PaymentMethod ="PayPal";