将三个计数查询合并到一个结果

Question

我正在学习，但我已经达到了自我成长的极限。我希望有以下三个查询，显示在一个查询。但我对“联合”和“联合”的术语不太了解。

SELECT    DID.display as "DisplayID",
          COUNT(AltID.REFID) as "C_AltIDs"
FROM      DID,
          AltID
WHERE     DID.REFID = AltID.REFID
GROUP BY  AltID.REFID, DID.display 
ORDER BY  COUNT(AltID.REFID) desc;


SELECT    DID.display as "DisplayID",
          COUNT(address.REFID) as "C_AltAddresses"
FROM      DID,
          address
WHERE     DID.REFID = address.REFID
GROUP BY  address.REFID, DID.display 
ORDER BY  COUNT(address.REFID) desc;


SELECT    DID.display as "DisplayID",
          COUNT(name.REFID) as "C_AltNames"
FROM      DID,
          name
WHERE     DID.REFID = name.REFID
GROUP BY  name.REFID, DID.display 
ORDER BY  COUNT(name.REFID) desc;



**Desired end result**
|DisplayID|AltIDs|AltAddresses|AltNames|
    01       32        32         2
    02        2        12         4

Answer 1

也许使用相关的子查询对您有用吗？

SELECT    
  DID.display as "DisplayID",
  (SELECT COUNT(AltID.REFID) FROM AltID WHERE REFID = DID.REFID) as "C_AltIDs",
  (SELECT COUNT(address.REFID) FROM address WHERE REFID = DID.REFID) as "C_AltAddresses",
  (SELECT COUNT(name.REFID) FROM name WHERE REFID = DID.REFID) as "C_AltNames" 
FROM DID
ORDER BY display 

编辑:在评论中要求的附加解决方案：

;WITH x AS (
  SELECT    
    DID.display as "DisplayID",
    (SELECT COUNT(AltID.REFID) FROM AltID WHERE REFID = DID.REFID) as "C_AltIDs",
    (SELECT COUNT(address.REFID) FROM address WHERE REFID = DID.REFID) as "C_AltAddresses",
    (SELECT COUNT(name.REFID) FROM name WHERE REFID = DID.REFID) as "C_AltNames" 
  FROM DID
) 

SELECT *, C_AltIDs+C_AltAddresses+C_AltNames AS SumOfCounts 
FROM x

Answer 2

肯定会让人感到困惑。当您想将结果(或表)连接在一起时，请这样想：

1. 如果希望结果位于同一行，则需要连接
2. 如果你想把结果堆在一起，那么你就需要一个联盟

在您的情况下，您希望它们并排，所以您将不得不将所有这些一起。

SELECT
    t1.display,
    t1.C_AltIDs,
    t2.C_AltAddresses,
    t3.C_AltNames
FROM
    (
        SELECT    DID.display as "DisplayID",
              COUNT(AltID.REFID) as "C_AltIDs"
        FROM      DID,
              AltID
        WHERE     DID.REFID = AltID.REFID
        GROUP BY  DID.display 
    ) t1

    LEFT OUTER JOIN
    (

        SELECT    DID.display as "DisplayID",
              COUNT(address.REFID) as "C_AltAddresses"
        FROM      DID,
              address
        WHERE     DID.REFID = address.REFID
        GROUP BY  DID.display 
    ) t2 ON
        t1.Display = t2.Display

    LEFT OUTER JOIN
    (
        SELECT    DID.display as "DisplayID",
              COUNT(name.REFID) as "C_AltNames"
        FROM      DID,
              name
        WHERE     DID.REFID = name.REFID
        GROUP BY  DID.display 
    ) t3 ON
        t1.Display = t3.Display

我已经从每个子查询中删除了ORDER BY子句，因为您不能在子查询中执行ORDER BY。我还从COUNT()子句中删除了使用GROUP BY进行聚合的字段，因为如果您想要对它们进行计数，那么将它们放在其中没有多大意义。出于某种原因，MySQL允许您这样做，即使这是荒谬的，但如果您试图在已经在SELECT子句中聚合的字段上进行GROUP BY，则大多数RDBMS会抛出一个错误。