当前位置: 动力学知识库 > 问答 > 编程问答 >

MYSQL filtering using AS name of column

问题描述:

I'm using this query (minimal version) to filter some data from a table

SELECT DISTINCT

t.name AS tname,

CASE WHEN (t.task_group != -1) THEN (SELECT p.keyid FROM proyects AS p, task_groups AS tg WHERE tg.keyid = t.task_group AND tg.proyect = p.keyid) ELSE -1 END AS pkeyid

FROM tasks AS t, task_users AS tu

WHERE (t.status = '0' OR t.status = '1' OR t.status = '3') AND (t.keyid = tu.task) AND ((tu.worker = 5));

The query works perfectly. But now I want to filter some of the rows by doing:

SELECT DISTINCT

t.name AS tname,

CASE WHEN (t.task_group != -1) THEN (SELECT p.keyid FROM proyects AS p, task_groups AS tg WHERE tg.keyid = t.task_group AND tg.proyect = p.keyid) ELSE -1 END AS pkeyid

FROM tasks AS t, task_users AS tu

WHERE (t.status = '0' OR t.status = '1' OR t.status = '3') AND (t.keyid = tu.task) AND ((tu.worker = 5)) AND ((pkeyid = 7) OR (pkeyid = 8) OR (pkeyid = 16))

I get the syntax error "Unknown column 'pkeyid' in 'where clause'"

I can understand why this is happening (pkeyid is not a column in any of the tables in the from clause). Is there a simple way around this? Or do I have to rewrite the entire query?

网友答案:

You can not use the alias name in the where clause, you need to use having clause if you want that to be used in the filtering something as

SELECT DISTINCT 
t.name AS tname, 
CASE WHEN (t.task_group != -1) THEN (SELECT p.keyid FROM proyects AS p, task_groups AS tg WHERE tg.keyid = t.task_group AND tg.proyect = p.keyid) ELSE -1 END AS pkeyid 
FROM tasks AS t, task_users AS tu 
WHERE 
(t.status = '0' OR t.status = '1' OR t.status = '3') 
AND t.keyid = tu.task 
AND tu.worker = 5
having
pkeyid = 7 or pkeyid = 8 or pkeyid = 16
分享给朋友:
您可能感兴趣的文章:
随机阅读: