聚合 SQL 函数以仅从每个组中获取第一个 [英] Aggregate SQL Function to grab only the first from each group

问题描述

我有 2 个表 - 一个帐户表和一个用户表.每个帐户可以有多个用户.我有一个场景，我想对这两个表执行单个查询/联接，但我想要所有帐户数据 (Account.*) 和只有 第一 组用户数据(特别是他们的名字)).

I have 2 tables - an Account table and a Users table. Each account can have multiple users. I have a scenario where I want to execute a single query/join against these two tables, but I want all the Account data (Account.*) and only the first set of user data (specifically their name).

我不想在我的聚合组上做最小"或最大"，而是想做第一个".但是，显然，TSQL 中没有第一个"聚合函数.

Instead of doing a "min" or "max" on my aggregated group, I wanted to do a "first". But, apparently, there is no "First" aggregate function in TSQL.

有关如何获取此查询的任何建议?显然，很容易得到Account x Users的笛卡尔积:

Any suggestions on how to go about getting this query? Obviously, it is easy to get the cartesian product of Account x Users:

 SELECT User.Name, Account.* FROM Account, User
 WHERE Account.ID = User.Account_ID

但是我怎么可能只根据他们的 User.ID 的顺序从产品中获取第一个用户?

But how might I got about only getting the first user from the product based on the order of their User.ID ?

推荐答案

与其分组，不如这样做...

Rather than grouping, go about it like this...

select
    *

from account a

join (
    select 
        account_id, 
        row_number() over (order by account_id, id) - 
            rank() over (order by account_id) as row_num from user
     ) first on first.account_id = a.id and first.row_num = 0

这篇关于聚合 SQL 函数以仅从每个组中获取第一个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！