SQL每行返回n行值 [英] SQL return n rows per row value

问题描述

问候所有国家的SQL人员.

Greetings SQL people of all nations.

一个简单的问题，希望是一个简单的答案.

Simple question, hopefully a simple answer.

我有一个包含人员信息的Oracle数据库表.列为:

I have an Oracle database table with persons' information. Columns are:

FirstName, LastName, BirthDate, BirthCountry

在此表中，假设我有1500人出生在阿鲁巴(BirthCountry ="Aruba")，678博茨瓦纳人(BirthCountry ="Botswana")，13338加拿大人(BirthCountry ="Canadia").

Let's say in this table I have 1500 persons born in Aruba (BirthCountry = "Aruba"), 678 Botswanans (BirthCountry = "Botswana"), 13338 Canadians (BirthCountry = "Canadia").

我需要写哪个查询来提取每个国家的10条记录的样本批次?十个只要十个就没有关系.

What query would I need to write extract a sample batch of 10 records from each country? It doesn't matter which 10, just as long as there are 10.

此查询将输出30行，每个BirthCountry输出10行.

This one query would output 30 rows, 10 rows from each BirthCountry.

推荐答案

这将从每个国家/地区中选择10个最年轻的人:

This will select 10 youngest people from each country:

SELECT  *
FROM    (
        SELECT  p.*,
                ROW_NUMBER() OVER (PARTITION BY birthCountry ORDER BY birthDate DESC) rn
        FROM    persons p
        )
WHERE   rn <= 10

这篇关于SQL每行返回n行值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！