HSQLDB对GROUP BY和MIN的使用 [英] Usage of GROUP BY and MIN with HSQLDB

问题描述

我需要执行一个查询来检索每个客户的到期日期最快的商品。

我有以下产品表：

I need to execute a query for retrieving the item with the soonest expire date for each customer.
I have the following product table:

PRODUCT
 |ID|NAME |EXPIRE_DATE|CUSTOMER_ID
  1 |p1   |2013-12-31 |1
  2 |p2   |2014-12-31 |1
  3 |p3   |2013-11-30 |2

，我想获得以下结果：

|ID|EXPIRE_DATE|CUSTOMER_ID
 1 |2013-12-31 |1
 3 |2013-11-30 |2

使用Mysql，我将有一个查询，例如：

With Mysql I would have a query like:

 SELECT ID,min(EXPIRE_DATE),CUSTOMER_ID
 FROM PRODUCT
 GROUP BY CUSTOMER_ID

但是我正在使用HyperSQL，但无法获得此结果。

使用HSQLDB时，出现以下错误： java.sql.SQLSyntaxErrorException：表达式未聚合或GROUP BY列：PRODUCT.ID 。

如果在HyperSQL中修改了查询的最后一行，例如 GROUP BY CUSTOMER_ID，ID ，则我将获得原始表的相同条目。


如何使用HSQLDB为每个客户计算到期日期最快的产品？

But I am using HyperSQL and I am not able to obtain this result.
With HSQLDB I get this error: java.sql.SQLSyntaxErrorException: expression not in aggregate or GROUP BY columns: PRODUCT.ID.
If in HyperSQL I modify the last row of the query like GROUP BY CUSTOMER_ID,ID I obtain the same entries of the original table.

How can I compute the product with the soonest expire date for each customer with HSQLDB??

推荐答案

假设支持子查询，则可以像这样自己连接表：

Assuming sub queries are supported, you can join the table on itself like such:

SELECT P.ID, P.EXPIRE_DATE, P.CUSTOMER_ID
FROM PRODUCT P
   JOIN (
       SELECT CUSTOMER_ID, MIN(EXPIRE_DATE) MIN_EXPIRE_DATE
       FROM PRODUCT
       GROUP BY CUSTOMER_ID
   ) P2 ON P.CUSTOMER_ID = P2.CUSTOMER_ID
        AND P.EXPIRE_DATE = P2.MIN_EXPIRE_DATE

这篇关于HSQLDB对GROUP BY和MIN的使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！