在 SQL Server 中，如何使用另一个表中的列名来比较行

发布于 2024-09-08 11:09:15 字数 558 浏览 7 评论 0原文

我有两个表 A 和 B，其中有动态列，除了另一个名为 C 的表之外，我不知道哪些列是其中的关键列。C

表指定哪一列是表 A 和 B 中的关键列。是 1 个或多个关键列。

我的问题是，如何生成这样一个查询，其中我选择 A 中的所有行，其中键列等于 B 中的相同键列？

我的一个想法是创建一个使用 sp_executesql 执行的文本查询，但我需要一些关于如何生成查询的好主意。

首先，我将从表 C 中为表 A 和 B 选择所有关键列到我声明的表 @keyColumns。

然后我将使用 while 循环来遍历 @keyColumns 内的所有关键列并生成查询并使用 sp_executesql 执行它。

例如：

UPDATE A 
SET ... 
FROM B INNER JOIN A 
ON A.keycol1 = B.keycol1 AND A.keycol2 = B.keycol2 AND ...

为了明确起见，C表只为B表指定了键列，从中我知道A表具有相同的键列。

但我想知道是否有更好的方法来解决这个问题。

原文

I have two tables A and B, with dynamic columns where I have no idea what columns are key inside them, except from another table called C.

The C table specifies which column/s is a key column in tables A and B. There can be 1 or more key columns.

My question is, how would I generate such a query where I select all rows from A where the key columns are equal to the same key columns in B?

One idea I had was to create a text query that I execute with sp_executesql, but I need some good ideas on how to generate the query.

First of all, I would select all key columns from table C for the table A and B to my declared table @keyColumns.

Then I would use a while loop to go through all key columns inside @keyColumns and generate the query and execute it with sp_executesql.

For example:

UPDATE A 
SET ... 
FROM B INNER JOIN A 
ON A.keycol1 = B.keycol1 AND A.keycol2 = B.keycol2 AND ...

Just to make it clear, the C table only specifies key columns for the table B, and from that I know A has the same key columns.

But I want to know if there's a better way to solve this.

分享到QQ

分享到微博

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

发布评论

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

乜一 2024-09-15 11:09:15

“C”中保存的键列是主键吗？如果是这样，您可以从 INFORMATION_SCHEMA.TABLE_CONSTRAINTS 和 INFORMATION_SCHEMA.KEY_COLUMN_USAGE 检索这些内容，如此处而不是使用不同的表。

我认为你必须为此使用动态 SQL。没有像 FROM B JOIN A ON PRIMARY KEYS 这样的语法。不过，您可以通过如下所示的 SELECT 连接查询，而不是使用 WHILE 循环。

DECLARE @DynSql nvarchar(max)
DECLARE @TableA sysname
DECLARE @TableB sysname

SET @TableA = 'A'
SET @TableB = 'B';

WITH C AS
(
SELECT 'B' AS [Table], 'keycol2' As col UNION ALL
SELECT 'B' AS [Table], 'keycol1' As col UNION ALL
SELECT 'X' AS [Table], 'keycol1' As col
)

SELECT @DynSql = ISNULL(@DynSql + ' AND ','')+ @TableA + '.'+QUOTENAME(col) + '= ' + @TableB + '.'+QUOTENAME(col)
FROM C WHERE [Table] = @TableB

IF @@ROWCOUNT=0
RAISERROR('No Entry found for table %s',16,1,@TableB)

SET @DynSql = 
'UPDATE ' + @TableA + ' 
SET ... 
FROM ' + @TableB + '  INNER JOIN ' + @TableA + ' ON  
' + @DynSql

PRINT @DynSql

Are the key columns held in 'C' the primary key? If so you can retrieve these from INFORMATION_SCHEMA.TABLE_CONSTRAINTS, and INFORMATION_SCHEMA.KEY_COLUMN_USAGE as described here rather than using a different table.

You have to use dynamic SQL for this I think. There is no syntax like FROM B JOIN A ON PRIMARY KEYS. Instead of the WHILE loop though you can just concatenate your query through a SELECT as below.

DECLARE @DynSql nvarchar(max)
DECLARE @TableA sysname
DECLARE @TableB sysname

SET @TableA = 'A'
SET @TableB = 'B';

WITH C AS
(
SELECT 'B' AS [Table], 'keycol2' As col UNION ALL
SELECT 'B' AS [Table], 'keycol1' As col UNION ALL
SELECT 'X' AS [Table], 'keycol1' As col
)

SELECT @DynSql = ISNULL(@DynSql + ' AND ','')+ @TableA + '.'+QUOTENAME(col) + '= ' + @TableB + '.'+QUOTENAME(col)
FROM C WHERE [Table] = @TableB

IF @@ROWCOUNT=0
RAISERROR('No Entry found for table %s',16,1,@TableB)

SET @DynSql = 
'UPDATE ' + @TableA + ' 
SET ... 
FROM ' + @TableB + '  INNER JOIN ' + @TableA + ' ON  
' + @DynSql

PRINT @DynSql

回复收藏 0 原文

~没有更多了~