php - 我的 JOIN + GROUP BY ... HAVING COUNT 查询是否正确？

Question

我是 SQL 的新手，我想实现以下查询:

我有两个表，LicenseTbl 和 UnlockTbl:

LicenseTbl 包含有关购买的软件许可证的信息:

LicenseID, ProgramID, Owner, Location, OrderNo, BlockTime

UnlockTbl 包含有关特定软件注册的信息:

UnlockID, LicenseID (foreign key into LicenseTbl), Timestamp, SerialNo, Key, UninstallTime

如果许可证被阻止或软件已卸载，BlockTime 和 UninstallTime 包含时间戳，否则为 NULL。

我想设计一个查询，为我提供满足以下条件的所有 LicenseID:

• belongs to a given customer,
• is not blocked,
• is either not listed in the UnlockTbl or there are < X different SerialNo's in lines which are not marked as uninstalled.

我已经写了这个，但我不确定它是否绝对正确(这是我有史以来第一个 SQL 查询之一):

SELECT LicenseID FROM LicenseTbl
JOIN UnlockTbl
   ON (LicenseTbl.LicenseID = UnlockTbl.LicenseID) 
WHERE  LicenseTbl.OrderNo   = '$givenOrderNo'
   AND LicenseTbl.Owner     = '$givenOwner'
   AND LicenseTbl.Location  = '$givenLocation'
   AND LicenseTbl.BlockTime IS NULL
   AND UnlockTbl.UninstallTime IS NULL
GROUP BY LicenseTbl.LicenseID, UnlockTbl.Key
HAVING COUNT(*) < $X

(这应该意味着，列出所有同时使用少于 X 次的许可证。我更喜欢那些使用最少但不知道如何排序的许可证。)

Answer 1

这是一个好的开始，但我会将查询更改为以下...

SELECT
  LicenseID
FROM
  LicenseTbl
LEFT JOIN
  UnlockTbl
    ON  UnlockTbl.LicenseID = LicenseTbl.LicenseID
    AND UnlockTbl.UninstallTime IS NULL
WHERE
      LicenseTbl.OrderNo   = '$givenOrderNo'
  AND LicenseTbl.Owner     = '$givenOwner'
  AND LicenseTbl.Location  = '$givenLocation'
  AND LicenseTbl.BlockTime IS NULL
GROUP BY
  LicenseTbl.LicenseID
HAVING
  COUNT(DISTINCT UnlockTbl.SerialNo) < $X
ORDER BY
  COUNT(DISTINCT UnlockTbl.SerialNo)

1). 左连接

LEFT JOIN 确保返回 LicenseTbl 中的所有行，即使 UnlockTbl 表中没有匹配项。 (如果没有匹配项，UnlockTbl 表的值全部表示为 NULL。)

2). UnlockTbl.UninstallTime IS NULL 在 JOIN 而不是 WHERE

WHERE 子句在JOIN 之后应用。这意味着 UnlockTbl 中 UninstallTime 具有真实值 (NOT NULL) 的任何记录都将被加入并然后得到过滤掉了。这反过来意味着，如果 所有 UnlockTbl 中的相关记录在 UninstallTime 中具有非 NULL 值，则该许可证的所有行都将获得过滤。

3). GROUP BY 仅针对许可证，而非 key 。

简单地说，我不知道你为什么把它放在那里，而且它没有出现在你想要的东西的英文描述中。

因为您需要 LicenseID 列表，仅该字段分组可确保您为每个 LicenseID 获得一条记录。

4). HAVING 子句修改为查看 COUNT(DISTINCT SerialNo)

COUNT(*) 计算所有记录。即使没有匹配(所有 UnlockTbl 值都显示为 NULL)，这也会返回 1。

COUNT(SerialNo) 仅计算 SerialNo 为 NOT NULL 的记录。如果没有匹配(所有 UnlockTbl 值显示为 NULL)，这将返回 0。

COUNT(DISTINCT SerialNo) 也只计算 SerialNo 不为 NULL 的记录，但将重复的 sme 值视为 1 个条目。

5). 按计数排序(不同序列号)

采用与在 HAVING 子句中过滤的值相同的值，并按它排序。