php - 如果相同 uID 的其他行具有特定的单元格值，则查询 SELECT uID

Question

我正在尝试构建一个基于 PHP 的 Web 软件，但我遇到了一个我不知道解决方案语法的问题。

基本上，我有两个表:

+-------------+     +---------------+
| Certs       |     | Clients       |
+-------------+     +---------------+
| userID      |     | eID           |
| prodID      |     | prods         |
| prodName    |     +---------------+
+-------------+

在这里，个人购买的每件产品都由他唯一的用户 ID 和产品的唯一 prodID 存储。

客户就像卖家，他们自己有不同的 ID，prod 是一串以逗号分隔的产品 ID。

证书表中的示例行如下:

userID  |  prodID  |  prodName
------------------------------
9000    |  42      |  Pen     
4234    |  54      |  Pencil
9000    |  54      |  Pencil

示例客户行如下:

eID  |  prods
-------------
595  |  42,54

现在，我需要形成一个查询，返 repo 买了特定卖家的所有产品的所有用户 ID。

例如，对于卖家 ID 595，它应该返回 9000，因为用户 ID 9000 已经购买了 42 和 54。

为了方便起见，可以修改表格结构，但由于其他技术问题，我无法创建与其卖家相关的规范化产品列表。

任何帮助将不胜感激!谢谢~

注意:另一种表达方式是将卖家的产品列表保存在结果类型数组中。然后SELECT prodID FROM certs WHERE userID = x最后检查 prodID 的结果数组是否包含数组 prods 的所有元素。

Answer 1

试试这个解决方案:

SELECT a.userID
FROM certs a
INNER JOIN clients b ON b.eID = 595 AND FIND_IN_SET(a.prodID, b.prods) > 0
CROSS JOIN
(
    SELECT (LENGTH(prods) - LENGTH(REPLACE(prods, ',', ''))) + 1 AS prodcnt
    FROM clients
    WHERE eID = 595
) c
GROUP BY a.userID, c.prodcnt
HAVING COUNT(1) = c.prodcnt

---

工作原理:

CROSS JOIN
(
    SELECT (LENGTH(prods) - LENGTH(REPLACE(prods, ',', ''))) + 1 AS prodcnt
    FROM clients
    WHERE eID = 595
) c

这样做是将特定卖家拥有的 prodID 数量的计数附加到每一行。我们通过获取 CSV 列表的字符串长度并减去相同的 CSV 列表但不带逗号的长度，然后将差值加一来计算它。示例:

“54,234,436”的字符串长度为 10。

替换逗号:

“54234436”的字符串长度为 8。

那么 (10-8)+1 = 3。

列表中的三个项目。

---

我们使用 FIND_IN_SET作为 certs 和 clients 之间的连接条件，以确定 prodID 是否存在于 CSV prods 列表中。

FIND_IN_SET 基本上返回 CSV 列表中特定项目的从 1 开始的索引位置。例如:

FIND_IN_SET('54', '34,76,54,128') 

将返回 3，因为 54 作为列表中的第三项存在。我们只需要检查它是否存在于加入条件的列表中，所以我们只检查它是否 > 0。如果该项目不在 CSV 列表中，它将返回 0。

---

一旦我们获得了特定客户的笛卡尔连接产品计数，并且连接了表，中间结果集将如下所示:

a.userID  |  a.prodID  |  b.eID  |  b.prods  |  c.prodcnt
---------------------------------------------------------
9000      |  42        |  595    |  42,54    |  2
4234      |  54        |  595    |  42,54    |  2
9000      |  54        |  595    |  42,54    |  2

这些行中的每一行都满足连接条件。

然后我们按 userID 分组以计算每个 userID 的行数。由于 9000 与 c.prodcnt 中的重复值具有相同的行数，因此返回该行。 userID 4234 被过滤掉，因为只有一行满足连接条件。