时间戳

首页 > 解决方案 > 如何进一步过滤 SELECT 查询的结果？

mysql - 如何进一步过滤 SELECT 查询的结果？

问题描述

好的，所以我有这个简单的 Mysql 查询：

SELECT * 
FROM student
LEFT JOIN student_state ON student.student_id = student_state.student_id_fk
WHERE student_surname LIKE '%XXX%' 
   OR student_lastname LIKE '%XXX%'

它会返回一些结果。

我想获取这些结果并通过第二个选择进一步过滤它，如下所示：

SELECT * 
FROM 
    (SELECT student_payment 
     FROM student
     LEFT JOIN student_state ON student.student_id = student_state.student_id_fk
     WHERE student_surname LIKE '%XXX%' 
        OR student_lastname LIKE '%XXX%') AS test 
WHERE  
    student_payment LIKE '%XXX%' 
    OR student_unlock  LIKE '%XXX%'

当我只是将第一个SELECT插入FROM括号时，我收到一个需要别名的错误。当我为子查询提供别名时，它说

未知列 student_payment 或 student_unlock

有任何想法吗？

标签： mysql

解决方案

Your 1st query returns only 1 column but in the 2nd you try to filter by 2 columns.
So include both columns is your 1st query:

SELECT t.* FROM (
  SELECT student_payment, student_unlock 
  FROM student LEFT JOIN student_state 
  ON student.student_id = student_state.student_id_fk
  WHERE student_surname LIKE '%XXX%' OR student_lastname LIKE '%XXX%'
) t
WHERE t.student_payment LIKE '%XXX%' OR t.student_unlock  LIKE '%XXX%'

You can get the same results with this:

SELECT student_payment, student_unlock 
FROM student LEFT JOIN student_state 
ON student.student_id = student_state.student_id_fk
WHERE 
  (student_surname LIKE '%XXX%' OR student_lastname LIKE '%XXX%')
  AND
  (t.student_payment LIKE '%XXX%' OR t.student_unlock  LIKE '%XXX%')

推荐阅读