mysql - 利用在一个 SQL 查询中完成的工作来简化第二个 SQL 查询？

Question

我有一个数据库，其中包括以下两个表:

classes 是一个简单的表格，在类(class)表中每个类(class)一行。

sessions 是一个描述每个类(class)开会的日期和时间的表格，其中每一行都能够表达如下概念:

"Tuesdays | Jan 22-Mar 5 | 6-9pm"
"Tuesdays & Thursdays | Jan 22-Mar 7 | 6-9pm"
"Monday-Thursday | Jan 21-24 | 3-6pm"
"Saturday | Mar 9 | 9am-4pm"

等等。

对于 classes 中的每一行，保证在 sessions 中至少有一行，并且对于某些类，可能有两个或多个关联的 session 行。

目前，我使用两个不同的查询来获取符合一组特定条件的类的类和 session 信息，如下所示:

select c.class_id, c.title, c.instructor, c.num_seats, c.price
  from classes c
  join classes_by_department cbd 
    on (cbd.class_id = c.class_id)
  join /* several other tables */
    on /* several other join conditions */
 where cbd.department_id = '{$dept_id}'
   and /* several other qualifying conditions */
;

还有这个:

select s.class_id, s.start_date, s.end_date, s.day_bits, s.start_time, s.end_time
  from sessions s
  join classes c
    on (c.class_id = s.class_id)
  join classes_by_department cbd
    on (cbd.class_id = s.class_id) 
  join /* the same other tables */
    on /* the same other join conditions */
 where cbd.department_id = '{$dept_id}'
   and /* the same other qualifying conditions */
;

这工作得很好，而且——至少在当前的应用程序中——表不够大，流量也不够大，两个查询不会成为问题。尽管如此，这让我觉得有点浪费，我想知道是否没有办法更好地利用第一个查询已经完成的工作来执行第二个查询(而不是运行相同的查询两次和只需选择不同的列)。

当然，我知道我可以在单个查询(第二个查询)中从 classes 和 sessions 中选择所有相关列，但我喜欢这样的事实在目前的方法中，第一个查询只为每个符合条件的类(class)提供一行，而不是与类(class)有 session 记录的行数一样多。如果合并查询，我将需要重组处理查询结果的现有逻辑。 (是的，我知道，哇...)

我想到的一个解决方案是将第一个查询返回的所有 class_id 收集到一个向量中(因为无论如何我都必须遍历这些结果)然后格式化该向量的内容作为 IN 子句的 value-list 的内容，因此第二个查询将简单地变为:

select s.class_id, s.start_date, s.end_date, s.day_bits, s.start_time, s.end_time
  from sessions s
 where s.class_id in (/* value-list */);

我不太担心这种解决方案的可扩展性，据我所知 huge SQL queries are no big deal .此外，它还可以利用在 sessions.class_id 上定义的索引。

但是……好吧……对于希望提高 SQL 能力的人来说，这并不是很令人满意，我承认这还很初级。它感觉不雅，而且不是很“SQL-ish”，或者与术语 Pythonic 等价的 SQL。

谁能提出更合适的建议？

Answer 1

做你想做的事情的规范方法是使用 View 。将您的第一个查询定义为:

create view vw_MyClasses as
    select c.class_id, c.title, c.instructor, c.num_seats, c.price, cbd.department_id
    from classes c
         join classes_by_department cbd 
         on (cbd.class_id = c.class_id)
         join /* several other tables */
         on /* several other join conditions */
   where /* several other qualifying conditions */

那么你的类查询将是:

select *
from vw_MyClasses
where department_id = '{$dept_id}'

然后，您的第二个查询可以是:

 select s.class_id, s.start_date, s.end_date, s.day_bits, s.start_time, s.end_time
 from sessions s
 where s.class_id in (select class_id from vw_MyClasses 
                                      where department_id = '{$dept_id}');

或者，在 MySQL 中什么可能更有效:

 select s.class_id, s.start_date, s.end_date, s.day_bits, s.start_time, s.end_time
 from sessions s
 where exists (select 1 from vw_MyClasses mc where mc.class_id = s.class_id limit 1)

这样做是有充分理由的。在多个查询中重复这样的逻辑成为维护的噩梦。当您在一个地方修改逻辑时，很容易忘记在所有地方进行修改。有时， View 不够用，因此您可能需要使用用户定义的函数，如 here 所述.

此外，如果条件非常有用，您可能希望在类表中放置标志以识别它们。这需要以某种方式维护它们，例如每晚更新或使用触发器。