将同一表的两个INNER JOINS的结果合并到SQL Server中的一个单个临时表中 [英] Merging the result of two INNER JOINS of the same table into one single temporary table in SQL Server

问题描述

我正在尝试从文档中获取所有键值对，您可以自己使用键值对列表进行搜索.

I am trying to get all key-value pairs from a document which you can search for with a list of key-value pairs yourself.

为此，我为每个键值对创建一个临时表和一个SELECT语句，如下所示:

For this I create a temporary table and a SELECT statement for each key-value pair provided like this:

DECLARE @DocMetaTemp TABLE
(
    DocumentsID int,
    DataKey varchar(255),
    DataValue varchar(255) 
);

INSERT INTO @DocMetaTemp
    SELECT docMeta01.DocumentsID, docMeta01.DataKey, docMeta01.DataValue
    FROM psp_doc.Documents as docs
    INNER JOIN psp_doc.DocumentMetadata as docMeta01 ON docMeta01.DocumentsID = docs.ID 
                                                     AND docMeta01.DataKey = 'testkey1' 
                                                     AND docMeta01.DataValue = 'testval1';

INSERT INTO @DocMetaTemp
    SELECT docMeta02.DocumentsID, docMeta02.DataKey, docMeta02.DataValue
    FROM psp_doc.Documents as docs
    INNER JOIN psp_doc.DocumentMetadata as docMeta02 ON docMeta02.DocumentsID = docs.ID 
                                                     AND docMeta02.DataKey = 'testkey2'  
                                                     AND docMeta02.DataValue = 'testval1';

select * from @DocMetaTemp

会导致

但是这对我来说效率很低，并且也不会返回分配给返回的DocumentsID的所有键值对的列表.

but this looks very inefficent to me and also does not return the list of all key-value pairs that is assigned to the returned DocumentsIDs.

我试图通过在第一个语句之后添加第二个INNER JOIN语句来合并查询，但这会导致错误，因为它似乎没有使用列名进行合并.

I tried to merge the query by adding the second INNER JOIN statement after the first one, but this results in an error as it does not seem to merge using the column names.

DECLARE @DocMetaTemp TABLE
(
    DocumentsID int,
    DataKey varchar(255),
    DataValue varchar(255) 
);

INSERT INTO @DocMetaTemp
SELECT  docMeta01.DocumentsID, docMeta01.DataKey, docMeta01.DataValue, docMeta02.DocumentsID, docMeta02.DataKey, docMeta02.DataValue
FROM psp_doc.Documents as docs
INNER JOIN psp_doc.DocumentMetadata as docMeta01 ON docMeta01.DocumentsID = docs.ID and docMeta01.DataKey = 'testkey1' and docMeta01.DataValue = 'testval1'
INNER JOIN psp_doc.DocumentMetadata as docMeta02 ON docMeta02.DocumentsID = docs.ID and docMeta02.DataKey = 'testkey2' and docMeta02.DataValue = 'testval1';

select * from @DocMetaTemp

产生

列名或提供的值数与表不匹配 定义.

Column name or number of supplied values does not match table definition.

如何在单个语句中执行此操作?以及如何根据返回的ID获取所有键值对?

How can I do this in a single statement? And how do I get all key-value pairs based on the IDs returned?

推荐答案

对我来说还不太清楚，但是听起来您希望能够使用任意数量的键-值对来做到这一点.在这种情况下，用键值填充临时表并将其联接起来可能会更容易.像这样:

It's a little unclear to me, but it sounds like you want to be able to do this with an arbitrary number of key-Value pairs. In that case, it would probably be easier to populate a temp table with the key-values and join onto that. Something like:

DECLARE @KeyValyePairs TABLE ([Key] VARCHAR(255), [Value] VARCHAR(255));

INSERT INTO @KeyValuePairs (Key, Value)
VALUES
('testkey1', 'testval1'),
('testkey2', 'testval1') -- Or two or whatever
--(....., ....),.....

SELECT m.DocumentsID, m.DataKey, m.DataValue
FROM
    psp_doc.DocumentMetadata as m INNER JOIN
    @KeyValuePairs as kvp ON m.DataKey = kvp.Key AND m.DataValue = kvp.Value

如果需要的话，可以返回此值，也可以像以前一样将其放在临时表中，或者在WITH语句中使用，等等.

You could either return this if that's what you wanted or put it in a temp table as before or use in a WITH statement, etc.

如果您想从列表中获取所有文档的信息(听起来也像从问题中听到的那样，则可以执行以下操作:

If you wanted to get the info for all documents from the list (which it also kind of sounded like from the question, you would do the following:

SELECT dm.DocumentID, dm.DataKey, dm.DataValue
FROM psp_doc.DocumentMetadata as dm
WHERE m.DocumentsID IN
(
    SELECT m.DocumentsID
    FROM
        psp_doc.DocumentMetadata as m INNER JOIN
        @KeyValuePairs as kvp ON m.DataKey = kvp.Key AND m.DataValue = kvp.Value
);

-编辑

从下面在使用JOIN和UNION的位置看到答案: 您可以跳过UNION并将两个条件都放入JOIN.我把它放在评论中，但它不是很可读. 注意:在您的示例中，您可以使用WHERE来执行此操作，因为您没有使用documents表.这将完全跳过连接，这将节省一些代码，并且可能(我认为可能会对其进行优化)更有效地运行.这是带有要选择到表中的联接的代码.我使用了子查询，因为我认为它们更易于阅读.

From seeing your answer below where you use the JOIN and the UNION: You can skip the UNION and put both conditions in the JOIN. I put it in a comment, but it wasn't very readable. Note: in your example you could do this with a WHERE as you aren't using the documents table. This would skip the join entirely, which would save some code and probably (I think, it might be optimized away) run more efficiently. Here is the code with the join to select into your table. I've used a subquery as I think they are easier to read.

SELECT meta.DocumentsID, meta.DataKey, meta.DataValue
FROM psp_doc.DocumentMetadata meta
WHERE meta.DocumentsID IN
(
    SELECT m.DocumentsID
    FROM psp_doc.DocumentMetadata m
    WHERE
        (m.DataKey = 'testkey1' AND m.DataValue = 'testvalue1') OR
        (m.DataKey = 'testkey2' AND m.DataValue = 'testvalue2')
)

这篇关于将同一表的两个INNER JOINS的结果合并到SQL Server中的一个单个临时表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！