如何在MySQL的SELECT期间使用CONCAT作为列名? [英] How can I use CONCAT during SELECT in MySQL as a column name?

问题描述

谁能告诉我在SELECT期间如何使用CONCAT的结果作为列名?我的尝试看起来像这样:

Can anyone tell me how I can use a result from a CONCAT as a column name during SELECT? My attempt looks like this:

INSERT INTO `table_1` (datum, comment)
    SELECT CURRENT_DATE(), CONCAT('s',DAYOFWEEK(CURRENT_DATE())-1)
    FROM `table_2` 
    WHERE id = 12345

结果，我在注释列中得到的值是s0-s6，而不是我想从:/

As a result I get s0 - s6 as a value in my comment column instead of the value that is actually in my 2nd table that I want to read the value from :/

事实:如果我只是键入s0(例如，可以全部使用7)而不是CONCAT，它就可以正常工作，并且我得到了想要的实际值.

funfact: If I just type in s0 (as an example, works with all 7) instead of the CONCAT, it works just fine and I get the actual value that I want to.

感谢您的帮助.

推荐答案

好吧，我必须承认我花了一些时间才能理解您的要求. Table2有7列s0到s6，您想从与日期匹配的列中获取值.是吗?

Well, I must admit it took me a while to understand what you are asking. Table2 has 7 columns s0 to s6 and you want to get the value from the column matching the date. Yes?

因此，当然可以使用

SELECT CURRENT_DATE(), s2

为您提供s2的内容，而

gives you the content of s2, whereas

SELECT CURRENT_DATE(), CONCAT('s',DAYOFWEEK(CURRENT_DATE())-1)

给您's2'.如果没有的话，那将是可怕的.您是否真的希望DBMS计算一个值，然后检查该值是否恰好与列名匹配?然后

gives you 's2'. It would be horrible if not. Are you really expecting the DBMS to calculate a value and then check whether that value happens to match a column name? Then

select name, job from person;

在大多数情况下，将选择此人的姓名和职务，但对于名为Job的人，您将获得两次该职务.您看到这是不可取的，对吧?

would select the person's name and job in most cases but for the person named Job you would get the job twice instead. You see that this can not be desired, right?

因此，请检查您的表达式结果，然后从相应的列中读取:

So check your expression result instead and read from the according column:

insert into table_1 (datum, comment)
select 
  current_date(), 
  case dayofweek(current_date()) - 1 
    when 0 then s0
    when 1 then s1
    when 2 then s2
    when 3 then s3
    when 4 then s4
    when 5 then s5
    when 6 then s6
  end
from table_2 where id = 12345;

这篇关于如何在MySQL的SELECT期间使用CONCAT作为列名?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！