php - 一种绕过结果以使用第一个结果集访问更多信息的方法

Question

给定以下数据库设置:

Customers
| customerId | accountId | addressId |
|------------|-----------|-----------|
|      1     |    110    |     8     |
|      2     |    120    |     9     |
|      3     |    130    |     10    |

Address
| addressId | companyName | state |
|-----------|-------------|-------|
|     8     |  FooBar Co  |   FL  |
|     9     |   Self Co   |   VA  |
|     10    |    Cli Co   |   CA  |

Tests
| testId | accountId | testType | Status  |
|--------|-----------|----------|---------|
|    1   |    120    |   Urine  |   Done  |
|    2   |    110    |   Blood  | Pending |
|    3   |    110    |   Blood  | Pending |
|    4   |    130    |  Biopsy  |   Done  |
|    5   |    130    |   Blood  |   Done  |
|    6   |    130    |   Urine  | Pending |
|    7   |    110    |  Biopsy  | Pending |
|    8   |    120    |   Urine  | Pending |
|    9   |    110    |  Biopsy  | Pending |
|   10   |    110    |   Urine  | Pending |

有没有办法让我绕过 mysqli 客户结果集，以便根据测试类型的名称和 accountId 获取每个 testType 的 COUNT。因此，例如我目前正在这样做:

$sql = "SELECT C.accountId, A.companyName 
         FROM Customers C
         JOIN Address A
         ON C.addressId=A.addressId";

$result = mysqli_query($connection,  $sql);

$customers = mysqli_fetch_all($result, MYSQLI_ASSOC);

foreach( $customers as $customer ) :
  echo '<h2>' . $customer["companyName"] . '</h2>';

  // Urine Tests Count
  $sql = 'SELECT COUNT(testId) as count FROM Tests WHERE accountId=' . $customer["accountId"] . ' AND testType="Urine"';
  $result = mysqli_query($connection, $sql);
  $testCount = mysqli_fetch_array($result, MYSQLI_ASSOC);
  echo '<p>Total Urine Tests: ' . $testCount["count"] . '</p>';

  // Biopsy Tests Count
  $sql = 'SELECT COUNT(testId) as count FROM Tests WHERE accountId=' . $customer["accountId"] . ' AND testType="Biopsy"';
  $result = mysqli_query($connection, $sql);
  $testCount = mysqli_fetch_array($result, MYSQLI_ASSOC);
  echo '<p>Total Biopsy Tests: ' . $testCount["count"] . '</p>';

  // Blood Tests Count
  $sql = 'SELECT COUNT(testId) as count FROM Tests WHERE accountId=' . $customer["accountId"] . ' AND testType="Blood"';
  $result = mysqli_query($connection, $sql);
  $testCount = mysqli_fetch_array($result, MYSQLI_ASSOC);
  echo '<p>Total Blood Tests: ' . $testCount["count"] . '</p>';
endforeach;

您可能会说这是重复的，但到目前为止，这是获得我想要的信息的唯一方法。我知道一旦进入 foreach 循环，我就可以使用 GROUP BY 执行 COUNT() 测试，但如果我这样做，我将无法返回0 用于客户可能没有的测试，如果 testType 为 0，我需要显示。

与 MySQLi 和 SQL 相比，我更精通 PhP。我在这里缺少什么吗？当然必须有更好的方法来获得所有这些，甚至可以在一个查询中获得它？任何建议或指向正确方向的观点都会很棒。

Answer 1

简单的聚合和主查询上的一两个左连接应该可以做到。交叉连接可用于识别唯一的测试类型，通过将其交叉连接到客户/地址 1:1 关系，我们为每个客户的每个测试类型生成一行。这样，当帐户不存在该类型的测试时，每个客户都会列出每种类型的一种，并带有适当的计数 (0)。

SELECT C.accountId, A.companyName, Z.TestType, Count(distinct T.TestID)
FROM Customers C
LEFT JOIN Address A
  ON C.addressId=A.addressId
CROSS JOIN (Select Distinct TestType from Tests) Z
LEFT JOIN Tests T
  on T.AccountID = C.AccountID
 and Z.TestType = T.TestType
 and ReceiveDT>=SomeDate
 and ReceiveDT<=SomeOtherDate
GROUP BY C.accountId, A.companyName, Z.TestType

LEFT Joinn 将返回所有客户记录，并且仅返回地址匹配的客户记录，并且仅返回与客户记录匹配的测试记录。因此，当没有类型/测试匹配时，count 将为 0。

Customer:Address:TestTypes:Tests 预期基数:1:1:M:M

因此，如果只有 3 种类型的测试，我希望在结果中看到 3* 的客户记录数。这是因为 Address 和 customers 似乎是 1:1，测试类型的数量将决定我们复制客户记录的次数，并且正在聚合左连接到测试，因此它不会添加到行数中。

显然，如果您需要限制特定客户，您可以添加一个 where 子句，但我认为您想要所有客户的所有可能测试类型的测试类型计数。