Mysql 引用子查询结果在父 where 子句中 [英] Mysql Reference subquery result in parent where clause

问题描述

在我们学校，当一个学生表现好时，他们会得到一个存储在数据库中的虚拟英镑(或美元).

In our school, when a student is good, they get given a virtual pound (or dollar) which is stored in a database.

这是我的查询:

SELECT s.chosen_name, s.chosen_surname, s.regId, s.admission_number, 
    (
        SELECT SUM(a.pounds_amount) 
        FROM tbl_pounds_log a 
        WHERE a.student_id=l.student_id
    ) AS total_pounds, 
    (
        SELECT SUM(b.pounds_amount) 
        FROM tbl_pounds_log b 
        WHERE b.student_id=l.student_id 
        AND b.staff_id=:staffId 
        AND b.action_timestamp>(UNIX_TIMESTAMP()-3600) 
    ) AS available_pounds 
FROM TBL_student s 
LEFT JOIN tbl_pounds_log l 
    ON l.student_id=s.admission_number 
WHERE ((s.chosen_name LIKE :termOne AND s.chosen_surname LIKE :termTwo) 
        OR (s.chosen_name LIKE :termThree AND s.chosen_surname LIKE :termFour))
    AND (total_pounds>=:lowerPoundLimit 
        AND total_pounds<=:upperPoundLimit) 
GROUP BY s.admission_number 
ORDER BY s.chosen_surname ASC, s.chosen_name ASC  
LIMIT 0,10

(我使用 PHP PDO 来执行查询，因此使用 :text 占位符).

(I'm using PHP PDO to perform the query hence the :text placeholders).

当涉及到父查询中的 WHERE 条件时，我遇到了一些问题.

I'm having a bit of an issue when it comes to the WHERE condition in the parent query.

它说的是:

... AND (total_pounds>=:lowerPoundLimit and total_pounds<=:upperPoundLimit)

total_pounds 字段是子查询的列结果，但是当我运行查询时，它不断出现:

The total_pounds field is a column result of a subquery, however when I run the query it keeps coming up with:

Unknown column 'total_pounds' in 'where clause'

有人知道解决办法吗?

谢谢

菲尔

推荐答案

问题是在评估 WHERE 子句时，别名还不可用.应该通过将子查询从 SELECT 子句移动到 JOIN 子句中来解决这个问题:

The problem is that the alias names is not yet available when the WHERE clause is evaluated. The problem should be solved by moving the subqueries from the SELECT clause into JOIN clauses like this:

SELECT s.chosen_name, s.chosen_surname, s.regId, s.admission_number, 
       total_pounds.pound_amount as total_pounds,
       available_pounds.pound_amount as available_pounds
FROM TBL_student s
LEFT JOIN
  (SELECT student_id, SUM(pounds_amount) AS pound_amount 
   FROM tbl_pounds_log
   GROUP BY student_id) AS total_pounds
ON total_pounds.student_id = s.admission_number 
LEFT JOIN
  (SELECT student_id, SUM(b.pounds_amount) AS pound_amount
   FROM tbl_pounds_log 
   WHERE b.staff_id=:staffId 
   AND b.action_timestamp>(UNIX_TIMESTAMP()-3600)
   GROUP BY student_id) as available_pounds  
ON available_pounds.student_id = s.admission_number
WHERE ((s.chosen_name LIKE :termOne AND s.chosen_surname LIKE :termTwo) 
        OR (s.chosen_name LIKE :termThree AND s.chosen_surname LIKE :termFour))
    AND (total_pounds.pound_amount >= :lowerPoundLimit 
        AND total_pounds.pound_amount <= :upperPoundLimit) 
GROUP BY s.admission_number 
ORDER BY s.chosen_surname ASC, s.chosen_name ASC  
LIMIT 0,10

似乎也可以编写没有子查询的查询:

It is also seems possible to write the query without subqueries:

SELECT s.chosen_name, s.chosen_surname, s.regId, s.admission_number,
       SUM(tp.pounds_amount) AS total_pounds
       SUM(ap.pounds_amount) AS available pounds
FROM tbl_students s
LEFT JOIN tbl_pounds_log tp
ON tp.student_id = s.admission_number
LEFT JOIN tbl_pounds_log ap
ON ap.student_id = tp.student_id
AND ap.staff_id = tp.staff_id
AND ap.staff_id = :staffId
AND ap.action_timestamp = tp.action_timestamp
AND ap.action_timestamp > (UNIX_TIMESTAMP()-3600)
WHERE s.chosen_name LIKE :termOne AND s.chosen_surname LIKE :termTwo
OR s.chosen_name LIKE :termThree AND s.chosen_surname LIKE :termFour
GROUP BY s.admission_number, s.name, s.regId, s.admission_number
HAVING SUM(tp.pounds_amount) BETWEEN upperPoundLimit AND lowerPoundLimit
ORDER BY s.chosen_surname, s.chosen_name
LIMIT 0,10

这篇关于Mysql 引用子查询结果在父 where 子句中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！