聚合SQL函数以仅从每个组中获取第一个 [英] Aggregate SQL Function to grab only the first from each group
问题描述
我有2个表-帐户表和用户表。每个帐户可以有多个用户。我有一种情况,我想对这两个表执行单个查询/联接,但我希望所有帐户数据(Account。*)以及仅 first 组用户数据(特别是它们的名称) )。
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屋!