代码之家 › 专栏 › 技术社区 › Shyju

SQL:从用户表中的许多表中获取用户记录的计数。最好的方法是什么?

aggregate-functions sql-server-2008 tsql sql-server sql

Shyju · 技术社区 · 14 年前

我有4个SQL Server(2008版本)表

1) USER- to store user information (Fields : UserId,UserName)
2) FILES - to store files uploaded by user (FileId,FileName,UserId)
3) PHOTOS -to store files uploaded by user (PhotoId,PhotoName,UserId)
4) GROUPS= to store groups created by user ( GroupId,GroupName,UserId)

现在我想得到一个id=5的用户记录,以及用户id 5上传的文件总数,用户id 5上传的照片总数,用户id 5创建的组总数。我可以通过一个查询得到所有这些吗?或者,我应该在WHERE子句(3个查询..?)

我可以编写一个与用户和文件的内部连接,并进行计数以获取由特定用户上载的文件数。但是如何将照片数和组数添加到结果中呢?

最好的方法是什么?

样本数据

用户

USERID     USERNAME
------     ---------
1          Shyju
2          OMG
3          Gus

文件夹

FILEID      FILENAME    USERID
------      --------    ------
101         Sample1     1
102         Secondone   1
103         NewOne      2
104         BetterOne   3
105         Ocean       3
106         Sea         3

组

GROUPID   GROUPNAME   USERID
-------   ---------   ------
 51       Group-A     1
 52       Group-B     2
 53       Group-C     2
 54       Group-D     2
 55       Group-E     3
 56       Group-F     3

我要找的结果是

对于用户ID=1

 USERID  USERNAME  FILECOUNT  GROUPCOUNT
 ------  --------  ---------  ----------
  1      Shyju     2          1

对于userid=2

 USERID  USERNAME  FILECOUNT  GROUPCOUNT
 ------  --------  ---------  ----------
  2      OMG        1          3

1 回复 | 直到 14 年前

OMG Ponies 14 年前

用途:

   SELECT u.userid,
          u.username,
          COALESCE(f.numFiles, 0) AS numFiles,
          COALESCE(p.numPhotos, 0) AS numFiles,
          COALESCE(g.numGroups, 0) AS numGroups
     FROM [USER] u
LEFT JOIN (SELECT t.userid,
                  COUNT(*) AS numFiles
             FROM [FILES] t
         GROUP BY t.userid)f ON f.userid = u.userid
LEFT JOIN (SELECT t.userid,
                  COUNT(*) AS numPhotos
             FROM [PHOTOS] t
         GROUP BY t.userid) p ON p.userid = u.userid
LEFT JOIN (SELECT t.userid,
                  COUNT(*) AS numGroups
             FROM [GROUPS] t
         GROUP BY t.userid) g ON g.userid = u.userid
    WHERE u.userid = 2

您必须在一个查询中使用外部联接;所有表的内部联接都要求用户在结果集中的文件、照片和组表中至少有一条记录。外部联接意味着至少有一个表(文件、照片或组)中的记录的用户将被返回。

但加入也会使结果集膨胀,这是我之前版本答案中出现的问题。通过将查询更改为对文件、组和照片计数使用派生表/内联视图,解决了问题,并且不需要在派生表/内联视图之外进行分组。