有办法在一次查询中回答这个问题吗？

Question

我有一个由3个表组成的MySQL数据库：

I. Person (身份证、姓名、购买)

II. (id，product，date_purchased)

目录(id，产品，单位成本)

Person.purchases持有Purchase.id。也就是说，每当一个人买东西时，订单id就会被记录在Person.purchases中。就像。Person.purchases有1300,292个存储在里面。

每个购买条目都记录购买的任何项目的实例。因此，Purchase.id = 300可以是"foo“。

Catalog拥有关于"foo“的描述。

我想知道的是如何回答：“谁买了"foo"？我知道如何分两个步骤回答这个问题：

步骤1:从采购中选择Purchases.id，其中Purchases.product = Catalog.product；

我会将步骤1的结果存储在变量tmp中；

步骤2:从Person.orders喜欢“%tmp%”的人中选择名称；

我使用的方式如下所示，因为Person.orders存储了多个Purchase.id。

有办法将这两者合并成一个查询吗？

Answer 1

这个问题可以使用一个查询来回答：

使用存在

SELECT a.name
  FROM PERSON a
 WHERE EXISTS(SELECT NULL
                FROM PURCHASE b 
                JOIN CATALOG c ON c.product = b.product
               WHERE FIND_IN_SET(b.id, a.purchases) > 0
                 AND c.product = 'foo')

使用联接：

这需要不同的(或分组)，因为复制是可能的，如果一个人/客户已经购买了"foo“不止一次。

SELECT DISTINCT a.name
  FROM PERSON a
  JOIN PURCHASE b ON FIND_IN_SET(b.id, a.purchases) > 0
  JOIN CATALOG c ON c.product = b.product
 WHERE c.product = 'foo'

附录

我同意其他答案，即数据模型很差--购买表中应该有个人/客户id，而不是PERSON表。但它并没有彻底改变事情。

Answer 2

这是一个糟糕的数据库设计，它阻碍您回答一个相对简单的问题。我会把你的桌子设计成这样：

customers (id, name)
purchases (id, product_id, customer_id, date_purchased)
products (id, product_name, cost_per_unit)

因此，你要问的是“谁买了福？”是：

SELECT c.id, c.name
FROM products pr 
   LEFT JOIN purchases pu ON (pr.id = pu.product_id)
   INNER JOIN customers c ON (pu.customer_id = c.id)
WHERE product_id = foo 
-- could replace with product_name = 'foo' here, but you should know product_id

这使您的数据库以某种正常的形式出现(我不记得到底是哪一个)，因此您可以利用关系数据库提供的特性。

在这里创建另一个表，将其命名为receipts，并将purchases重命名为line_items，也可能是有用的。这可以确保您可以跟踪在一次购买中购买多个项目的客户，等等。

Answer 3

你的桌子的一个更好的结构是：

I. Person (个人姓名)-从这里删除的采购

二.(购买者、买方、productid、date_purchased)

目录(产品，产品，单位成本)

因此，与其将购买人的物品存储在实体表中，不如将其存储在购买表中。

这将有几个好处：

1. 你可以储存多少你喜欢的东西。按照现在的方式，“购买”字段最终将充满“购买”，然后您将做什么？
2. 更容易编写查询。

(如果Person.purchases中存储了",1,300,292“，例如字段开头和结尾处的逗号，而没有空格)，那么您的问题可以在这样的一个查询中得到回答：

如果开头和结尾都没有逗号，条件就会更复杂，但肯定是可以做到的。

SELECT p.id, p.name
FROM Person p
  JOIN Purchase pur
    ON p.purchases LIKE CONCAT("%,",CAST(pur.id AS CHAR),",%")
WHERE pur.product LIKE "foo"

而且您不需要使用Catalog连接，因为Product名称也在Purchase表中。

如果您确实希望从Catalog获得信息，您也可以使用另一个连接：

SELECT p.id, p.name, cat.*
FROM Person p
  JOIN Purchase pur
    ON p.purchases LIKE CONCAT("%,",CAST(pur.id AS CHAR),",%")
  JOIN Catalog cat
    ON pur.product = cat.product
WHERE pur.product LIKE "foo"     ---or cat.product LIKE "foo"