SQL 查询 SELECT FROM [来自另一个表的列的值]

发布于 2024-11-01 14:33:29 字数 263 浏览 4 评论 0原文

我有一个表 X，当某些表发生更改时，触发器将在其中插入一行。我已将表名称插入到表 X 中。

现在，我想从表 X 中选择数据，同时与实际表本身进行内连接。是否可以使用 select 表的列中的值作为内连接的表？

查询应该看起来像这样

SELECT X.a, Y.b, Y.c FROM X
INNER JOIN [X.TableName] AS Y ON Y.ID = X.ID

原文

I have a table X where a trigger will insert a row when there's a changes to some tables. I've inserted the table name into table X.

Now, I would like to select the data from table X while inner join with the actual table itself. Is it possible by using a value from a column of the select table as the table for inner join?

The query should looks something like this

SELECT X.a, Y.b, Y.c FROM X
INNER JOIN [X.TableName] AS Y ON Y.ID = X.ID

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

许一世地老天荒 2024-11-08 14:33:29

执行

select 'SELECT X.a, Y.b, Y.c FROM X
INNER JOIN [' + X.TableName + '] AS Y ON X.ID = Y.ID 
where x.primarykey =' + x.primarykey from x

将输出一系列sql语句

SELECT X.a, Y.b, Y.c FROM X
INNER JOIN [ customer ] AS Y ON X.ID = Y.ID
where x.primarykey = 1234

，您可以根据需要执行“sql to build sql”。

Executing

select 'SELECT X.a, Y.b, Y.c FROM X
INNER JOIN [' + X.TableName + '] AS Y ON X.ID = Y.ID 
where x.primarykey =' + x.primarykey from x

Will output a series of sql statements like

SELECT X.a, Y.b, Y.c FROM X
INNER JOIN [ customer ] AS Y ON X.ID = Y.ID
where x.primarykey = 1234

that you can then execute "sql to build sql" if you will.

回复收藏 0 原文

匿名。 2024-11-08 14:33:29

不，那是不可能的。您不能在查询中直接使用值作为表名，也不能将每个记录与不同的表连接起来。

您必须对单个记录进行联接，并动态创建查询以使用值作为表名：

declare @name varchar(50)
set @name = select TableName from X where ID = 42
exec('select X.a, Y.b, Y.c from X innner join ' + @name + ' as Y on Y.DI = X.ID where X.ID = 42')

No, that is not possible. You can't use values as table names directly in a query, and you can't join each record against a different table.

You would have to make the join for a single record, and create the query dynamically to use a value as table name:

declare @name varchar(50)
set @name = select TableName from X where ID = 42
exec('select X.a, Y.b, Y.c from X innner join ' + @name + ' as Y on Y.DI = X.ID where X.ID = 42')

回复收藏 0 原文

眼泪都笑了 2024-11-08 14:33:29

使用动态查询：

DECLARE @table AS NVARCHAR(128);
DECLARE @sql NVARCHAR(4000);

-- of course you'll have to add your WHERE clause here 
SELECT @table = TableName FROM X;
SET @sql = 'SELECT X.a, Y.b, Y.c FROM X INNER JOIN '+@table+' AS Y ON Y.ID = X.ID';

EXEC(@sql);

With dynamic query:

DECLARE @table AS NVARCHAR(128);
DECLARE @sql NVARCHAR(4000);

-- of course you'll have to add your WHERE clause here 
SELECT @table = TableName FROM X;
SET @sql = 'SELECT X.a, Y.b, Y.c FROM X INNER JOIN '+@table+' AS Y ON Y.ID = X.ID';

EXEC(@sql);

回复收藏 0 原文

~没有更多了~