SQL：用于CASE语句的别名列名

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语句使用的列，而不是必须将另一个参数传递给程序。

10 个解决方案

#1

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不允许这样做，因为他们会尝试在WHE子句中查找CASE子句中的所有列作为表的列。

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

#2

This:

这个：

SELECT col1 as a,
       CASE WHEN a = 'test' THEN 'yes' END as value 
  FROM table;

...will not work. 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.

...我不知道。

#3

@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

#4

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

#5

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

#6

Not in MySQL. I tried it and I get the following error:

不在MySQL中。我试过了，我收到以下错误：

ERROR 1054 (42S22): Unknown column 'a' in 'field list'

#7

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 ...那么col1就可以了

#8

If you write only equal condition just: Select Case columns1 When 0 then 'Value1' when 1 then 'Value2' else 'Unknown' End

如果你只写了相同的条件：选择Case columns1当0为0时'Value1'然后'Value2'否则'Unknown'结束
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

如果你想写更大，小于或等于你必须这样做：选择案例当[ColumnsName]> 0然后'value1'当[ColumnsName] = 0或[ColumnsName] <0然后'value2'Else'Unkownvalue'结束时

From tablename

来自tablename

Thanks Mr.Buntha Khin

谢谢Mr.Buntha Khin

#9

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

#10

-4

make it so easy.

让它变得如此简单。

select columnnameshow = (CASE tipoventa
when 'CONTADO' then 'contadito'
when 'CREDITO' then 'cred'
else 'no result'
end) from Promocion.Promocion

#1