火鸟查询-每组返回第一行 [英] Firebird Query- Return first row each group

问题描述

在带有表"Sales"的firebird数据库中，我需要选择所有客户的第一笔交易.参见下面的示例，该示例显示表和查询的期望结果.

In a firebird database with a table "Sales", I need to select the first sale of all customers. See below a sample that show the table and desired result of query.

---------------------------------------
SALES
---------------------------------------
ID  CUSTOMERID  DTHRSALE
1   25          01/04/16 09:32
2   30          02/04/16 11:22      
3   25          05/04/16 08:10
4   31          07/03/16 10:22
5   22          01/02/16 12:30
6   22          10/01/16 08:45

结果:仅基于销售日期进行首次销售.

Result: only first sale, based on sale date.

ID  CUSTOMERID  DTHRSALE
1   25          01/04/16 09:32
2   30          02/04/16 11:22      
4   31          07/03/16 10:22
6   22          10/01/16 08:45

我已经测试了以下代码"在每个列表中选择第一行GROUP BY组吗?"，但是没有用.

I've already tested following code "Select first row in each GROUP BY group?", but it did not work.

推荐答案

在Firebird 2.5中，您可以使用以下查询执行此操作；这是对针对您链接到的问题的接受的答案(针对您的模式和要求)的第二部分的微小修改:

In Firebird 2.5 you can do this with the following query; this is a minor modification of the second part of the accepted answer of the question you linked to tailored to your schema and requirements:

select   x.id,
         x.customerid, 
         x.dthrsale
    from sales x
    join (select customerid,
                 min(dthrsale) as first_sale
            from sales 
        group by customerid) p on p.customerid = x.customerid
                              and p.first_sale = x.dthrsale
order by x.id

order by不是必需的，我只是添加了它以使其按照您的问题中的顺序给出.

The order by is not necessary, I just added it to make it give the order as shown in your question.

对于Firebird 3，您可以使用窗口功能ROW_NUMBER，该功能也在链接的答案中进行了描述.链接的答案错误地指出，第一个解决方案将在Firebird 2.1及更高版本上运行.现在，我已对其进行编辑.

With Firebird 3 you can use the window function ROW_NUMBER which is also described in the linked answer. The linked answer incorrectly said the first solution would work on Firebird 2.1 and higher. I have now edited it.

这篇关于火鸟查询-每组返回第一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！