sql—获取多个表的每个租户的行数

gdx19jrr  于 2021-07-26  发布在  Java
关注(0)|答案(3)|浏览(301)

假设我有两张table: Tenants 以及 Wargles . Wargles 有外键指向 Tenants 打电话 TenantId . 如果我想得到每个租户的wargle数量,我可以这样做:

SELECT t.Id as TenantId, count(w.Id) as WargleCount
FROM Tenants t
JOIN Wargles w ON w.TenantId = t.Id
GROUP BY t.Id

现在,假设我还有一张table, Fiddles ,作为 Wargles 有一个fk指向 Tenants . 如何向上面的查询中添加另一列,以便获得每个租户的wargle数和fiddle数?
我试过这个:

SELECT t.Id as TenantId, count(w.Id) as WargleCount, count(f.Id) as FiddleCount
FROM Tenants t
JOIN Wargles w ON w.TenantId = t.Id
JOIN Fiddles f ON f.TenantId = t.Id
GROUP BY t.Id

但这行不通,因为它将为warglecount和fiddlecount提供相同的数字,这两个表中的行的乘积。

j9per5c4

j9per5c41#

使用两个子选择

SELECT t.Id as TenantId, 
       (SELECT Count(1) FROM Fiddles F WHERE F.TenantId = T.Id) as FiddleCount,
       (SELECT Count(1) FROM Wargles W WHERE W.TenantId = T.Id) as WargleCount
  FROM Tenants t
uxh89sit

uxh89sit2#

最有效的方法可能是使用相关子查询:

SELECT t.Id as TenantId,
       (SELECT COUNT(*)
        FROM Wargles w
        WHERE w.TenantId = t.Id
      ) as WargleCount, count(f.Id) as FiddleCount
      (SELECT COUNT(*)
       FROM Fiddles f
       WHERE f.TenantId = t.Id
     ) as FiddleCount
FROM Tenants t;

特别是,这可以利用上的索引 Wargles(TenantId) 以及 Fiddles(TenantId) .

2uluyalo

2uluyalo3#

在您的情况下,作为可扩展的解决方案,我建议使用标量函数。

/* SAMPLE DATA ARRANGE */
CREATE TABLE Tenants (Id INT, Title NVARCHAR(5)) ; INSERT INTO Tenants VALUES (1, 'A'), (2, 'B') , (3, 'C');
CREATE TABLE Wargles (Id INT,TenantId INT);INSERT INTO Wargles VALUES (1, 1), (2, 1) , (3, 1) , (4, 2), (5, 2) , (6, 1), (7, 3) , (8, 3);
CREATE TABLE Fiddles (Id INT,TenantId INT);INSERT INTO Fiddles VALUES (1, 1), (2, 1) , (3, 1) , (4, 2), (5, 2) , (6, 2), (7, 3) , (8, 2);

函数

/*NEEDED CODE*/
CREATE FUNCTION dbo.ufnGetTenantsNo ( @Id AS INT , @Tb AS INT)
RETURNS INT
AS
BEGIN
    DECLARE @Result INT = 0;

    IF (@TB = 1)
        SELECT @Result = COUNT(*)
        FROM Wargles
        WHERE TenantId = @Id
    ELSE
        SELECT @Result = COUNT(*)
        FROM Fiddles
        WHERE TenantId = @Id

    RETURN @Result
END
GO

select语句

SELECT Id AS TenantId
    ,dbo.ufnGetTenantsNo(Id, 1) AS WargleCount
    ,dbo.ufnGetTenantsNo(Id, 2) AS FiddleCount
FROM Tenants

相关问题