MySQL单表复杂查询问题

Question

在尝试了几种可能性之后，我停了下来。在不使 mysql 服务器因数百个查询而超载的情况下，这就是我想要实现的目标：

这是表格

    CREATE TABLE `users` (
  `id` int(11) NOT NULL auto_increment,
  `firstname` varchar(64) NOT NULL,
  `lastname` varchar(64) NOT NULL,
  `email` varchar(64) NOT NULL,
  `status` smallint(5) NOT NULL,
  `refchid1` int(11) NOT NULL,
  `refchid2` int(11) NOT NULL,
  `refchid3` int(11) NOT NULL,
  `refchid4` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 ;

说明：

1. 我试图根据复杂的树调整一个人的状态。每个用户都有一个由 auto_increment id 字段表示的 id。
2. 用户 A 将其详细信息输入到表中。
3. 用户 B 被用户 A 引用，因此当输入他的详细信息时，refchid1 与用户 A 具有相同的 id。
4. 用户 C 被用户 B 引用，他的 refchid1 成为用户 B 的 id，他的 refchid2 成为用户 A 的 id
5. 。 D 被用户 C 引用，他的 refchid1 变为用户 C 的引用，refchid2 变为用户 B 的引用，refchid3 变为用户 A 的引用

。如上所述，您走在正确的道路上。

上述描述的示例数据：

(6, 'Lars', 'Luna', '[email protected]', 25, 0, 0, 0, 0),
(7, 'Sonya', 'Cox', '[email protected]', 25, 6, 0, 0, 0),
(8, 'Aiko', 'Hodge', '[email protected]', 25, 7, 6, 0, 0),
(9, 'Lillith', 'Bray', '[email protected]', 25, 8, 7, 6, 0),
(10, 'Macey', 'Hayes', '[email protected]', 25, 9, 8, 7, 6);

我想要完成的是：

1. 默认状态是 25。（25,28,30,35,40）基本上将其视为折扣系统，您推荐的人越多，人就越多他们将您推荐到原始推荐人所在的位置，您将获得更大的折扣。
2. 如果用户 A 拥有至少 10 个被引用为 refchid1 的唯一用户，他将获得状态。
3. 如果用户 A 在 refchid1 中至少有 10 个唯一用户，而每个用户又在 refchid1 中拥有 10 个唯一用户，其中用户 A 是 refchid2，则他将获得状态。
4. 用户 A 在 rechid1 中拥有 10 个唯一用户，每个用户在 refchid1 中拥有 10 个唯一用户，其中用户 A 是 rechid2，而 rechid2 又在 refchid1 中拥有 10 个唯一用户，其中用户 A 是 refchid3。
5. 如上所述

，测试的结构很复杂，我希望在不使 mysql 服务器因数百万个查询而超载的情况下进行测试。该查询应该是更新查询，它将调整所有匹配用户的状态。

有谁知道我将如何实现这一目标的解决方案？

Answer 1

我真的不知道你是如何计算这些点的，但是这个查询可能会为你提供足够的数据来处理、使用它，并在你让公式工作时将其重写为更新查询。

SELECT
 parent.*,
 SUM(IF(parent.id = child.refchid1, 1, 0)) AS count_ref1,
 SUM(IF(parent.id = child.refchid2, 1, 0)) AS count_ref2,
 SUM(IF(parent.id = child.refchid3, 1, 0)) AS count_ref3,
 SUM(IF(parent.id = child.refchid4, 1, 0)) AS count_ref4,
 COUNT(child.id) as count_ref
FROM users AS parent 
LEFT JOIN users AS child 
    ON (  parent.id = child.refchid1 
       OR parent.id = child.refchid2 
       OR parent.id = child.refchid3 
       OR parent.id = child.refchid4)
GROUP BY parent.id

Answer 2

我应该创建一个用户列表和一个单独的引用表，

CREATE TABLE reference
(
    reference_id SERIAL,
    new_user_id BIGINT UNSIGNED NOT NULL,
    refered_user_id BIGINT UNSIGNED NOT NULL,
    reference_depth INT UNSIGNED NOT NULL
);

当您添加新用户时，如果该用户被另一个用户引用，请添加一行深度为 1 的行，

INSERT INTO reference SET
    new_user_id = $new_user_id, 
    refered_user_id =  $refered_user_id, 
    reference_depth = 1

然后选择链接到该用户的所有引用并添加深度whit 1：

INSERT INTO reference (new_user_id, refered_user_id, reference_depth)
SELECT $new_user_id, refered_user_id, reference_depth +1
FROM reference WHERE new_user_id = $refered_user_id;

然后计算点

SELECT
  SUM(
    IF(reference_depth = 1, 10, 0) 
    + IF(reference_depth = 2, 3, 0) 
    + IF(reference_depth = 3, 2, 0)
    + IF(reference_depth > 3, 1, 0)
  )
FROM reference
WHERE new_user_id = $user_id

Answer 3

您是否考虑过 触发器 ？
当您直接在表的行上更新状态而不是通过视图动态生成状态（我更喜欢）时，我认为触发插入操作然后创建正确的“if”语句可能是一个好主意。

否则，我建议不要使用 status 和 refchid* 列，而只使用一个 refid ，它引用“父”id，然后使用视图来生成状态值（递归地考虑refid）。