具有12列组合PK的SQL联接表本身是无痛的

Question

我有一个包含12列(c1、c2、.、c12)的复合自然主键的表。

我想编写一个自联接查询：

SELECT *
FROM T1 AS a
JOIN T1 AS b ON a.c1 = b.c1 AND a.c2 = b.c2 AND ... AND a.c12 = b.c12;

这个连接是一个痛苦的写作。有更好的办法吗？

Answer 1

就个人而言，如果您必须有一个12列" key "，我将DROP该键，并创建一个连接所有值的计算列，然后在该列上创建键。这是过度简化的，但这应该给您提供要点：

CREATE TABLE dbo.YourTable (C1 int NOT NULL,
                            C2 int NOT NULL,
                            C3 int NOT NULL,
                            C4 int NOT NULL,
                            C5 int NOT NULL,
                            C6 int NOT NULL,
                            C7 int NOT NULL,
                            C8 int NOT NULL,
                            C9 int NOT NULL,
                            C10 int NOT NULL,
                            C11 int NOT NULL,
                            C12 int NOT NULL);
GO
--Create a unique index, instead of a PK
CREATE UNIQUE INDEX UQ_YourTable ON dbo.YourTable (C1,C2,C3,C4,C5,C6,C7,C8,C9,C10,C11,C12);
GO
--Add the new column
ALTER TABLE dbo.YourTable ADD K AS CONCAT(C1,C2,C3,C4,C5,C6,C7,C8,C9,C10,C11,C12);
--Set it as the PK 
ALTER TABLE dbo.YourTable ADD CONSTRAINT PK_YourTable PRIMARY KEY (K);

然后，您的JOIN变得非常简单，如：

SELECT *
FROM dbo.YourTable YT1
     JOIN dbo.YourTable YT2 ON YT1.K = YT2.K;

注意:这个假设所有的列都是NOT NULL__。如果它们有NULL值，这很可能无法正常工作。

Answer 2

你的问题没道理。为什么要在主键上使用自连接？您的代码等效于：

select t.*, t.*
from t1 t;

也就是说，行匹配是1-1.join是多余的，因为它正好匹配一行，即引用行(这是主键的工作方式)。

对我来说，有12列的“自然”键对我来说并不那么自然。我建议使用一个使用标识列的合成密钥：

CREATE TABLE t1 (
    t1_id INT IDENTITY(1, 1) PRIMARY KEY,
    c1 ? NOT NULL,
    -- rest of columns here
    UNIQUE (c1, c2, c3, . . . )
);

对于外键引用，请使用标识。别重复列！

CREATE TABLE t2 (
    t2_id INT IDENTITY(1, 1) PRIMARY KEY,
    t2_id INT REFERENCES t1(t1_id),
    -- columns specific to this table
);

如果您想要c1等的值，请使用JOIN来获取它们。

Answer 3

如果仍然希望在不更改底层表结构的情况下编写联接，则可以查询PK的列并生成SELECT的结果。如下所示：

设置示例：

CREATE TABLE T1 (
    C1 INT,
    C2 INT,
    C3 INT,
    C4 INT,
    C5 INT,
    PRIMARY KEY (C1, C2, C3, C4, C5))

查询：

DECLARE @tableName VARCHAR(100) = 'T1'
DECLARE @SQLTableAlias_1 VARCHAR(5) = 'a'
DECLARE @SQLTableAlias_2 VARCHAR(5) = 'b'

SELECT 
    TableName = KU.TABLE_NAME,
    PrimaryKeyColumn = KU.COLUMN_NAME,
    SQL = 
        @SQLTableAlias_1 + '.' + QUOTENAME(KU.COLUMN_NAME) + 
        ' = ' + 
        @SQLTableAlias_2 + '.' + QUOTENAME(KU.COLUMN_NAME) +
        CASE 
            WHEN COUNT(*) OVER () = ROW_NUMBER() OVER (ORDER BY KU.ORDINAL_POSITION) 
            THEN '' 
            ELSE ' AND ' 
        END
FROM 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU ON 
        TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND
        TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME
WHERE
    KU.table_name = @tableName
ORDER BY 
    KU.TABLE_NAME, 
    KU.ORDINAL_POSITION

结果：

TableName   PrimaryKeyColumn    SQL
T1          C1                  a.[C1] = b.[C1] AND 
T1          C2                  a.[C2] = b.[C2] AND 
T1          C3                  a.[C3] = b.[C3] AND 
T1          C4                  a.[C4] = b.[C4] AND 
T1          C5                  a.[C5] = b.[C5]

然后，只需复制/粘贴SQL列。