mysql - 在另一个嵌套查询中使用嵌套查询结果

Question

SQL FIDDLE:http://sqlfiddle.com/#!2/5895f/5

我有组，每个组(item_group)可以有很多项目(item)，每个项目每个用户有一个状态条目(item_status>).我正在尝试检索不是来自您自己的每个组的最后一项。将其视为消息线程，您只想将该线程的预览视为已发送但不是您发送的最后一条消息。

这适用于我的查询

  SELECT * FROM (
    SELECT it.id as group_id, i.sender_id as sender_id
    FROM item_group it
    JOIN item i ON (i.item_group_id = it.id)
    JOIN item_status s ON (s.item_id = i.id)
  ) as results
  WHERE results.sender_id != 2  
  GROUP BY results.group_id;

然而，下一步是我想确保我的项目中任何项目的状态都不是 0，这就是我的问题所在。它说 Unknown column 'results.group_id'

  SELECT * FROM (
    SELECT it.id as group_id, i.sender_id as sender_id
    FROM item_group it
    JOIN item i ON (i.item_group_id = it.id)
    JOIN item_status s ON (s.item_id = i.id)
  ) as results
  WHERE results.sender_id != 2
  AND (SELECT COUNT(*) = 0 
       FROM item_status s
       --- PROBLEM IS HERE results.group_id ---
       JOIN item i ON (i.item_group_id = results.group_id AND s.item_id = i.id) 
       ---------------------------------------- 
       WHERE s.user_id = 2 AND s.status = 0)
  GROUP BY results.group_id;



 CREATE TABLE item_group (
  id      int(10) PRIMARY KEY NOT NULL AUTO_INCREMENT);

  CREATE TABLE item (
  id             int(10) PRIMARY KEY NOT NULL AUTO_INCREMENT, 
  item_group_id  int(10) NOT NULL, 
  sender_id MEDIUMINT UNSIGNED NOT NULL);

  CREATE TABLE item_status (
  item_id     int(10) NOT NULL, 
  user_id     MEDIUMINT UNSIGNED NOT NULL, 
  status      int(10) NOT NULL);

  INSERT INTO item_group (ID) VALUES (1);
  INSERT INTO item (item_group_id, sender_id) VALUES (1, 1);
  INSERT INTO item (item_group_id, sender_id) VALUES (1, 2);
  INSERT INTO item_status (item_id, user_id, status) VALUES (1, 1, 1);
  INSERT INTO item_status (item_id, user_id, status) VALUES (1, 2, 1);
  INSERT INTO item_status (item_id, user_id, status) VALUES (2, 1, 1);
  INSERT INTO item_status (item_id, user_id, status) VALUES (2, 2, 1);

Answer 1

我会用下面的方式写这个，没有子查询:

SELECT i.item_group_id as group_id, i.sender_id as sender_id
FROM item i 
JOIN item_status s ON (s.item_id = i.id)
LEFT OUTER JOIN (
    item i2 INNER JOIN item_status s2 
      ON (s2.item_id = i2.id AND s2.user_id = 2 AND s2.status = 0) 
) ON (i.item_group_id = i2.item_group_id)
WHERE i.sender_id != 2 AND i2.item_group_id IS NULL
GROUP BY group_id ORDER BY NULL;

您还应该创建以下索引:

ALTER TABLE item_status 
    ADD KEY (user_id, status),
    ADD KEY (item_id);

我在 MySQL 5.5.30 上测试了这个查询。有了推荐的索引，优化器EXPLAIN报告如下:

+----+-------------+-------+--------+-----------------+---------+---------+-----------------+------+----------------------------------------------+
| id | select_type | table | type   | possible_keys   | key     | key_len | ref             | rows | Extra                                        |
+----+-------------+-------+--------+-----------------+---------+---------+-----------------+------+----------------------------------------------+
|  1 | SIMPLE      | i     | ALL    | PRIMARY         | NULL    | NULL    | NULL            |    2 | Using where; Using temporary                 |
|  1 | SIMPLE      | s2    | ref    | user_id,item_id | user_id | 7       | const,const     |    0 |                                              |
|  1 | SIMPLE      | i2    | eq_ref | PRIMARY         | PRIMARY | 4       | test.s2.item_id |    1 | Using where                                  |
|  1 | SIMPLE      | s     | ref    | item_id         | item_id | 4       | test.i.id       |    1 | Using index                                  |
+----+-------------+-------+--------+-----------------+---------+---------+-----------------+------+----------------------------------------------+

---

对于您的后续问题，我想到了一个快速解决方案，即如何获取所有 状态为 0 的项目组。

SELECT i.item_group_id as group_id, i.sender_id as sender_id
FROM item i 
JOIN item_status s ON (s.item_id = i.id)
LEFT OUTER JOIN (
    item i2 INNER JOIN item_status s2 
      ON (s2.item_id = i2.id AND s2.user_id = 2 AND s2.status <> 0) 
) ON (i.item_group_id = i2.item_group_id)
WHERE i.sender_id != 2 AND i2.item_group_id IS NULL
GROUP BY group_id ORDER BY NULL;

这与上面的查询几乎相同，只是我更改了 = 0至 <> 0在加入条件。所以这意味着:

"try to find any non-zero status, and if you can't find any, then the OUTER JOIN will return null for all columns of the joined tables. When that happens, you've found an item group with all zero statuses."

PS:我还没有测试过这个，只是提供它作为一个快速解决方案。