mysql - 查询随机返回所有项目或仅返回部分项目

Question

我有 4 个表:

mysql> describe solution_sections;
+---------------------+---------------+------+-----+---------+----------------+
| Field               | Type          | Null | Key | Default | Extra          |
+---------------------+---------------+------+-----+---------+----------------+
| solution_section_id | int(10)       | NO   | PRI | NULL    | auto_increment |
| display_order       | int(10)       | NO   |     | NULL    |                |
| section_name        | varchar(1000) | YES  |     | NULL    |                |
+---------------------+---------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> describe suggested_solution_comments;

+-----------------------+----------------+------+-----+---------+----------------+
| Field                 | Type           | Null | Key | Default | Extra          |
+-----------------------+----------------+------+-----+---------+----------------+
| comment_id            | int(10)        | NO   | PRI | NULL    | auto_increment |
| problem_id            | int(10)        | NO   |     | NULL    |                |
| suggested_solution_id | int(10)        | NO   |     | NULL    |                |
| commenter_id          | int(10)        | NO   |     | NULL    |                |
| comment               | varchar(10000) | YES  |     | NULL    |                |
| solution_part         | int(3)         | NO   |     | NULL    |                |
| date                  | date           | NO   |     | NULL    |                |
+-----------------------+----------------+------+-----+---------+----------------+


mysql> describe users;
+--------------+---------------+------+-----+---------+----------------+
| Field        | Type          | Null | Key | Default | Extra          |
+--------------+---------------+------+-----+---------+----------------+
| user_id      | int(10)       | NO   | PRI | NULL    | auto_increment |
| first_name   | varchar(100)  | NO   |     | NULL    |                |
| last_name    | varchar(100)  | NO   |     | NULL    |                |
| email        | varchar(150)  | NO   |     | NULL    |                |
| user_pass    | varchar(40)   | NO   |     | NULL    |                |
| zip          | varchar(100)  | NO   |     | NULL    |                |
| country      | varchar(100)  | NO   |     | NULL    |                |
| city         | varchar(100)  | NO   |     | NULL    |                |
| state        | varchar(100)  | NO   |     | NULL    |                |
| lat          | float(9,6)    | YES  |     | NULL    |                |
| lng          | float(9,6)    | YES  |     | NULL    |                |
| agreed_terms | tinyint(1)    | YES  |     | NULL    |                |
| join_date    | date          | NO   |     | NULL    |                |
| last_login   | date          | NO   |     | NULL    |                |
| bio_blurb    | varchar(5000) | YES  |     | NULL    |                |
+--------------+---------------+------+-----+---------+----------------+
15 rows in set (0.03 sec)

mysql> describe member_photo;
+-------------------+---------------+------+-----+---------+----------------+
| Field             | Type          | Null | Key | Default | Extra          |
+-------------------+---------------+------+-----+---------+----------------+
| photo_id          | int(10)       | NO   | PRI | NULL    | auto_increment |
| member_id         | int(10)       | NO   |     | NULL    |                |
| photo_description | varchar(3000) | YES  |     | NULL    |                |
| photo_path        | varchar(1000) | NO   |     | NULL    |                |
| small_thumb       | varchar(1000) | YES  |     | NULL    |                |
| mid_thumb         | varchar(1000) | YES  |     | NULL    |                |
| is_main_photo     | tinyint(1)    | YES  |     | NULL    |                |
+-------------------+---------------+------+-----+---------+----------------+

我有这样的查询:

select comment_id,
       commenter_id,
       section_name,
       comment,
       solution_part,
       display_order,
       solution_section_id,
       suggested_solution_id, 
       DAYOFMONTH(date),
       DAYNAME(date),
       YEAR(date),
       MONTH(date),
       first_name,
       last_name,
       email,
       small_thumb,
       mid_thumb 
from solution_sections 
left join suggested_solution_comments on
    solution_sections.solution_section_id = suggested_solution_comments.solution_part 
left join users on
    suggested_solution_comments.commenter_id = users.user_id  
left join member_photo on
    suggested_solution_comments.commenter_id = member_photo.member_id
where suggested_solution_id = 61 OR 
      suggested_solution_id IS NULL
order by solution_section_id,
         comment_id,
         section_name,
         comment,
         solution_part,
         display_order;

它应该做的是从 solution_sections 表中获取每个 section_name，然后找到评论(以及关于评论者的数据)。有时没有评论，但它仍应至少返回带有 section_name 且所有其他内容均为 null 的行。

但出于某种原因，它没有。最奇怪的是，如果我给它一个不同的 suggested_solution_id 来匹配，它将返回 solution_sections 的所有行。

知道为什么会发生这样的事情吗？谢谢!!

我刚刚意识到一件事 - 如果对任何 problem_id 进行了另一条评论，则此查询将不会返回包含该部分的行。

Answer 1

当不能保证子记录退出时，您需要一个左外部连接来查看父表中的所有记录。我也会避免在使用外连接时添加 where 子句。我认为将连接保留在子选择中并过滤结果更具可读性。尝试这样的事情:

select * from 
(
    select sc.comment_id,
           sc.commenter_id,
           ss.section_name,
           sc.comment,
           sc.solution_part,
           ss.display_order,
           ss.solution_section_id,
           sc.suggested_solution_id, 
           DAYOFMONTH(sc.date),
           DAYNAME(sc.date),
           YEAR(sc.date),
           MONTH(sc.date),
           u.first_name,
           u.last_name,
           u.email,
           mp.small_thumb,
           mp.mid_thumb 
    from solution_sections ss
    left outer join suggested_solution_comments sc on ss.solution_section_id = sc.solution_part
    left outer join users u on sc.commenter_id = u.user_id  
    left outer join member_photo mp on sc.commenter_id = mp.member_id) a
where a.suggested_solution_id = 61 OR 
      a.suggested_solution_id IS NULL
order by a.solution_section_id,
         a.comment_id,
         a.section_name,
         a.comment,
         a.solution_part,
         a.display_order;

编辑:

select sc.comment_id,
       sc.commenter_id,
       ss.section_name,
       sc.comment,
       sc.solution_part,
       ss.display_order,
       ss.solution_section_id,
       sc.suggested_solution_id, 
       DAYOFMONTH(sc.date),
       DAYNAME(sc.date),
       YEAR(sc.date),
       MONTH(sc.date),
       u.first_name,
       u.last_name,
       u.email,
       mp.small_thumb,
       mp.mid_thumb 
from solution_sections ss
left outer join suggested_solution_comments sc on ss.solution_section_id = sc.solution_part 
                                               AND sc.suggested_solution_id = 61
left outer join users u on sc.commenter_id = u.user_id  
left outer join member_photo mp on sc.commenter_id = mp.member_id
order by solution_section_id,
         comment_id,
         section_name,
         comment,
         solution_part,
         display_order;