WHERE子句中的列别名 [英] Column Alias in a WHERE Clause

问题描述

我在查询中使用备用列名(别名)，我可以将别名"given_name"用作ORDER BY的一部分，但不能将其用作WHERE子句的一部分. WHERE"given_name"作为请求的结果传入我的控件之外，我不知道在WHERE条件下应使用的实际列名.

I am using alternate column name (alias) in a Query, I can use the alias "given_name" as part of the ORDER BY but am unable to use it as part of the WHERE clause. The WHERE "given_name" is passed in as the result of a request out of my control and I do not know the actual column name that should be used in the WHERE condition.

1. 有没有办法在WHERE子句中使用列别名?
2. 有没有办法从别名中查找列名?

研究

经过一些研究，似乎在WHERE子句之后添加了别名.

Research

After some research it looks like alias are added after after the WHERE clause.

SELECT profile.id AS id, given.name AS 'given_name', family.name AS 'family_name'
FROM green_profile profile 
LEFT JOIN green_name given ON given.profileid = profile.id AND given.name_typeid = 0 
LEFT JOIN green_name family ON family.profileid = profile.id AND family.name_typeid = 1 
WHERE given_name LIKE 'levi%' 
ORDER BY given_name DESC LIMIT 0 , 25

推荐答案

未经测试，但是此hack应该有效...

Untested, but this hack should work...

SELECT * FROM (  
    SELECT profile.id AS id, given.name AS 'given_name', family.name AS 'family_name'
    FROM green_profile profile 
    LEFT JOIN green_name given ON given.profileid = profile.id AND given.name_typeid = 0 
    LEFT JOIN green_name family ON family.profileid = profile.id AND family.name_typeid = 1   
) as temptable
WHERE given_name LIKE 'levi%' 
ORDER BY given_name DESC LIMIT 0 , 25

它的工作原理是简单地从原始的select语句(没有where子句和顺序)创建一个临时表，该表具有您指定的列名.然后，从中选择所需的列名.

It works by simply creating a temporary table from your original select statement (without the where clause and ordering), which has the column names you specify. You then select from this with the column names you want.

更好的方法可能是创建一个具有所需列名的视图，然后从该视图中选择...

A better approach might be to create a view, with the column names you want, and select from the view...

CREATE VIEW newtable AS
SELECT profile.id AS id, given.name AS 'given_name', family.name AS 'family_name'
FROM green_profile profile 
LEFT JOIN green_name given ON given.profileid = profile.id AND given.name_typeid = 0 
LEFT JOIN green_name family ON family.profileid = profile.id AND family.name_typeid = 1;

然后...

SELECT * FROM newtable
WHERE given_name LIKE 'levi%' 
ORDER BY given_name DESC LIMIT 0 , 25

这篇关于WHERE子句中的列别名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！