mysql - 引用外部查询/子查询的最佳方式？

Question

我试图在第三个选择(子查询)表中引用第一个选择表中的字段。但是，当该字段转到查询的该子级别时，该字段无法识别。我正在处理的 php 代码使用 sql 返回将在其他地方使用的部分 sql 命令(字符串)。

我想出了这个例子，它显示了我想要解决的嵌套查询。在这里，我试图获取夜间工作的用户的姓名和电子邮件，并且具有与可用工作匹配的工作等级:

tables -----------> fields
table_users      -> [user_id, name, email, rank, ...]
table_users_jobs -> [user_id, job_id, period, ....]
table_jobs       -> [job_id, status, rank, ...]


-- sql calling code -> $rank = "t1.rank"; get_users_info_by_rank($rank);
-- maybe using: SET @rank = NULL; SELECT @rank := $rank, t1.name, ...
   SELECT t1.name, t1.email
   FROM table_users as t1
   WHERE t1.user_id IN (
       SELECT t2.user_id
       FROM table_users_jobs as t2
       WHERE t2.period = 'night' AND
         t2.job_id IN (
--         avaiable jobs to that rank -> get_job_ranks_sql($rank);
           SELECT t3.job_id 
           FROM table_jobs as t3
--         maybe using: t3.rank = @rank
           WHERE t3.rank = t1.rank AND
            t3.status = 'avaiable_position')
       )

工作一点我想我可以避免第 3 级选择问题。尽管如此，关键是我正在尝试重用 sql 代码，例如为我提供我选择的等级的 job_id 的函数:

  function get_job_ranks_sql($rank){
    //probably 't3' will be renamed for something more unique
    return 'SELECT t3.job_id 
            FROM table_jobs as t3
            WHERE t3.rank = '.$rank.' AND
            t3.status = "available_position")';
  }

即使使用 php，我也在尝试使其通用化，以便在可能的情况下与其他语言一起使用。使用的sql版本为MySQL 5.1.41

实际上我认为这可能是我想要的方式，通过使用像@rank 这样的 sql 变量，但我不确定它是否更慢以及是否有其他更好的方法 去做。

在此先感谢您的帮助:)

Answer 1

因此，正如一位评论者所指出的，我认为使用 JOINS 比子选择要好得多。例如，如果我正确地阅读了您的查询/问题，您可以执行这样的连接查询:

SELECT t1.name, t1.email, t3.job_id
FROM table_users t1
LEFT JOIN table_users_job t2
ON t1.user_id = t2.user_id
LEFT JOIN table_jobs t3
ON t3.job_id = t2.job_id
WHERE t2.period = 'night
AND t3.status = 'available_position'

它更简洁、更易于阅读，并且在您的数据库上也更容易。但是这样做会阻止您模块化 SQL。如果这真的很重要，您可以考虑将此类查询存储在存储过程中。这样，您实际上可以让 SP 返回结果列表。看看这个教程:

http://www.wellho.net/resources/ex.php4?item=s163/stp4

当然，这并不能真正解决您能够在子选择的较低级别访问变量的问题，但它会使您的 SQL 更易于管理，并使其可用于其他语言实现，因为您提到的可能是您的需要。

从更大的角度来看，其他需要考虑的事情是迁移到提供 ORM 层的 PHP 框架，您可以在其中将这些表制作成对象，然后能够更轻松、更灵活地访问您的数据 (通常)。但那是非常“大局”，可能不适合您的项目要求。然而，我可以推荐的一个这样的框架是 CakePHP。