引用外部查询/子查询的最佳方法? [英] 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')
       )

稍作努力，我想我可以避免第3级选择问题.不过，关键是我试图重用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.

在此先感谢您的帮助:)

Thanks in advance for any help :)

推荐答案

因此，正如一位评论者所指出的，我认为使用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屋！