Oracle 查找最接近的匹配单条记录

我想要一个查询，应该根据匹配条件获取一条记录:
表看起来像

create table SERVICES
(
  srvc     VARCHAR2(10) not null,
  location VARCHAR2(10),
  grp      VARCHAR2(10),
  empno    VARCHAR2(10),
  price    NUMBER default 0 not null);

alter table SERVICES
  add constraint uq_services unique (SRVC, LOCATION, GRP, EMPNO);

insert into services(srvc, location, empno, grp, price)
values('srv1', null, null, null, 1500);
insert into services(srvc, location, empno, grp, price)
values('srv1', null, '123', null, 2000);
insert into services(srvc, location, empno, grp, price)
values('srv1', null, '123', 'A', 2500);
commit;

我想根据以下条件查询此表:
srvc = 'srv1' 和location = 'home' 和empno = '123 '和grp = 'A' ----> 它应该返回价格2500

srvc = 'srv1' 和location = 'home' 和empno = '123 '和grp = 'B' ----> 它应该返回价格2000

srvc = 'srv1' 和location = 'office' 和empno = '456 '和grp = 'B' ----> 它应该返回价格1500

我想匹配列与指定值匹配或列为null的行。

请帮助我建立查询。
问候

所以你想返回最符合条件的行？

如果是这样，请进行查询，检查每个列是否与bind变量匹配或为null。然后对每列上的匹配行进行排序。这将把空值行放在最后。并获得以下第一个:

create table SERVICES
(
  srvc     VARCHAR2(10) not null,
  location VARCHAR2(10),
  grp      VARCHAR2(10),
  empno    VARCHAR2(10),
  price    NUMBER default 0 not null);

alter table SERVICES
  add constraint uq_services unique (SRVC, LOCATION, GRP, EMPNO);

insert into services(srvc, location, empno, grp, price)
values('srv1', null, null, null, 1500);
insert into services(srvc, location, empno, grp, price)
values('srv1', null, '123', null, 2000);
insert into services(srvc, location, empno, grp, price)
values('srv1', null, '123', 'A', 2500);
commit;

var srv varchar2(10);
var loc varchar2(10);
var emp number;
var g   varchar2(1);

exec :srv := 'srv1';
exec :loc := 'home';
exec :emp := '123';
exec :g   := 'A';

select * from (
  select * from services
  where  ( srvc = :srv or srvc is null )
  and    ( location = :loc or location is null )
  and    ( empno = :emp or empno is null )
  and    ( grp = :g or grp is null )
  order  by srvc, location, grp, empno
)
where  rownum = 1;

SRVC   LOCATION   GRP   EMPNO   PRICE   
srv1        A     123        2500 

exec :srv := 'srv1';
exec :loc := 'home';
exec :emp := '123';
exec :g   := 'B';

select * from (
  select * from services
  where  ( srvc = :srv or srvc is null )
  and    ( location = :loc or location is null )
  and    ( empno = :emp or empno is null )
  and    ( grp = :g or grp is null )
  order  by srvc, location, grp, empno
)
where  rownum = 1;

SRVC   LOCATION   GRP      EMPNO   PRICE   
srv1           123        2000 

exec :srv := 'srv1';
exec :loc := 'office';
exec :emp := '456';
exec :g   := 'B';

select * from (
  select * from services
  where  ( srvc = :srv or srvc is null )
  and    ( location = :loc or location is null )
  and    ( empno = :emp or empno is null )
  and    ( grp = :g or grp is null )
  order  by srvc, location, grp, empno
)
where  rownum = 1;

SRVC   LOCATION   GRP      EMPNO    PRICE   
srv1                 1500