SQL：从许多表中获取用户表中USER表中的记录。最佳方法是什么？ [英] SQL:Getting count from many tables for a user record in USER table.Whats the best approach?

问题描述

我有4个SQL Server（2008版）表

I have 4 SQL server(2008 version) tables

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的USER记录以及userid上传的文件总数5，用户id上传的照片总数5，用户id创建的群组总数5，我可以通过一个查询获得所有这些信息吗？或者我应该在where子句中写一个USER ID = 5的表的SELECT COUNT（3个查询..？）

Now I want to get a USER record with id=5 along with Total number of Files uploaded by userid 5,Total Photos uploaded by user id 5,Total groups created by userid 5.Can i get all these with a single query ? OR should i write a SELECT COUNT of each table with USER ID=5 in the where clause ( 3 queries.. ?)

我可以用USER和FILES并进行计数以获取某个虚拟用户上载的文件数量。但是如何将照片数量和组数量添加到结果中？

I can write an inner join with USER and FILES and do the count to get the number of files uploaded by a purticular user.But how to add the number of photos and number of groups to the result ?

什么是最好的方法是这样做？

What is the best approach to do this ?

样本数据

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

文件

FILES

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

组

GROUPS

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

我要寻找的结果是

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

对于UserId = 2

For UserId=2

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

推荐答案

使用：

   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

要在一个查询中使用OUTER联接，就必须这样做。所有表的INNER联接都要求用户在结果集中必须有FILES，PHOTOS和GROUPS表中的至少一条记录。 OUTER连接意味着将返回具有至少一个表（FILES，PHOTOS或GROUPS）中的记录的用户。

You have to use OUTER joins for this to be in one query; INNER joins for all the tables would require that the user have at least one record in FILES, PHOTOS and GROUPS table to be in the resultset. An OUTER join means that users with records in at least one of the tables (FILES, PHOTOS or GROUPS) will be returned.

但是JOIN还会冒充结果集的风险，这会导致结果集膨胀是我的答案的先前版本中发生的问题。通过更改查询以将派生表/内联视图用于FILES，GROUPS和PHOTOS计数，问题得以解决，并且不需要派生表/内联视图之外的GROUP BY。

But JOINs also risk inflating the resultset, which is the issue that occurred in the previous version of my answer. By changing the query to use derived tables/inline views for the FILES, GROUPS and PHOTOS counts, the problem is solved and there's no need for a GROUP BY outside of the derived tables/inline views.

这篇关于SQL：从许多表中获取用户表中USER表中的记录。最佳方法是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！