mysql 选择大小写多列作为

mysql select case multiple columns as

提问by Ray

I want to set multiple fields with something like this, how can I make it work?

我想用这样的东西设置多个字段，我怎样才能让它工作？

Select TableId,
    CASE
        WHEN StartTime > Appt THEN
            Field AS Field_1
            FieldId AS FieldId_1
        WHEN StartTime BETWEEN Appt AND DATE_ADD(Appt, INTERVAL 1 MONTH) THEN
            Field AS Field_2
            FieldId AS FieldId_2
    END
FROM TableA;

It doesn't work, the syntax is wrong near AS Field_1.

它不起作用，AS Field_1 附近的语法错误。

回答by Jeremy Smyth

You're trying to perform something similar to macro expansion in a SQL statement, which you cannot do. The simplest way to solve your problem is to write the SQL dynamically in your application code, and execute that statement conditionally from the app.

您正在尝试在 SQL 语句中执行类似于宏扩展的操作，这是您无法做到的。解决问题的最简单方法是在应用程序代码中动态编写 SQL，然后从应用程序有条件地执行该语句。

If you must do it within SQL, you have a couple of options.

如果您必须在 SQL 中执行此操作，您有几个选择。

Each CASEis a single expression, so you can only produce output fields singly, by having a separate CASEfor each column:

每个CASE都是一个单独的表达式，因此您只能通过CASE为每一列单独生成输出字段：

SELECT ID,
  CASE WHEN StartTime > Appt THEN Field_A 
       WHEN StartTime BETWEEN Appt AND DATE_ADD(Appt, INTERVAL 1 MONTH) THEN
        Field_B
  END AS field1,
  CASE WHEN StartTime > Appt THEN Field_C 
       WHEN StartTime BETWEEN Appt AND DATE_ADD(Appt, INTERVAL 1 MONTH) THEN
        Field_D
  END AS field2
FROM TableA;

Alternatively, use the statement form of CASE(or IF, which might be simpler) within a stored procedure, to contain multiple versions of your statement and execute them conditionally.

或者，在存储过程中使用(or ，这可能更简单)的语句形式CASEIF来包含语句的多个版本并有条件地执行它们。

回答by Marc B

That is utterly broken casesyntax. If all you're doing is changing the aliases of those fields, then do that transformation in your client.

那是完全破坏的case语法。如果您所做的只是更改这些字段的别名，那么请在您的客户端中进行该转换。

e.g.

例如

SELECT (StartTime > Appt) AS case1,
       (StartTime BETWEEN Appt AND Appt + INTERVAL 1 MONTH) AS case2
       Field,
       FieldID
FROM TableA

if ($row['case1']) then
    $field_1 = $row['Field'];
    $fieldid_1 = $row['FieldID'];
} else if ($row['case2']) {
    etc..
}

回答by Hogan

You are close, it looks like this

你很近，看起来像这样

Select TableId,
    CASE WHEN StartTime > Appt THEN Field   ELSE NULL END AS Field_1,
    CASE WHEN StartTime > Appt THEN FieldId ELSE NULL END AS FieldId_1,
    CASE WHEN StartTime BETWEEN Appt AND DATE_ADD(Appt, INTERVAL 1 MONTH) THEN Field ELSE NULL END AS Field_2 ,
    CASE WHEN StartTime BETWEEN Appt AND DATE_ADD(Appt, INTERVAL 1 MONTH) THEN FieldId ELSE NULL END AS FieldId_2

FROM TableA;

回答by Anthony Kal

I dont know what is field_XXXXcontent,

我不知道是什么field_XXXX内容，

But i can make it work with reusing the switch case from the query like this one : (here i am using temporary variable)

但是我可以通过重用查询中的 switch case 来使它工作：（我在这里使用temporary variable）

SELECT TableId,
    CASE
        WHEN StartTime > Appt THEN
            @temp := 'Field_1' 
        WHEN StartTime BETWEEN Appt AND DATE_ADD(Appt, INTERVAL 1 MONTH) THEN
            @temp := 'Field_2' 
    END AS `Field`,
    CASE
        WHEN @temp := 'Field_1' THEN 'FieldId_1'
        WHEN @temp := 'Field_2' THEN 'FieldId_2'
    END AS `FieldId`
FROM TableA; 

maybe this what you mean.

也许这就是你的意思。

回答by vish

change the order of your field selection, because case should be first column to be retrieved with alias in mysql the code should be as follows

更改字段选择的顺序，因为 case 应该是在 mysql 中使用别名检索的第一列，代码应如下

Select 
    CASE
        WHEN StartTime > Appt THEN
            Field AS Field_1
            FieldId AS FieldId_1
        WHEN StartTime BETWEEN Appt AND DATE_ADD(Appt, INTERVAL 1 MONTH) THEN
            Field AS Field_2
            FieldId AS FieldId_2
    END as <alias>,
TableId
FROM TableA;