SQL Server，将一个对象ID与映射表中的其他对象ID匹配

Question

我完全被我用T-SQL编写的查询难住了。我有一个映射表，其中图书is存储在BookId列中，而AttributeId存储在另一列中。

CREATE TABLE BookMap (
BookId int not null,
AttributeId int not null
)

每本书可以有1到10个属性。如果图书1具有属性3-6，我希望找到也具有属性3-6的图书。由于某些原因，我想不出该如何编写这个查询。

有什么建议吗？

以下是编辑:为了进一步解释，我有以下数据：

INSERT INTO BookMap (BookId, AttributeId) VALUES (1, 3);
INSERT INTO BookMap (BookId, AttributeId) VALUES (1, 6);
INSERT INTO BookMap (BookId, AttributeId) VALUES (2, 3);
INSERT INTO BookMap (BookId, AttributeId) VALUES (2, 4);
INSERT INTO BookMap (BookId, AttributeId) VALUES (2, 6);
INSERT INTO BookMap (BookId, AttributeId) VALUES (5, 3);
INSERT INTO BookMap (BookId, AttributeId) VALUES (5, 6);
INSERT INTO BookMap (BookId, AttributeId) VALUES (6, 3);
INSERT INTO BookMap (BookId, AttributeId) VALUES (6, 5);

我想根据BookId =1进行查询，并返回恰好是3和6，但不是更多或更少的BookId。另一种方法是返回BookId列表和匹配百分比，按百分比降序排序。都不适合我的任务。

Answer 1

编辑:下面的是几个可以产生所需结果的查询。根据索引、统计信息等的不同，它们可能具有相当不同的性能。用真实数据检查执行计划应该是有启发性的。

-- Sample data.
declare @BookMap as Table ( BookId int not null, AttributeId int not null ) 
insert into @BookMap ( BookId, AttributeId ) values 
  (1, 3), (1, 6), 
  (2, 3), (2, 4), (2, 6), 
  (5, 3), (5, 6), 
  (6, 3), (6, 5)
select * from @BookMap 

-- Target book.
declare @BookId as Int = 1 
select AttributeId 
  from @BookMap 
  where BookId = @BookId 

-- Books with matching attributes using NOT EXISTS in the last line. 
select BookId 
  from ( 
    select BookId, Sum( 1 ) as MatchCount 
      from @BookMap as BM 
      where BookId <> @BookId and AttributeId in ( select AttributeId from @BookMap where BookId = @BookId ) 
      group by BookId ) as Ellen 
    where 
      -- The number of matching attributes is the number of desired attributes. 
      MatchCount = ( select Count( 42 ) from @BookMap where BookId = @BookId ) and 
      -- There are no other attributes as determined by looking for additional attributes. 
      not exists ( select 42 from @BookMap where BookId = Ellen.BookId and AttributeId not in ( select AttributeId from @BookMap where BookId = @BookId ) ) 

-- Books with matching attributes using COUNT() in the last line. 
select BookId 
  from ( 
    select BookId, Sum( 1 ) as MatchCount 
      from @BookMap as BM 
      where BookId <> @BookId and AttributeId in ( select AttributeId from @BookMap where BookId = @BookId ) 
      group by BookId ) as Ellen 
    where 
      -- The number of matching attributes is the number of desired attributes. 
      MatchCount = ( select Count( 42 ) from @BookMap where BookId = @BookId ) and 
      -- There are no other attributes as determined by counting attributes. 
      ( select Count( 42 ) from @BookMap where BookId = Ellen.BookId ) = ( select Count( 42 ) from @BookMap where BookId = @BookId ) 



-- Display the attributes that we must, and must not, match.
select distinct AttributeId,
  case when AttributeId in ( select AttributeId from @BookMap where BookId = @BookId ) then 1 else 0 end as MustMatch,
  case when AttributeId not in ( select AttributeId from @BookMap where BookId = @BookId ) then 1 else 0 end as MustNotMatch
  from @BookMap



-- Get the similar books using SUM() in the last line.
; with A as (
  -- All attributes with MustMatch/MustNotMatch flags.
  select distinct AttributeId,
    case when AttributeId in ( select AttributeId from @BookMap where BookId = @BookId ) then 1 else 0 end as MustMatch,
    case when AttributeId not in ( select AttributeId from @BookMap where BookId = @BookId ) then 1 else 0 end as MustNotMatch
    from @BookMap
  )
