sql查询帮助（计数）

Question

我有3张桌子

CUSTOMERS (CUSTOMER_ID, LASTNAME, FIRSTNAME, ... other fields)
LICENSES(LICENSE_ID, CREATED_AT, RELEASE_ID, CUSTOMER_ID, ... other fields)
RELEASES(RELEASE_ID, RELEASE_NAME, RELEASE_NUMBER, ... other fields)

许可证中的CREATED_AT是一个日期（非空）字段。

表根据同名主键/外键进行关联；客户可以拥有0个或多个许可证，每个许可证都有一个版本。

我想从这些表格中得到：

客户的名字、姓氏和创建的最后一个许可证的release\u id（根据许可证中的created\u AT字段查找最后一个），如果有。

对于这个问题，我使用了以下查询：

SELECT CUSTOMERS.FIRSTNAME,
       CUSTOMERS.LASTNAME,

  (SELECT RELEASES.RELEASE_ID
   FROM RELEASES
   INNER JOIN LICENSES ON LICENSES.RELEASE_ID = RELEASES.RELEASE_ID
   INNER JOIN CUSTOMERS AS t ON t.CUSTOMER_ID = LICENSES.CUSTOMER_ID
   WHERE t.CUSTOMER_ID = CUSTOMERS.CUSTOMER_ID
   ORDER BY LICENSES.CREATED_AT DESC LIMIT 1) AS REL_ID
FROM CUSTOMERS
WHERE EXISTS
    (SELECT 1
     FROM RELEASES
     INNER JOIN LICENSES ON LICENSES.RELEASE_ID = RELEASES.RELEASE_ID
     INNER JOIN CUSTOMERS AS t ON LICENSES.CUSTOMER_ID = t.CUSTOMER_ID
     AND t.CUSTOMER_ID = CUSTOMERS.CUSTOMER_ID)

这似乎有效，但我问是否有人可以证实我这一点，或者是否有可能使它更简单。

我想从这些表中获得的其他数据是发布版。RELEASE\u ID，RELEASES。RELEASE\u NAME，RELEASES。RELEASE\u NUMBER和上次获得此版本许可证的客户数（根据许可证中的CREATED\u）。

我无法创建此查询。我正在使用h2数据库。

谢谢你的帮助

Answer 1

这个怎么样：

SELECT c.FIRSTNAME, c.LASTNAME, r.RELEASE_ID
FROM CUSTOMERS c
INNER JOIN LICENSES l ON (c.CUSTOMER_ID = l.CUSTOMER_ID)
INNER JOIN RELEASES r ON (r.RELEASE_ID = l.RELEASE_ID)

WHERE r.CREATED_AT = ( SELECT MAX(t.CREATED_AT) FROM RELEASES t
                       WHERE t.RELEASE_ID = r.RELEASE_ID )

由于内部连接，只有发布了版本的客户才会被列出。

Answer 2

这可能是等效的

SELECT CUSTOMERS.FIRSTNAME,
       CUSTOMERS.LASTNAME,
      (SELECT LICENSES.RELEASE_ID
       FROM LICENSES
       WHERE LICENSES.CUSTOMER_ID = CUSTOMERS.CUSTOMER_ID
       ORDER BY LICENSES.CREATED_AT DESC
       LIMIT 1) AS REL_ID
FROM CUSTOMERS
WHERE EXISTS (
    SELECT 1 
    FROM LICENSES
    WHERE LICENSES.CUSTOMER_ID = CUSTOMER.CUSTOMER_ID
)

如果许可证上有外键约束，则不需要加入所有这些表。发布ID和许可证。客户ID。特别是，加入发布表没有意义，因为许可证。RELEASE\u ID列已包含所需信息。

为了完整性起见，如果这是PostgreSQL、Oracle、SQL Server等，则可以执行横向连接，也称为交叉应用

SELECT CUSTOMERS.FIRSTNAME,
       CUSTOMERS.LASTNAME,
       l.RELEASE_ID
FROM CUSTOMERS 
CROSS JOIN LATERAL (
    SELECT *
    FROM LICENSES
    WHERE LICENSES.CUSTOMER_ID = CUSTOMERS.CUSTOMER_ID
    ORDER BY LICENSES.CREATED_AT DESC
    LIMIT 1
) l