SQL - 如何从另一个表中选择列别名? [英] SQL - How to select a column alias from another table?

问题描述

我有一个包含数据的 SQL 表和另一个包含该列别名的表.它用于翻译目的.

I have a table in SQL with data and another table that holds the alias for that column. It is used for translation purposes.

我想知道如何对这些列进行选择但从另一个表中检索别名?

I was wondering how can I do a select on those columns but retrieve the alias from another table?

这是保存真实列名的表:

This is the table that holds the real column names:

ID      PageID  ColName         Order   Type    Width   IsDeleted
1   7   CustType    2   NULL    NULL    0
2   7   Description 3   NULL    NULL    0
3   7   ApplyVAT    4   NULL    NULL    0
4   7   ProduceInvoices 5   NULL    NULL    0
5   7   PurchaseSale    6   NULL    NULL    0
6   7   TermsDays   7   NULL    NULL    0
7   7   DateTimeLastUpdated 8   NULL    NULL    0

这是保存别名(文本)的表:

This is the table that holds the alias (text):

ID      ColID   UserID  Text            Order   Enabled?
50  22  1   id          1       1
51  1   1   CustTypes   2   1
52  2   1   Description 3   1
53  3   1   ApplyVAT    NULL    0
54  4   1   ProduceInvoices NULL    0
55  5   1   PurchaseSale    NULL    0
56  6   1   TermsDays   NULL    0
57  7   1   DateTimeLastUpdated NULL    0

推荐答案

我相信你将需要使用动态 sql 来做到这一点，例如:

I believe you will need to use dynamic sql to do this, e.g.:

DECLARE @Sql NVARCHAR(MAX);
SELECT TOP 1 @Sql = 'SELECT dt.ID as ' + at.IDAlias + ', dt.Town as ' + at.TownAlias 
                  + ' FROM DataTable dt'
FROM AliasTable at
WHERE at.LanguageID = 2;
EXEC(@Sql)

以数据表为例

CREATE TABLE DataTable
(
   ID INT,
   Town NVARCHAR(50)
);

还有一个包含语言的表格 - 上述列的依赖别名:

And a table holding language - dependent aliases for the columns in the above:

CREATE TABLE AliasTable
(
   LanguageId INT,
   IDAlias NVARCHAR(100),
   TownAlias NVARCHAR(100)
);

这里是SqlFiddle

动态 Sql 的(许多)警告之一是您需要确保别名数据针对 Sql Injectin 攻击进行验证.

One of the (many) caveats with dynamic Sql is you will need to ensure that the alias data is validated against Sql Injectin attacks.

这篇关于SQL - 如何从另一个表中选择列别名?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！