您好,我有一个关于以下示例表的问题
Table: Customer -> customer_id, customer_name
Table: Product -> product_id, product_serialnumber
Table: Customer_Product -> customer_id, product_serialnumber
现在,我怎样才能获得customer_name,同时有一个输入:product_id
.我要输入product_id
,并想选择所有购买此商品customer_name(Customer_Product
(
我有一个查询,但事实如下:
SELECT customer_name
FROM Customer
WHERE customer_id IN (
SELECT customer_id
FROM Customer_Product
WHERE product_serialnumber IN (
SELECT product_serialnumber
FROM Product
WHERE product_id = ?)))
还有其他简单的查询吗?
您的查询很好。 编写查询的一种更典型的方法是使用显式联接:
select c.*
from customer c join
customer_product cp
on c.customer_id = cp.customer_id join
product p
on p.product_serialnumber = cp.product_serialnumber
where p.product_id = @PRODUCT_ID;
这种方法的一个缺点是,如果某些客户多次购买产品,您可能会收到重复项。 当然,您可以使用select distinct
来解决此问题:
select distinct c.*
from customer c join
customer_product cp
on c.customer_id = cp.customer_id join
product p
on p.product_serialnumber = cp.product_serialnumber
where p.product_id = @PRODUCT_ID;
试试这个它会起作用:
使用内部联接
SELECT t3.`customer_name` FROM Customer_Product t1
JOIN Product t2 ON t2.`product_serialnumber` = t1.`product_serialnumber` AND t2.`product_id`='$product_id'
JOIN Customer t3 ON t3.`customer_id` = t1.`customer_id`
JOIN Customer t3 ON t3.`customer_id` = t1.`customer_id`
在这里,$product_id
是由用户输入的。
这是从多个表中获取数据的最简单方法。