帮我编写一个SQL交叉表查询

Question

我有三个长这样的表：

tblVideo:
    VideoID     |   Video Name
          1             video 1
          2             video 2
          3             video 3
          4             video 4

tblCategory:
    CategoryID  |   CategoryName
          1           category1
          2           category2
          3           category3

tblVideoCategory:
     VideoID    |    CategoryID
          1               3
          2               1
          2               2
          3               1
          3               2
          3               3
          4               1

我希望您能够编写一个查询，返回一个类似于这样的表格：

vVideoCategory:
VideoID   |   VideoName   |   category1   |   category2   |   category3
   1           video 1           false           false          true 
   2           video 2           true            true           false
   3           video 3           true            true           true
   4           video 4           true            false          false

我已经试过寻找示例，但似乎没有找到完全相同的。任何帮助都将不胜感激，谢谢。

肯定正在寻找允许更改和添加/删除类别的东西。

- matthew_360

Answer 1

使用：

  SELECT v.videoid,
         v.video_name,
         COALESCE(MAX(CASE WHEN vc.categoryid = 1 THEN 'true' END), 'false') AS category1,
         COALESCE(MAX(CASE WHEN vc.categoryid = 2 THEN 'true' END), 'false') AS category2,
         COALESCE(MAX(CASE WHEN vc.categoryid = 3 THEN 'true' END), 'false') AS category3
    FROM tblvideo v
    JOIN tblvideocategory vc ON vc.videoid = v.videoid
GROUP BY v.videoid, v.video_name

SQL Server 2005+:

DECLARE @SQL AS NVARCHAR(4000)
DECLARE @categoryid AS INT

DECLARE CUR CURSOR FAST_FORWARD FOR
   SELECT c.categoryid
     FROM tblcategory c
 ORDER BY c.categoryid

SET @SQL = N'SELECT v.videoid,
                    v.video_name, '

OPEN CUR
FETCH NEXT FROM CUR INTO @categoryid
WHILE @@FETCH_STATUS = 0
BEGIN

  SET @SQL = @SQL + ' COALESCE(MAX(CASE WHEN vc.categoryid = '+ @categoryid +' THEN 'true' END), 'false') AS category'+ @categoryid +' ,'

  FETCH NEXT FROM CUR INTO @categoryid
END

CLOSE CUR;
DEALLOCATE CUR;

--Get rid of trailing comma at the end
SELECT @SQL = SUBSTRING(@SQL, 1, LEN(@SQL) -1)

SET @SQL = @SQL + ' FROM tblvideo v
                    JOIN tblvideocategory vc ON vc.videoid = v.videoid
                GROUP BY v.videoid, v.video_name 
                ORDER BY v.videoid, v.video_name '

BEGIN

  EXEC sp_executesql @SQL

END

- OMG Ponies

---

@OMG - 如果没有分类数量的限制会怎样？ - LittleBobbyTables - Au Revoir

@LittleBobbyTables：如果是这种情况，可以使用动态SQL。但是OP没有指定要提供哪个数据库的信息以及使用哪种动态SQL语法。 - OMG Ponies

应该明确指定，我确实想要动态分类。 - matthew_360

我正在使用 SQL Server 2005，但我可以安装 2008 版本。 - matthew_360

@matthew_360：这并不需要转移到2005。我更新了以添加一个动态SQL示例。 - OMG Ponies

最终完美解决了，谢谢天哪。我只是稍微改了一下。 - matthew_360

Answer 2

我想稍微展开一下OMG Ponies给我的内容：

@"            
            DECLARE @SQL AS NVARCHAR(4000)
            DECLARE @categoryid AS INT

            DECLARE CUR CURSOR FAST_FORWARD FOR
               SELECT c.categoryid
                 FROM Category c
             ORDER BY c.categoryid

            SET @SQL = 'SELECT v.videoid, v.title, v.Tags, '

            OPEN CUR
            FETCH NEXT FROM CUR INTO @categoryid
            WHILE @@FETCH_STATUS = 0
            BEGIN
              SET @SQL = @SQL + ' COALESCE(MAX(CASE WHEN vc.categoryid = ' + str(@categoryid) + ' THEN ''true'' END), ''false'') AS [category' + ltrim(str(@categoryid)) + '] ,'
              FETCH NEXT FROM CUR INTO @categoryid
            END

            CLOSE CUR;
            DEALLOCATE CUR;

            --Get rid of trailing comma at the end
            SELECT @SQL = SUBSTRING(@SQL, 1, LEN(@SQL) -1)

            SET @SQL = @SQL + ' FROM Video v
                                LEFT JOIN VideoCategory vc ON vc.videoid = v.videoid
                            " + where+ @"
                            GROUP BY v.videoid, v.title, v.Tags 
                            ORDER BY v.title, v.videoid, v.Tags '

            BEGIN
              EXEC sp_executesql @SQL
            END            
        "

表名与我最初开始的有些不同，但主要更改是我将JOIN更改为LEFT JOIN，以包括尚未标记为类别的视频。此外，我还包括了一个where子句。

- matthew_360