在msyql查询中为所有字段名称动态分配别名 [英] Dynamically assign alias to all the field names in msyql query

问题描述

我在mysql DB中有2个表。两个表都具有很少的固定和少量的动态列（Fields / Schema）。我想加入这两个表与以下查询：

  SELECT * 
 FROM`cd` cd 
 LEFT JOIN cd_n cn ON cd.id = cn.fk_cd 
  

我想结果为

  CD_Column1 CD_Column1 CD_Column3 ... CN_Column1 CN_Column2 CN_Column3 ..... 
值值...价值值... 
值值值...价值值... 
  

其中.....是两个表的动态列名。
因此，我不知道列名，因为它们是动态的，我想在查询级别重命名（别名）。请让我知道我该怎么做？

解决方案

您需要查询 information_schema 来获取这两个表的列名。假设您将在数组 $ cd_columns 和 cd_n中存储的 cd 列名称数组中的列名称 $ cdn_columns 。

然后在PHP中创建查询循环遍历列数组，并执行如下操作：

  $ sql ='SELECT'; 
 
 //添加cd列
 $ i = 0; 
 foreach（$ cd_columns as $ col）{
 $ sql。={$ col} AS CD_Column {$ i}; 
 $ i ++; 
} 
 
 //添加cd_n列
 $ i = 0; 
 foreach（$ cdn_columns as $ col）{
 $ sql。={$ col} AS CN_Column {$ i}，; 
 $ i ++; 
} 
 
 //删除尾部逗号
 $ sql = trim（$ sql，'，'）; 
 //继续SQL 
 $ sql。='FROM ...'; 
  

这对您有帮助吗？

I have 2 tables in mysll DB. Both tables have few fixed and few dynamic columns (Fields / Schema). I want to join both these tables with following query:

SELECT *
FROM `cd` cd
LEFT JOIN cd_n cn ON cd.id = cn.fk_cd

And I want to result as

CD_Column1   CD_Column1   CD_Column3   ...... CN_Column1   CN_Column2   CN_Column3  .....
value        value        value        ...... value        value        value       ...       
value        value        value        ...... value        value        value       ...       

Where ..... is dynamic column names of both the tables. So the case is I dont know the column names because they are dynamic and I want rename (alias) it on query level. Please let me know how can I do this?

解决方案

You would need to query the information_schema to get the column names of that two tables. Lets assume You would have the cd column names stored in the array $cd_columns and the cd_n column names in the array $cdn_columns.

Then in PHP when creating the query loop through the column arrays and do something like this:

$sql = 'SELECT ';

// add the cd columns
$i = 0;
foreach($cd_columns as $col) {
    $sql .= "{$col} AS CD_Column{$i},";
    $i++;
}

// add the cd_n columns
$i = 0;
foreach($cdn_columns as $col) {
    $sql .= "{$col} AS CN_Column{$i},";
    $i++;
}

// remove the trailing comma
$sql = trim($sql, ',');
// continue the SQL
$sql .= ' FROM ...';

Was this helpful?

这篇关于在msyql查询中为所有字段名称动态分配别名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！