引用外部查询/子查询的最佳方式? [英] Best way to reference an outer query / subquery?

问题描述

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

I'm trying to reference a field from the 1st select table in the 3rd select(subquery) table. However, that field isn't recognized when it goes to that sub-level of a query. The php code I'm working on uses sql to return part of the sql command (string) that will be used in other places.

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

I've came up with this example that shows up the kind of nested querys that I want to solve. In here I'm trying to get the name and emails of users that are working at night and have a matching job rank for an available job:

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')
       )

我想我可以避免第三级选择问题.尽管如此，关键是我正在尝试重用 sql 代码，例如为我提供所选职级的 job_id 的函数:

Working a little I guess I could avoid the 3rd level select problem. Nevertheless the point is that I'm trying to reuse sql code like the function that gives me the job_id of the rank that I chose:

  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

Even using php I'm trying to make it generic to maybe use with another language if possible. The sql version using is MySQL 5.1.41

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

Actually I think it's possible the way I want, by using sql variables like @rank, but I'm not sure if it's slower and if there are other better ways to do it.

提前感谢您的帮助:)

推荐答案

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

So, as one commenter pointed out, I think you would do much better off using JOINS, than sub-selects. For example, if I am reading your query/problem correctly, you could do a join query like this:

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 来返回一个结果列表.看看这个教程:

Which is a lot more concise, easier to read, and is easier on your database. But doing this would prevent you from modularizing your SQL. If that is really important, you might consider storing such queries in Stored Procedure. This way, you can actually get a SP to return a list of results. Take a look at this tutorial:

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

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

Of course, that doesn't really solve your problem of being able to access variables at the lower levels of a sub select, but it would make your SQL easier to manage, and make it available to other language implementations, as you mentioned might be a need for you.

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

Something else to consider, in the bigger picture, would be migrating to a PHP framework that provides an ORM layer, where you could make those tables into objects, and then be able to access your data with much greater ease and flexibility (usually). But that is very 'big picture' and might not be suitable for your project requirements. One such framework that I could recommend, however, is CakePHP.

这篇关于引用外部查询/子查询的最佳方式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！