SQL：用于 CASE 语句的别名列名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1583609/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL: Alias Column Name for Use in CASE Statement
提问by Verhogen
Is it possible to alias a column name and then use that in a CASE statement? For example,
是否可以为列名添加别名，然后在 CASE 语句中使用它？例如，
SELECT col1 as a, CASE WHEN a = 'test' THEN 'yes' END as value FROM table;
I am trying to alias the column because actually my CASE statement would be generated programmatically, and I want the column that the case statement uses to be specified in the SQL instead of having to pass another parameter to the program.
我正在尝试为该列设置别名，因为实际上我的 CASE 语句将以编程方式生成，并且我希望在 SQL 中指定 case 语句使用的列，而不必将另一个参数传递给程序。
采纳答案by eKek0
I think that MySql and MsSql won't allow this because they will try to find all columns in the CASE clause as columns of the tables in the WHERE clause.
我认为 MySql 和 MsSql 不允许这样做，因为它们会尝试将 CASE 子句中的所有列查找为 WHERE 子句中表的列。
I don't know what DBMS you are talking about, but I guess you could do something like this in any DBMS:
我不知道你在说什么 DBMS，但我想你可以在任何 DBMS 中做这样的事情：
SELECT *, CASE WHEN a = 'test' THEN 'yes' END as value FROM (
SELECT col1 as a FROM table
) q
回答by OMG Ponies
This:
这个：
SELECT col1 as a,
CASE WHEN a = 'test' THEN 'yes' END as value
FROM table;
...will notwork. This will:
...不会工作。这将：
SELECT CASE WHEN a = 'test' THEN 'yes' END as value
FROM (SELECT col1 AS a
FROM TABLE)
Why you wouldn't use:
为什么你不会使用：
SELECT t.col1 as a,
CASE WHEN t.col1 = 'test' THEN 'yes' END as value
FROM TABLE t;
...I don't know.
...我不知道。
回答by Tarunjit Singh
@OMG Ponies - One of my reasons of not using the following code
@OMG Ponies - 我不使用以下代码的原因之一
SELECT t.col1 as a,
CASE WHEN t.col1 = 'test' THEN 'yes' END as value
FROM TABLE t;
can be that the t.col1 is not an actual column in the table. For example, it can be a value from a XML column like
可能是 t.col1 不是表中的实际列。例如，它可以是来自 XML 列的值，如
Select XMLColumnName.value('(XMLPathOfTag)[1]', 'varchar(max)')
as XMLTagAlias from Table
回答by ali
It should work. Try this
它应该工作。尝试这个
Select * from
(select col1, col2, case when 1=1 then 'ok' end as alias_col
from table)
as tmp_table
order by
case when @sortBy = 1 then tmp_table.alias_col end asc
回答by jason saldo
I use CTEs to help compose complicated SQL queries but not all RDBMS' support them. You can think of them as query scope views. Here is an example in t-sql on SQL server.
我使用 CTE 来帮助编写复杂的 SQL 查询，但并非所有 RDBMS 都支持它们。您可以将它们视为查询范围视图。这是 SQL 服务器上 t-sql 中的一个示例。
With localView1 as (
select c1,
c2,
c3,
c4,
((c2-c4)*(3))+c1 as "complex"
from realTable1)
, localView2 as (
select case complex WHEN 0 THEN 'Empty' ELSE 'Not Empty' end as formula1,
complex * complex as formula2
from localView1)
select *
from localView2
回答by Mr.Buntha Khin
If you write only equal condition just: Select Case columns1 When 0 then 'Value1' when 1 then 'Value2' else 'Unknown' End
If you want to write greater , Less then or equal you must do like this: Select Case When [ColumnsName] >0 then 'value1' When [ColumnsName]=0 Or [ColumnsName]<0 then 'value2' Else 'Unkownvalue' End
如果你只写相等的条件： Select Case columns1 When 0 then 'Value1' when 1 then 'Value2' else 'Unknown' End
如果你想写更大，小于或等于你必须这样做： Select Case When [ColumnsName] >0 then 'value1' When [ColumnsName]=0 Or [ColumnsName]<0 then 'value2' Else 'Unkownvalue' End
From tablename
从表名
Thanks Mr.Buntha Khin
感谢 Buntha Khin 先生
回答by Evakm25
SELECT
a AS [blabla a],
b [blabla b],
CASE c
WHEN 1 THEN 'aaa'
WHEN 2 THEN 'bbb'
ELSE 'unknown'
END AS [my alias],
d AS [blabla d]
FROM mytable
回答by Asaph
Not in MySQL. I tried it and I get the following error:
在 MySQL 中没有。我试过了，出现以下错误：
ERROR 1054 (42S22): Unknown column 'a' in 'field list'回答by PostMan
Nor in MsSql
在 MsSql 中也没有
SELECT col1 AS o, e = CASE WHEN o < GETDATE() THEN o ELSE GETDATE() END
FROM Table1
Returns:
返回：
Msg 207, Level 16, State 3, Line 1
Invalid column name 'o'.
Msg 207, Level 16, State 3, Line 1
Invalid column name 'o'.
However if I change to CASE WHEN col1... THEN col1 it works
但是，如果我更改为 CASE WHEN col1... THEN col1 它可以工作
回答by Abdur Rahman
In MySql, alice name may not work, therefore put the original column name in the CASE statement
在MySql中，alice名称可能不起作用，因此将原始列名放在CASE语句中
SELECT col1 as a, CASE WHEN col1 = 'test' THEN 'yes' END as value FROM table;
Sometimes above query also may return error, I don`t know why (I faced this problem in my two different development machine). Therefore put the CASE statement into the "(...)" as below:
有时上面的查询也可能返回错误，我不知道为什么（我在两台不同的开发机器上遇到了这个问题）。因此将 CASE 语句放入 "(...)" 中，如下所示：
SELECT col1 as a, (CASE WHEN col1 = 'test' THEN 'yes' END) as value FROM table;
