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

给定以下数据库设置:

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 '
' . $customer["companyName"] . '
';

  // 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 '
Total Urine Tests: ' . $testCount["count"] . '
';

  // 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 '
Total Biopsy Tests: ' . $testCount["count"] . '
';

  // 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 '
Total Blood Tests: ' . $testCount["count"] . '
';
endforeach;

正如你可能会说这是hella repetative,但到目前为止它是获取我想要的信息的唯一方法.我知道COUNT(),GROUP BY一旦我进入foreach循环,我就可以用a 进行测试,但是如果我这样做,我将不会回到0用于客户可能没有的测试,如果testType为0,我需要显示.

我更熟悉PhP,然后我在MySQLi和SQL中.这里有什么我想念的吗？当然必须有更好的方法来获得所有这些,甚至可以在一个查询中获得它？任何建议或正确方向的观点都会很棒.

简单聚合和主要查询上的左连接或两个连接应该这样做.可以使用交叉连接来标识唯一的测试类型,并通过将其交叉连接到客户/地址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.

客户:地址:TestTypes:测试基数预期:1:1:M:M

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

显然,如果您需要限制特定客户,可以添加where子句,但我认为您希望所有客户的测试类型都计入所有可能的测试类型.