sql - T-SQL 按独特的多对多关系性能分组

Question

我正在尝试创建一个 SQL 查询，它可以按唯一的多对多关系进行分组。我有一个 Event 表，其中可以有一位或多位艺术家。我想对事件进行分组但是 前提是它们没有相同的阵容(完全相同的艺术家表演)。如果他们的阵容完全相同，我只想获得最新的(=事件日期)事件。

经过一些调查，似乎必须使用外部应用来完成，但我真的想不通。

更新

我需要在预期结果表中描述的结果集中为每个艺术家、每个事件单独的行。它们(或艺术家 ID)不应连接在一个字段中。

更新 2

我确实得到了以下查询来执行我需要的操作，但 EXISTS 条件的性能很差。能否以更高效的方式重写？

注意:使用 EXISTS 条件的 SQL 语句效率非常低，因为子查询对外部查询表中的每一行都重新运行。有更有效的方法来编写大多数不使用 EXISTS 条件的查询。

 SELECT  E.*
 FROM [Events] AS E
 WHERE EXISTS(
    SELECT NULL AS [EMPTY]
    FROM [Headliners] AS H
    WHERE H.[EventId] = E.[EventId]
 )
AND NOT EXISTS(
     SELECT NULL AS [EMPTY]
     FROM [Events] AS E2
     WHERE E2.[Date] < E.[Date] 
     AND NOT EXISTS(
          SELECT NULL AS [EMPTY]
          FROM [Headliners] AS H1
          WHERE NOT EXISTS(
               SELECT NULL AS [EMPTY]
               FROM [Headliners] AS [t4]
               WHERE [t4].[EventId] = E.[EventId]
               AND CASE WHEN [t4].[ArtistId] <> H1.[ArtistId] THEN 1 ELSE 0 END = 0) 
         AND H1.[EventId] = E2.[EventId]) 
    AND NOT EXISTS(
         SELECT NULL AS [EMPTY]
         FROM [Headliners] AS H2
         WHERE NOT EXISTS(
              SELECT NULL AS [EMPTY]
              FROM [Headliners] AS [t6]
              WHERE [t6].[EventId] = E2.[EventId]
              AND CASE WHEN [t6].[ArtistId] <> H2.[ArtistId] THEN 1 ELSE 0 END = 0) 
        AND H2.[EventId] = E.[EventId]
     )
 )

预期结果

 x-----------x---------x------------x----------x
 |  EventId  |   Name  | Date       | ArtistId |
 x-----------x---------x------------x----------x
 |   1       |   E1    | 2016-01-01 | 1        |
 |   1       |   E1    | 2016-01-01 | 2        |
 |   2       |   E2    | 2016-01-02 | 3        |
 |   4       |   E4    | 2016-01-04 | 5        |
 |   4       |   E4    | 2016-01-04 | 6        |
 |   5       |   E5    | 2016-01-05 | 4        |
 |   6       |   E6    | 2016-01-06 | 5        |
 x-----------x---------x------------x----------x

架构

事件表

 x-----------x---------x------------x
 |  EventId  |   Name  | Date       |
 x-----------x---------x------------x
 |   1       |   E1    | 2016-01-01 |
 |   2       |   E2    | 2016-01-02 |
 |   3       |   E3    | 2016-01-03 |
 |   4       |   E4    | 2016-01-04 |
 |   5       |   E5    | 2016-01-05 |
 |   6       |   E6    | 2016-01-06 |
 |   7       |   E7    | 2016-01-07 |
 |   8       |   E8    | 2016-01-08 |
 x-----------x---------x------------x

标题表

 x-----------x-------------x
 |  EventId  |   ArtistId  |
 x-----------x-------------x
 |   1       |   1         |
 |   1       |   2         |
 |   2       |   3         |
 |   3       |   2         |
 |   3       |   1         |
 |   4       |   5         |
 |   4       |   6         |
 |   5       |   4         |
 |   6       |   5         |
 |   8       |   3         |
 x-----------x-------------x

