T-SQL如何匹配多行

There are two tables, package table and product table. In my case, the package contains multiple products. We need to recognize multiple products whether they can match a package which is already in package records. Some scripts are below.

有两个表,包表和产品表。就我而言,该软件包包含多个产品。我们需要识别多个产品是否可以匹配包装记录中的包装。下面是一些脚本。

DECLARE @tblPackage TABLE(
    PackageID   int,
    ProductID   int
)

INSERT INTO @tblPackage VALUES(436, 4313)
INSERT INTO @tblPackage VALUES(436, 4305)
INSERT INTO @tblPackage VALUES(436, 4986)

INSERT INTO @tblPackage VALUES(437, 4313)
INSERT INTO @tblPackage VALUES(437, 4305)

INSERT INTO @tblPackage VALUES(442, 4313)
INSERT INTO @tblPackage VALUES(442, 4335)

INSERT INTO @tblPackage VALUES(445, 4305)
INSERT INTO @tblPackage VALUES(445, 4335)

DECLARE @tblProduct TABLE(
    ProductID   int
)

INSERT INTO @tblProduct VALUES(4305) 
INSERT INTO @tblProduct VALUES(4313)

We have two product 4305 and 4313, then I need to retrieve the matched package record 437. Only the exactly matched one can be return, so package 436 is not the right one. It's not easy to make a multiple rows query clause. please someone can have any suggestions? Thanks.

我们有两个产品4305和4313,然后我需要检索匹配的包记录437.只有完全匹配的包可以返回,所以包436不是正确的。制作多行查询子句并不容易。请有人可以有任何建议吗?谢谢。

4 个解决方案

#1

Try this. SQLFIDDLE DEMO

尝试这个。 SQLFIDDLE DEMO

Declare @cnt Int
Select @cnt = count(distinct ProductID) from tblProduct 

SELECT B.packageid 
FROM   (SELECT packageid 
        FROM   tblpackage 
        GROUP  BY packageid 
        HAVING Count(productid) = @cnt) A 
       JOIN tblpackage B 
         ON a.packageid = b.packageid 
WHERE  EXISTS (SELECT 1  FROM tblproduct c WHERE c.productid = b.productid) 
GROUP  BY B.packageid 
HAVING Count(DISTINCT B.productid) = @cnt

#2

This is a "set-within-sets" query. I would approach it using aggregation and having:

这是一个“set-within-sets”查询。我会使用聚合来处理它并具有:

select p.PackageID
from @tblPackage p left join
     @tblProduct pr
     on p.ProductId = pr.ProductId
group by p.PackageId
having count(*) = count(pr.ProductId) and
       count(*) = (select count(*) from @tblProduct);

The left join keeps all products for each package. The first condition in the having clause says that all these products match what is in the product table. The second says that all the products are actually there.

左连接保留每个包的所有产品。 having子句中的第一个条件是所有这些产品都与产品表中的产品相匹配。第二个说所有的产品都在那里。

Note that if you have duplicates in either table, then you'll need to use count(distinct) in one or more places. Your sample data suggests that this is not an issue.

请注意,如果您在任一表中都有重复项,那么您需要在一个或多个位置使用count(distinct)。您的示例数据表明这不是问题。

#3

How about this:

这个怎么样:

;WITH MatchingRows
AS
(
    SELECT P1.PackageID, P1.ProductID 
    FROM @tblPackage P1
    INNER JOIN @tblProduct P2
        ON P1.ProductID = P2.ProductID
)
SELECT DISTINCT P1.PackageID
FROM @tblPackage P1
INNER JOIN @tblProduct P2
    ON P1.ProductID = P2.ProductID
WHERE (SELECT COUNT(*) FROM @tblPackage P3 WHERE P3.PackageID = P1.PackageID) /* How may products are in this package */
    = (SELECT COUNT(*) FROM MatchingRows WHERE MatchingRows.PackageID = P1.PackageID) /* how many matching rows in this package - are they the same*/

#4

This query will be helpful..

此查询将有所帮助..

DECLARE @tblPackage TABLE(
    PackageID   int,
    ProductID   int
)

INSERT INTO @tblPackage VALUES(436, 4313)
INSERT INTO @tblPackage VALUES(436, 4305)
INSERT INTO @tblPackage VALUES(436, 4986)

INSERT INTO @tblPackage VALUES(437, 4313)
INSERT INTO @tblPackage VALUES(437, 4305)

INSERT INTO @tblPackage VALUES(442, 4313)
INSERT INTO @tblPackage VALUES(442, 4335)

INSERT INTO @tblPackage VALUES(445, 4305)
INSERT INTO @tblPackage VALUES(445, 4335)

DECLARE @tblProduct TABLE(
    ProductID   int
)

INSERT INTO @tblProduct VALUES(4305) 
INSERT INTO @tblProduct VALUES(4313)

SELECT A.PackageID FROM
    (SELECT PackageID,
           CombinedProductID = 
            (
                SELECT
                    CAST(t2.ProductID AS VARCHAR(100))
                FROM @tblPackage t2
                WHERE
                    t2.PackageID = t1.PackageID
                GROUP BY t2.ProductID
                FOR XML PATH(''), TYPE).value('.', 'varchar(max)'
            )
    FROM @tblPackage t1
    GROUP BY t1.PackageID) AS A
INNER JOIN     
    (SELECT (SELECT ProductID 
         FROM @tblProduct 
         ORDER BY ProductID  
         FOR XML PATH (''), type).value('.', 'varchar(max)') 
     AS CombinedProductID) AS B

ON A.CombinedProductID = B.CombinedProductID