SQL 如何为我的数据创建交叉引用表/查询？

How do I create a cross reference table/query for my data?

提问by Dillie-O

I have two simple tables in my database. A "card" table that contains Id, Name, and text of a card, and a "rulings" table which contains the Id of the card, and text detailing the rulings for the card.

我的数据库中有两个简单的表。一个包含卡片 ID、名称和文本的“卡片”表，以及包含卡片 ID 和详细说明卡片规则的文本的“规则”表。

Often enough in the ruling text, there is a reference to another card in the database. It is easy enough to find this in the text because each card is encapsulated within quotes in the text. It is not uncommon to have multiple cards referenced within the text of the ruling.

通常在裁决文本中，有对数据库中另一张牌的引用。在文本中很容易找到这一点，因为每张卡片都封装在文本中的引号中。在裁决文本中引用多张牌的情况并不少见。

What I'd like to do is be able to create a cross reference table (or procedure if it is efficient enough) so that when I submit a query for a card, I can find all the ruling records that directly reference the card through the Id and get all of the ruling records where the card name is referenced in the text.

我想要做的是能够创建一个交叉引用表（或程序，如果它足够有效），以便当我提交卡片查询时，我可以通过识别并获取文本中引用了卡片名称的所有裁决记录。

What would be the best way to approach this? My environment is SQL 2005, but any kind of "DB agnostic" solutions are greatly accepted here.

解决这个问题的最佳方法是什么？我的环境是 SQL 2005，但是这里非常接受任何类型的“数据库不可知”解决方案。

回答by Tom H

This seems like a fairly simple and common relational problem that is solved by a cross-reference table. For example:

这似乎是一个相当简单和常见的关系问题，可以通过交叉引用表来解决。例如：

CREATE TABLE dbo.Cards (
    id        INT            NOT NULL,
    name      VARCHAR(50)    NOT NULL,
    card_text VARCHAR(4000)  NOT NULL,
    CONSTRAINT PK_Cards PRIMARY KEY CLUSTERED (id)
)
GO
CREATE TABLE dbo.Card_Rulings (
    card_id        INT            NOT NULL,
    ruling_number  INT            NOT NULL,
    ruling_text    VARCHAR(4000)  NOT NULL,
    CONSTRAINT PK_Card_Rulings PRIMARY KEY CLUSTERED (card_id, ruling_number)
)
GO
CREATE TABLE dbo.Card_Ruling_Referenced_Cards (
    parent_card_id    INT    NOT NULL,
    ruling_number     INT    NOT NULL,
    child_card_id     INT    NOT NULL,
    CONSTRAINT PK_Card_Ruling_Referenced_Cards PRIMARY KEY CLUSTERED (parent_card_id, ruling_number, child_card_id)
)
GO
ALTER TABLE dbo.Card_Rulings
ADD CONSTRAINT FK_CardRulings_Cards FOREIGN KEY (card_id) REFERENCES dbo.Cards(id)
GO
ALTER TABLE dbo.Card_Ruling_Referenced_Cards
ADD CONSTRAINT FK_CardRulingReferencedCards_CardRulings FOREIGN KEY (parent_card_id, ruling_number) REFERENCES dbo.Card_Rulings (card_id, ruling_number)
GO
ALTER TABLE dbo.Card_Ruling_Referenced_Cards
ADD CONSTRAINT FK_CardRulingReferencedCards_Cards FOREIGN KEY (child_card_id) REFERENCES dbo.Cards(id)
GO

To get all card rulings for a card:

获取一张卡片的所有卡片规则：

SELECT *
FROM dbo.Cards C
INNER JOIN dbo.Card_Rulings CR ON CR.card_id = C.id
WHERE C.id = @card_id

To get all cards referenced in a ruling by a given card:

要获取给定卡片的裁决中引用的所有卡片：

SELECT C.*
FROM dbo.Card_Rulings CR
INNER JOIN dbo.Card_Ruling_Referenced_Cards CRRC ON CRRC.parent_card_id = CR.card_id
INNER JOIN dbo.Cards C ON C.id = CRRC.child_card_id
WHERE CR.card_id = @card_id

This was all off the top of my head and is not tested, so there might be syntactic errors, etc.

这完全是我的想法并且没有经过测试，因此可能存在语法错误等。

Your front end would be responsible for maintaining the references. This is probably desirable since it avoids the issue of someone forgetting to put quotes around a card name in a ruling text, etc.

您的前端将负责维护引用。这可能是可取的，因为它避免了有人忘记在裁决文本中的卡片名称周围加上引号等的问题。

回答by George Mastros

I would recommend that you create another table that stores your references. Then, create an insert and update trigger that maintains this table. This way, you would have a faster query to return the data you are looking for.

我建议您创建另一个表来存储您的引用。然后，创建维护此表的插入和更新触发器。这样，您将有一个更快的查询来返回您正在寻找的数据。

I recognize that initially populating this table might be a little difficult, which is why I am showing some sample data (and query) below, that you can use to get you started.

我认识到最初填充此表可能有点困难，这就是为什么我在下面显示一些示例数据（和查询），您可以使用它们来开始。

Declare @Card Table(Id Int, Name VarChar(20), CardText VarChar(8000))

Declare @Ruling Table(CardId Int, CardRuling VarChar(8000))

Insert Into @Card Values(1, 'Card 1', 'This is the card ID = 1')
Insert Into @Card Values(2, 'Card 2', 'This is the card id = 2.')
Insert Into @Card Values(3, 'Card 3', 'This is the card id = 3.')

Insert Into @Ruling Values(1, 'This is the ruling for 1 which references "2"')
Insert Into @Ruling Values(2, 'This is the ruling for 2 which references nothing')
Insert Into @Ruling Values(3, 'This is the ruling for 3 which references "1" and "2"')

Declare @CardId Int
Set @CardId = 1

Select  * 
From    @Card As Card
        Inner Join @Ruling As Ruling
            On Card.Id = Ruling.CardId
        Left Join @Card As CardReferences
            On Ruling.CardRuling Like '%"' + Convert(VarChar(10), CardReferences.Id) + '"%'

EDIT:

编辑：

The reason I suggested another table is because you will likely be disappointed with the performance of this query, especially for large tables.

我推荐另一个表的原因是因为您可能会对这个查询的性能感到失望，尤其是对于大表。