问题描述
我在用一个脚本选择/插入多个最大值时遇到麻烦.下表是为了代表值与我要使用的表的组合而组成的.
I am having trouble selecting/inserting multiple max values with one script. The following table is made up to represent the combination of values with the tables I am trying to work with.
该脚本必须是一个插入语句，才能将返回的值插入到另一个表中.
The script needs to be an insert statement to insert the returned values into a second table.
CREATE TABLE family
( state VARCHAR2(2 BYTE),
Birth date,
Married date,
Shirt size number(10 )
Shoe size number(10),
Hair VARCHAR2(80 BYTE),
carname VARCHAR2(24 BYTE),
CATname VARCHAR2(24 BYTE),
Hometown VARCHAR2(40 BYTE),
Alive CHAR(1 BYTE),
job_CATEGORY_1 VARCHAR2(40 BYTE),
job_CATEGORY_2 VARCHAR2(40 BYTE)
)
此表有1500多行.我需要从记录中选择所有值最老的出生日期，最老的结婚日期，最大的鞋子尺寸和最大的衬衫尺寸"的组合.其余值必须在脚本中.
This table has over 1500 rows. I need to select all values from the records with theThe combination of "Oldest birth date, oldest married date, largest shoe size and largest shirt size". The remaining values need to be in the script.
当我选择年龄最大的人时，我会得到三个记录，分别显示结婚日期，衬衫和鞋子的尺码.我需要选择max(出生)，max(已婚)，max(衬衫尺寸)和max(鞋子尺寸)的组合，然后包括其余的列.
When I select people with the oldest birth date, I get three records with various married dates, and various shirt and shoe size. My need is to select the combination of max(birth), max(married), max(shirt size) and max(shoe size), then include the remaining columns.
任何帮助都将不胜感激.
Any and all help will be greatly appreciated.
推荐答案
您可以使用分析函数:
select * from
(
select f.*,
rank() over (partition by state
order by birth) as birth_rank,
rank() over (partition by state, birth
order by married) as married_rank,
rank() over (partition by state, birth, married
order by shoe_size desc) as shoe_rank,
rank() over (partition by state, birth, married, shoe_size
order by shirt_size desc) as shirt_rank
from family f
)
where birth_rank = 1
and married_rank = 1
and shoe_rank = 1
and shirt_rank = 1;
但是，不要在外部查询中选择select *，而只选择您感兴趣的字段-您可能不想看到排名字段.
But instead of select * in the outer query, select only the fields you're interested in - you probably don't want to see the rank fields.
每个_rank伪列都是排名与前面一组匹配的列中的值.因此，对于birth_rank来说，它仅查看状态，并且在每个状态内将有一个或多个记录被排名为1.对于married_rank，它的查询范围进一步向下，因此在状态和出生日期，将有一个结婚日期排名为1.依此类推.然后，外部查询仅选择最高等级，并且通常每个状态只有一条记录. (排名允许联系，因此如果两个或多个记录满足所有条件，您可能需要一种打破联系的方法.)
Each _rank psuedocolumn is ranking the values in the matching column with the group of the preceding ones. So for birth_rank it's only looking at the state, and within each state there will be one or more records that get ranked as 1. For married_rank it's looking further down, so within each combination of state and birth date there will be one married date that is ranked as 1. And so on. The outer query then picks only the highest ranks, and there will usually only be one record per state. (Rank allows ties, so you may need a way to break ties if two or more records meet all the criteria).
出生和已婚者的等级升序排列，因此最早的数据排名第一；表演和衬衫的大小顺序是从大到小排列的.
The birth and married ranks are ordered ascending so the earliest data is ranked first; the show and shirt size ranks are ordered descending to the largest is ranked first.
SQL Fiddle演示，显示分配给所有值的所有值的等级在第一个查询中排成一行，然后在第二个查询中应用过滤器，因此只有一行(每个状态，尽管我只包括了一行).这就是为什么样本数据有用的原因.
SQL Fiddle demo, showing the ranks assigned to all the values for all rows in the first query, and then the filter applied in the second query so there is only one row (per state, though I've only included one). This is why sample data is useful.
这篇关于Oracle 11g使用一个脚本选择/插入多个最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！