mysql - CONCATS 用于列名、列别名

Question

我正在尝试对一个月内的每一天运行查询。该月第一天的查询部分如下所示，并且有效:

CASE 
WHEN CONCAT(
    Shifts.d1a,
    Shifts.d1b, 
    Shifts.d1c, 
    Shifts.d1d, 
    Shifts.d1e, 
    Shifts.d1f) = "" THEN "-"
ELSE
  CONCAT (
    IF (Shifts.d1a='Yes','A',''),
    IF (Shifts.d1b='Yes','B',''),
    IF (Shifts.d1c='Yes','C',''),
    IF (Shifts.d1d='Yes','D',''),
    IF (Shifts.d1e='Yes','E',''),
    IF (Shifts.d1f='Yes','F','')
    ) END AS d1,

该月的第二天看起来是一样的，只是表引用的所有“d1”部分都将是“d2”，并且“END AS d1”将是“END AS d2”。

我继承了表结构；每个 d[n][x] 的值均为“Yes”或“”。不涉及空值。目标是在一天内获取单独字段的项目 - 即 d[n]a、d[n]b、d[n]c 等，并将它们连接成(例如)“ABF”，对于每个具有 yes 的字段值，如果所有字段均为空，则在 d[n] 列中生成一个连字符。

就像我说的，如果我运行 31 次几乎相同的代码，这就会起作用。我觉得这既丑陋又不必要。但我遇到了在 SELECT 中使用连接字符串作为列名称以及在输出中使用变量作为列别名的一部分的明显障碍。所以这不起作用:

BEGIN
  DECLARE vcounter INT DEFAULT 0;
  DECLARE vcolumn VARCHAR (10);
  DECLARE vcolumna VARCHAR (64);
  DECLARE vcolumnb VARCHAR (64);
  DECLARE vcolumnc VARCHAR (64);
  DECLARE vcolumnd VARCHAR (64);
  DECLARE vcolumne VARCHAR (64);
  DECLARE vcolumnf VARCHAR (64);
shift_loop: REPEAT
SET vcounter = vcounter + 1;
SET vcolumn = CONCAT ('d',vcounter);
SET vcolumna = CONCAT ('Shift.',vcolumn,'a');
SET vcolumnb = CONCAT ('Shift.',vcolumn,'b');
SET vcolumnc = CONCAT ('Shift.',vcolumn,'c');
SET vcolumnd = CONCAT ('Shift.',vcolumn,'d');
SET vcolumne = CONCAT ('Shift.',vcolumn,'e');
SET vcolumnf = CONCAT ('Shift.',vcolumn,'f');
SELECT 
  CASE 
   WHEN CONCAT(
        vcolumna,
        vcolumnb,
        vcolumnc,
        vcolumnd,
        vcolumne,
        vcolumnf) = '' THEN '-'
    ELSE 
        CONCAT(
        vcolumna,
        vcolumnb,
        vcolumnc,
        vcolumnd,
        vcolumne,
        vcolumnf
        ) 
        END
          AS vcolumn;
UNTIL vcounter = 1
END REPEAT;
END

是的，我知道过程中的 CASE 语句是不必要的——我可以将最终 CONCAT 的结果分配给一个变量并针对空值对其进行测试。我只是保持原样，以便更好地了解我想要做什么。

结果是，对于每一行，我都会得到一个名为“vcounter”的列，其内容为“Shift.d1aShift.d1bShift.d1c”等。有人有什么建议吗？

编辑添加

好的，谢谢，我查看了建议的答案，但我不知道如何使其与我需要的 WHILE 语句一起使用。这是我将代码更改为:

 BEGIN  
 SET @vcounter = 0;  
 SET @vcounter = @vcounter + 1;  
 SET @vtable = 'Table_X';  
 SET @vcolumn = CONCAT ('d',@vcounter);  
 SET @vcolumna = CONCAT (@vtable,'.',@vcolumn,'a');  
 SET @vcolumnb = CONCAT (@vtable,'.',@vcolumn,'b');  
 SET @vcolumnc = CONCAT (@vtable,'.',@vcolumn,'c');  
 SET @vcolumnd = CONCAT (@vtable,'.',@vcolumn,'d');  
 SET @vcolumne = CONCAT (@vtable,'.',@vcolumn,'e');   
 SET @vcolumnf = CONCAT (@vtable,'.',@vcolumn,'f');  
 SET @vshifts = CONCAT  
 (@vcolumna,',',@vcolumnb,',',@vcolumnc,',',@vcolumnd,',',@vcolumne,',',@vcolumnf);  
 SET @vquery = CONCAT ('SELECT CASE WHEN CONCAT (',  
                  @vshifts,  
                  ') ="" THEN "-" ELSE   
                  CONCAT (IF(',@vcolumna,' = "Yes","A",""),  
                          IF(',@vcolumnb,' = "Yes","B",""),  
                          IF(',@vcolumnc,' = "Yes","C",""),  
                          IF(',@vcolumnd,' = "Yes","D",""),  
                          IF(',@vcolumne,' = "Yes","E",""),  
                          IF(',@vcolumnf,' = "Yes","F","")  
                  )END AS ',@vcolumn,' FROM ',@vtable);  
 PREPARE stmt FROM @vquery;  
 EXECUTE stmt;  
 END  

这对于一天来说效果很好。问题是，在任何插入 WHILE 或 REPEAT 语句的地方，都会得到不好的结果。我发现的最接近的结果最终会产生如下所示的结果

 d1  
 D  
 F  
 A  
 B  

 d2  
 A  
 AB  
 C  
 -  

等等。我想要的是

 d1    d2  
 D     A
 F     AB
 A     C
 B     -

我显然不太了解我在这里做什么，所以我真的很感谢任何人可以提供的帮助。谢谢。

Answer 1

好的，我自己解决了。这是有效的:

 BEGIN
 SET @vcounter = 0;
 SET @vtable = 'tablename';
 SET @vquery = 'SELECT
  last_name As Last_Name,
  first_name As First_Name, ';
 shift_loop: REPEAT
 SET @vcounter = @vcounter + 1;
 SET @vcolumn = CONCAT ('d',@vcounter);
 SET @vcolumna = CONCAT (@vcolumn,'a');
 SET @vcolumnd = CONCAT (@vcolumn,'b');
 SET @vcolumnp = CONCAT (@vcolumn,'c');
 SET @vcolumne = CONCAT (@vcolumn,'d');
 SET @vcolumnt = CONCAT (@vcolumn,'e');
 SET @vcolumns = CONCAT (@vcolumn,'f');
 SET @vshifts = CONCAT (@vcolumna,',',@vcolumnb,',',@vcolumnc,',',@vcolumnd,',',@vcolumne,',',@vcolumnf);
 SET @vquery = CONCAT (@vquery,' CASE WHEN CONCAT (',
                       @vshifts,
                       ') ="" THEN "-" ELSE 
                       CONCAT (IF(',@vcolumna,' = "Yes","A",""),
                              IF(',@vcolumnb,' = "Yes","B",""),
                              IF(',@vcolumnc,' = "Yes","C",""),
                              IF(',@vcolumnd,' = "Yes","D",""),
                              IF(',@vcolumne,' = "Yes","E",""),
                                  IF(',@vcolumnf,' = "Yes","F","")
                       ) END AS ',@vcolumn,',');
 Until @vcounter = 31
 END REPEAT;
 SET @vquery = CONCAT (
     @vquery,'
     comment AS Comment,
     _submitted_ AS Submitted
     FROM ',@vtable);
 PREPARE stmt FROM @vquery;
 EXECUTE stmt;
 DEALLOCATE PREPARE stmt;
 END