SQL 查询从另一个 SQL 查询中选择 COUNT

Question

我在进行 SQL SERVER 2000 查询时遇到了一些问题。这是我的场景：

• 我有一个名为 Folders 的表，其中包含 3 列：pk_folderID、folderName 和 fk_userID >。

• 此外，我还有另一个名为 FolderMedia 的表，它存储属于某个文件夹的媒体（无论什么）。有 2 列：fk_folderID、fk_media。

• 最后，我有一个名为 Media 的表，它存储一些媒体详细信息。它有一个主键pk_media，在其他列中，它有一个MediaType列，它告诉媒体的类型：图像或视频。

现在，我想要一个执行以下操作的查询：

选择属于某个 fk_userID 的所有文件夹，然后获取该文件夹中的媒体数量。我在 StackOverflow 上看到过这样的查询，但我没有设法升级它以获取 2 个媒体计数（基于其类型）

基本上，获取属于的所有文件夹的文件夹详细信息（名称等）用户（fk_userID），并且对于每个文件夹，获取其中的图像和视频的数量（作为单独的值）。

选择基本上会返回： 文件夹名称、计数（文件夹中的图像）、计数（文件夹中的视频）、其他文件夹详细信息。

一个明显的解决方案是获取所有文件夹，然后手动计算其中的图像/视频数量......但我首先想尝试查询。

谢谢你，

Answer 1

基本上是这样的：

SELECT
  f.pk_folderID,
  f.folderName,
  VideoCount = COUNT(CASE m.MediaType WHEN 'Video' THEN 1 END),
  ImageCount = COUNT(CASE m.MediaType WHEN 'Image' THEN 1 END)
FROM Folder f
  LEFT JOIN FolderMedia fm ON f.pk_folderID = fm.fk_folderID
  LEFT JOIN Media m ON fm.fk_media = m.pk_media
WHERE f.fk_userID = @UserID
GROUP BY
  f.pk_folderID,
  f.folderName

---

更新（基于附加请求）：

要将某种TOP 1 Media.Name包含到结果集中，可以将上面的查询更改为this：

SELECT
  f.pk_folderID,
  f.folderName,
  VideoCount = COUNT(CASE m.MediaType WHEN 'Video' THEN 1 END),
  ImageCount = COUNT(CASE m.MediaType WHEN 'Image' THEN 1 END),
  MediaName  = MAX(CASE fm.timestamp WHEN t.timestamp THEN m.Name END)
FROM Folder f
  LEFT JOIN FolderMedia fm ON f.pk_folderID = fm.fk_folderID
  LEFT JOIN Media m ON fm.fk_media = m.pk_media
  LEFT JOIN (
    SELECT
      fk_folderID,
      timestamp = MIN(timestamp)
    FROM FolderMedia
    GROUP BY fk_folderID
  ) t ON fm.fk_folderID = t.fk_folderID AND fm.timestamp = t.timestamp
WHERE f.fk_userID = @UserID
GROUP BY
  f.pk_folderID,
  f.folderName

如果最小 FolderMedia.timestamp 值在其文件夹内不唯一，则相应 Media.Name 的最终值将由其字母顺序排序决定。特别是，上面的查询选择了集合中的最后一个（使用 MAX()）。

Answer 2

从 Folders 表中获取所需的所有数据，将其与 FolderMedia 和 Media 左侧连接
使用 sum 和 case 来计算所有视频和图像的数量。

SUM(CASE WHEN mediaTypeId = videoId THEN 1 ELSE 0 END) as videoCount

Answer 3

//选择文件夹名称，计数（当Folders.pk_folderID为1时，否则为空结束）

count(SELECT FolderMedia.fk_media from FolderMedia JOIN Media on fk_media=pk_media where image is not null) as nrImg 

等..

Answer 4

这是带有一些示例数据的查询。希望有帮助。

declare @Folders table (pk_folderID int, folderName varchar(32), fk_userID int)
declare @Media table (pk_media int, name varchar(50), type varchar(32))
declare @FolderMedia table (fk_folderID int, fk_media int)

insert into @Folders values (1, 'Folder1', 1000)
insert into @Folders values (2, 'Folder2', 1000)
insert into @Folders values (3, 'Folder1', 2000)
insert into @Folders values (4, 'Folder1', 2000)

insert into @Media values (1, 'graph.jpg', 'image')
insert into @Media values (2, 'timer.jpg', 'image')
insert into @Media values (3, 'timer1.jpg', 'image')
insert into @Media values (4, 'harry_potter.mpeg', 'video')
insert into @Media values (5, 'harry_potter1.mpeg', 'video')
insert into @Media values (6, 'harry_potter2.mpeg', 'video')

insert into @FolderMedia values (1, 1)
insert into @FolderMedia values (1, 3)
insert into @FolderMedia values (1, 6)
insert into @FolderMedia values (2, 2)
insert into @FolderMedia values (2, 4)

select folderName, fk_userID, imageData.imgCount, videoData.videoCount from 
@Folders
left outer join
(
    select fk_folderID, COUNT(*) as imgCount
    from @FolderMedia
    inner join @Media 
    on fk_media = pk_media
    and type = 'image'
    group by fk_folderID
) as imageData
on imageData.fk_folderID = pk_folderID
left outer join
(
    select fk_folderID, COUNT(*) as videoCount
    from @FolderMedia
    inner join @Media 
    on fk_media = pk_media
    and type = 'video'
    group by fk_folderID
) as videoData
on videoData.fk_folderID = pk_folderID
where fk_userID = 1000