时间戳

首页 > 解决方案 > 使用带有 oracle sql 的 max 函数从列中获取值，包括重复项

sql - 使用带有 oracle sql 的 max 函数从列中获取值，包括重复项

问题描述

我整天都在尝试对底部选择查询的结果使用 max 函数。我想从以下方面获得：

ROOM    NURSES_PER_ROOM
RM2     1
RM1     .1666666666666666666666666666666666666667
RM3     1

至：

ROOM 
RM2      
RM3

其中 RM2 和 RM3 的 NURSES_PER_ROOM 比率最高。

create table Nurse
(PIN varchar2(6) not null primary key,
first_name char(16),
last_name char(20));

create table Room
(number_ varchar2(6) not null primary key,
size_ varchar2(6) not null);

create table Allocation
(nurse varchar2(6) not null primary key,
room varchar2(6) not null,
foreign key (nurse) 
    references Nurse(PIN),
foreign key (room) 
    references Room(number_));

insert into nurse(PIN, first_name, last_name)
values ('NU0011', 'Mary', 'Fritz');
insert into nurse(PIN, first_name, last_name)
values ('NU0012', 'Goth', 'Mortimer');
insert into nurse(PIN, first_name, last_name)
values ('NU0013', 'Rosa', 'Lotta');
insert into nurse(PIN, first_name, last_name)
values ('NU0014', 'Josie', 'Josiah');
insert into nurse(PIN, first_name, last_name)
values ('NU0015', 'Ruth', 'Williams');
insert into nurse(PIN, first_name, last_name)
values ('NU0016', 'Paige', 'Wakeham');

insert into room(number_, size_)
values ('RM1', '6');
insert into room(number_, size_)
values ('RM2', '2');
insert into room(number_, size_)
values ('RM3', '3');

insert into allocation(nurse, room)
values ('NU0011', 'RM1');
insert into allocation(nurse, room)
values ('NU0012', 'RM3');
insert into allocation(nurse, room)
values ('NU0013', 'RM3');
insert into allocation(nurse, room)
values ('NU0014', 'RM3');
insert into allocation(nurse, room)
values ('NU0015', 'RM2');
insert into allocation(nurse, room)
values ('NU0016', 'RM2');

select room, (number_nurses/ro.size_) as nurses_per_room from
    (select room, count(nurse) as number_nurses
    from allocation
    group by room), room ro
where ro.number_ = room

非常感谢任何帮助，谢谢。

编辑：尝试在没有JOIN操作或CASE语句的情况下执行此操作。

标签： sqloracle

解决方案

这是我理解问题的方式：

  • testCTE 计算每个房间的护士人数
  • 然后在子查询中使用它来仅获取护士人数最多的房间
SQL> with test as
  2    (select a.room,
  3            count(*) / r.size_ nurses_per_room
  4     from allocation a join room r on r.number_ = a.room
  5     group by a.room, r.size_
  6    )
  7  select t.room
  8  from test t
  9  where t.nurses_per_room = (Select max(t1.nurses_per_room) from test t1);

ROOM
------
RM2
RM3

SQL>

推荐阅读