mysql - 如何使用 split_str 编写子查询

Question

我编写了一个查询，并在我的一个字段中使用了 SPLIT_STR

SELECT id, approved_studied, 
       SPLIT_STR(approved_studied, '||', 8) as dissertation 
       SPLIT_STR(approved_studied, '||', 7) as university
FROM temp_users

现在我要过滤

WHERE dissertation LIKE '%Planting%' OR university LIKE '%Oxford%'

这个子查询怎么写？这是我到目前为止所想出的:

SELECT P1.approved_studied, P1.dissertation
FROM P1 
WHERE P1.dissertation LIKE '%Planting%' OR university LIKE '%Oxford%' (
     SELECT id, approved_studied, 
            SPLIT_STR(approved_studied, '||', 8) as dissertation 
            SPLIT_STR(approved_studied, '||', 7) as university
     FROM temp_users AS P1
)

这是非常旧的数据和编码:(我尝试创建sql fiddle，但它不允许我继续。http://sqlfiddle.com/#!2/3bca40/7

Answer 1

这里不需要子查询，只需添加where条件即可:

SELECT id, approved_studied, 
       SPLIT_STR(approved_studied, '||', 8) as dissertation,
       SPLIT_STR(approved_studied, '||', 7) as university  
FROM temp_users
WHERE SPLIT_STR(approved_studied, '||', 8) LIKE '%Planting%' AND 
      SPLIT_STR(approved_studied, '||', 7) LIKE '%oxford%'

请注意，您不能在 where 条件中使用别名。

字符串操作不会运行两次。