MySQL：使用另一个表中的值作为列别名？ [英] MySQL: use value from another table as column alias?

问题描述

我们有一个非常有趣的表，我正试图从中得到一个描述性的结果。

该表存储了紧密相关但完全不同的数据类型集合，并且有意义的列名称存储在单独的表中。我认为显示起来比解释要容易。

We have this one table that is really funky and I'm trying to get a descriptive result set out of it.
This table stores a tightly-related yet disparate collection of data types, and the meaningful column "names" are stored in a separate table. I think it'll be easier to show than to explain.

一个简化和抽象的示例：

 CREATE TABLE IF NOT EXISTS `something_obscure` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `obscure_type_id` tinyint(2) NOT NULL,
      `segment1` varchar(92) DEFAULT NULL,
      `segment2` varchar(92) DEFAULT NULL,
      `segment3` varchar(92) DEFAULT NULL,
      `datetime_created` datetime DEFAULT NULL,
      PRIMARY KEY (`id`)
    );
    INSERT INTO `something_obscure` 
      (`id`, `obscure_type_id`, `segment1`, `segment2`, `segment3`, `datetime_created`) 
    VALUES
      ('250', 1, '123', '456', '789', '2013-05-14 10:13:44'),
      ('251', 2, 'abc', 'def', 'ghk', NULL),
      ('252', 2, NULL, NULL, 'mnop', NULL);


 CREATE TABLE IF NOT EXISTS `obscure_type` (
      `id` tinyint(2) unsigned NOT NULL AUTO_INCREMENT,
      `type` varchar(30) NOT NULL,
      `description` varchar(92) DEFAULT NULL,
      `segment1` varchar(92) DEFAULT NULL COMMENT 'a LABEL/NAME for the data found in Segment1 of the Obscure table',
      `segment2` varchar(92) DEFAULT NULL,
      `segment3` varchar(92) DEFAULT NULL,
      `datetime_created` datetime DEFAULT NULL,
      PRIMARY KEY (`id`)
    );
    INSERT INTO `obscure_type` 
      (`id`, `type`, `description`, `segment1`, `segment2`, `segment3`) 
    VALUES
      (1, 'native', 'a descriptive description', 'best', 'worst', 'other'),
      (2, 'alien', 'another descriptive desc', 'in', 'out', 'stationary');

最初，我期望将有意义的名称添加到业务逻辑（在php中），但是如果我可以让SQL在程序甚至看到这个结果集之前就处理它，那将是 很棒！

Initially, I was expecting to have put meaningful names to the columns in the business logic (in php), but if I could just get SQL to handle that before our program even sees this result set, that'd be awesome!

目标：

只是为了说明（我知道这实际上是行不通的），在想类似的东西：

GOAL:
Just to illustrate (I know this won't actually work), I was thinking something kinda like:

SELECT 
  data.segment1 AS type.segment1,
  data.segment2 AS type.segment2,
  data.segment3 AS type.segment3
FROM something_obscure AS data
JOIN pobscure_type AS type
ON data.obscure_type_id = type.id
WHERE data.obscure_type_id = 2

这将返回：

----------------------------
| in   | out  | stationary |
----------------------------
| abc  | def  | ghk        |
| NULL | NULL | mnop       |
----------------------------

摘要：

如何将别名设置为 值 >另一个表？

我们不想指定别名，我们希望别名由引用字段的值设置。

SUMMARY:
How would one go about setting aliases to the values of another table?
We do not want to specify the alias name, we want the alias name set by the value of the referenced field. Is that possible?

推荐答案

您是否尝试将别名包装到backqoutes中？

Did you try to wrap aliases into backqoutes?

SELECT 
  data.segment1 AS `type.segment1`,
  data.segment2 AS `type.segment2`,
  data.segment3 AS `type.segment3`
FROM something_obscure AS data
JOIN obscure_type AS type
ON data.obscure_type_id = type.id

这篇关于MySQL：使用另一个表中的值作为列别名？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！