艺术家表

 x------------x---------x--------------x
 |  ArtistId  |   Name  |   Bio        |
 x------------x---------x--------------x
 |   1        |   A1    | BIO1         |
 |   2        |   A2    | BIO2         |
 |   3        |   A3    | BIO3         |
 |   4        |   A4    | BIO4         |
 |   5        |   A5    | BIO5         |
 |   6        |   A6    | BIO6         |
 x------------x---------x--------------x

Answer 1

此解决方案假设您可以将给定事件的所有 ArtistId 连接到一个以逗号分隔的列表(没有空格)中，该列表将适合 varchar(1000)，这将是如果您的所有 ArtistId 值都以十亿为单位(10 位)，则为 90。 Varchar(8000) 也可以，但如果您必须使用 varchar(max)，事情可能会变得不稳定。

我用它来设置数据:

CREATE TABLE Event
 (
   EventId  int  not null
  ,Name     varchar(50)  not null
  ,Date     date  not null
 )

INSERT Event values
  (1,'E1', 'Jan 1, 2016')
 ,(2,'E2', 'Jan 2, 2016')
 ,(3,'E3', 'Jan 3, 2016')
 ,(4,'E4', 'Jan 4, 2016')
 ,(5,'E5', 'Jan 5, 2016')
 ,(6,'E6', 'Jan 6, 2016')
 ,(7,'E7', 'Jan 7, 2016')
 ,(8,'E8', 'Jan 8, 2016')

CREATE TABLE Headliner
 (
   EventId  int  not null
  ,ArtistId int  not null
 )

INSERT Headliner values
  (1,1)
 ,(1,2)
 ,(2,3)
 ,(3,2)
 ,(3,1)
 ,(4,5)
 ,(4,6)
 ,(5,4)
 ,(6,5)
 ,(8,3)

CREATE TABLE Artist
 (
   ArtistId  int  not null
  ,Name      varchar(50)  not null
  ,Bio       varchar(50)  not null
 )

INSERT Artist values
   (1, 'A1', 'BI01')
  ,(2, 'A2', 'BI02')
  ,(3, 'A3', 'BI03')
  ,(4, 'A4', 'BI04')
  ,(5, 'A5', 'BI05')
  ,(6, 'A6', 'BI06')

SELECT
   EvntId
  ,isnull(
 from Headliner
 group by EventId

接下来，我创建了一个函数来连接给定 EventId 的所有 ArtistId:

IF objectproperty(object_id('dbo.Concat'), 'isScalarFunction') = 1
    DROP FUNCTION dbo.Concat

GO
CREATE FUNCTION dbo.Concat
 (
  @EventId  int
 )
RETURNS varchar(1000)
BEGIN
    DECLARE @Concatenated  varchar(1000)

    SELECT @Concatenated = isnull(@Concatenated + ',' , '') + cast(ArtistId as varchar(10))
     from Headliner
     where EventId = @EventId
     order by ArtistId

    RETURN @Concatenated
END
GO

这允许我们使用双重嵌套的 CTE 来完成这项工作。 (如果性能有问题，可以使用临时表来完成，但这似乎不太可能。)

;WITH cteSetList
 as (--  Build list of artists for each Event, and produce
     --  an ordering on events by date
     select
        EventId
       ,Name
       ,Date
       ,dbo.Concat(EventId)  ArtistList
       ,row_number() over (order by Date)  Ranking
      from Event
    )
 ,cteRankLists
  as (--  Find earliest event for each ArtistList
      select
         ArtistList
        ,min(Ranking)  FirstForList
       from cteSetList
       group by ArtistList
     )
 --  Take that earliest list of artists, join it back to the first query
 --  to get the EventId, join that back to the base tables and we're done
 select
    sl.EventId
   ,sl.Name
   ,sl.Date
   ,he.ArtistId
  from cteRankLists rl
   inner join cteSetList sl
    on sl.Ranking = rl.FirstForList
   left outer join Headliner he
    on he.EventId = sl.EventId
  order by sl.EventId, he.ArtistId

额外的奖励，它甚至可以解释“没有艺术家”的情况。性能和其他不可估量的因素取决于所使用的表和集合的大小。