select BookId
  from @BookMap as B inner join
    A as A on A.AttributeId = B.AttributeId
  where BookId <> @BookId
  group by BookId
  having Sum( MustNotMatch ) = 0 and Sum( MustMatch ) = ( select Count( 42 ) from @BookMap where BookId = @BookId )

-- Get the similar books using MAX() in the last line.
; with A as (
  -- All attributes with MustMatch/MustNotMatch flags.
  select distinct AttributeId,
    case when AttributeId in ( select AttributeId from @BookMap where BookId = @BookId ) then 1 else 0 end as MustMatch,
    case when AttributeId not in ( select AttributeId from @BookMap where BookId = @BookId ) then 1 else 0 end as MustNotMatch
    from @BookMap
  )
select BookId
  from @BookMap as B inner join
    A as A on A.AttributeId = B.AttributeId
  where BookId <> @BookId
  group by BookId
  having Max( MustNotMatch ) = 0 and Sum( MustMatch ) = ( select Count( 42 ) from @BookMap where BookId = @BookId )



-- Get the similar books without using SUM() and with extra credit for using a Cartesian product.
--   Using MAX() in the last line.
; with A as (
  -- All attributes with MustMatch/MustNotMatch flags.
  select distinct AttributeId,
    case when AttributeId in ( select AttributeId from @BookMap where BookId = @BookId ) then 1 else 0 end as MustMatch
    from @BookMap
  ),
B as (
  -- All books except the search pattern book.
  select distinct BookId
    from @BookMap
    where BookId <> @BookId ),
P as (
  -- Cross product plus original data and coefficient of wickedness.
  select B.BookId, A.AttributeId, A.MustMatch,
    case
      when MustMatch = 1 and T.AttributeId is not NULL then 0
      when MustMatch = 0 and T.AttributeId is NULL then 0
      else 1
      end as Wicked
    from B cross join
      A left outer join
      @BookMap as T on T.BookId = B.BookId and T.AttributeId = A.AttributeId
  )
select BookId
  from B
  where ( select Max( Wicked ) from P where P.BookId = B.BookId ) = 0

-- Get the similar books without using SUM() and with extra credit for using a Cartesian product.
--   Using NOT EXISTS in the last line.
; with A as (
  -- All attributes with MustMatch/MustNotMatch flags.
  select distinct AttributeId,
    case when AttributeId in ( select AttributeId from @BookMap where BookId = @BookId ) then 1 else 0 end as MustMatch
    from @BookMap
  ),
B as (
  -- All books except the search pattern book.
  select distinct BookId
    from @BookMap
    where BookId <> @BookId ),
P as (
  -- Cross product plus original data and coefficient of wickedness.
  select B.BookId, A.AttributeId, A.MustMatch,
    case
      when MustMatch = 1 and T.AttributeId is not NULL then 0
      when MustMatch = 0 and T.AttributeId is NULL then 0
      else 1
      end as Wicked
    from B cross join
      A left outer join
      @BookMap as T on T.BookId = B.BookId and T.AttributeId = A.AttributeId
  )
select BookId
  from B
  where not exists ( select 42 from P where P.BookId = B.BookId and Wicked = 1 )

至少在一个有点乏味的营销演示中，我很好地利用了我的时间。

Answer 2

SELECT b.bookID
FROM BookMaP A
INNER JOIN BookMap B
   ON a.attributeID = B.AttributeID
WHERE a.BookID = 1 -- The id you want to compare against
GROUP BY b.bookID
HAVING COUNT(DISTINCT b.AttributeID) = COUNT(DISTINCT a.AttributeID)

我认为聚合和self JOIN是最好的选择。这可能需要调整，您可能只需要在HAVING子句中指定计数。

Answer 3

我在这里测试了我的答案：http://www.sqlfiddle.com/#!3/a9eec/4 (以及我的本地服务器)

;WITH AttributeSet AS
(
  SELECT DISTINCT
    B.BookId
    , SUBSTRING((SELECT 
                    (',' + CAST(A.AttributeId AS VARCHAR(4)))
                FROM BookMap A
                WHERE A.BookId = B.BookId
                ORDER BY A.AttributeId
                FOR XML PATH ('')),2,9999) AS AttributeSet
  FROM BookMap B
)
SELECT
    MatchingBooks.BookId
FROM AttributeSet BaseBook
INNER JOIN AttributeSet MatchingBooks
    ON MatchingBooks.AttributeSet = BaseBook.AttributeSet
WHERE BaseBook.BookId = 1