我的数据库中有一个表,它有一个名为name_type
的列。这可以为每个用户包含许多3个字符的组合。因此,用户123可以具有"PRF"、"PRI"、"USA"、"BRA"等
name_type
的每个实例都有一行。
所以,我可能有以下。。。
emplid Name_type
123 PRF
123 PRI
我想为每个具有"PRF"名称类型的emplid
显示行。如果emplid
没有"PRF"名称类型,则我希望显示与"PRI"名称类型相关的行。当两者都有时,我希望它默认为"PRF"。如果有帮助,每个emplid
都将有一个"PRI"行。
这是我可以用if语句用6种语言编写的东西,但是,试图用AND和OR语句以及子查询来解决这个问题,简直是一场噩梦。
如何在SQL中执行此操作?
*编辑。。。这是调整了一些名称的当前代码。这不包括PRF部分。
select a.emplid
, b.national_id
, a.name
, a.last_name_srch
, a.first_name_srch
, 'xxxxx' || substr(b.national_id
,6
,4)
, c.birthdate
from qr_names a
, qr_pers_nid b
, qr_person c
where a.emplid = b.emplid
and a.emplid = c.emplid
and b.country = (
select max(b1.country)
from qr_pers_nid b1
where b1.emplid = b.emplid)
and a.name_type ='pri'
and a.effdt = (
select max(a1.effdt)
from qr_names a1
where a1.emplid = a.emplid
and a1.name_type = a.name_type);
您可以搜索其中一个值(使用IN
,或者如果您喜欢使用OR
(,该值将为每个ID提供一到两行;然后使用聚合来保持较低的值:
select emplid, min(name_type) as name_type
from your_table
where name_type in ('PRF', 'PRI')
group by emplid
order by emplid;
这在这里起作用,因为字符串"PRF"将按字母顺序排列在字符串"PRI"之前。
稍微通用一点,你可以明确指定排名来决定保留哪个值:
select emplid,
min(name_type) keep (dense_rank first order by
case name_type when 'PRF' then 1 when 'PRI' then 2 end) as name_type
from your_table
where name_type in ('PRF', 'PRI')
group by emplid
order by emplid;
keep
中的order by
子句使用大小写表达式为每个选项分配一个数值,然后保留其中的第一个。(大小写表达式的计算结果是什么并不重要——我碰巧使用了1和2,但它们可以是"A"one_answers"B",或者任何东西——只要这些值总是按照你需要的顺序排序,那么"PRF"将排在"PRI"之前。(
db<gt;小提琴
这也将扩展到搜索两个以上的值,并且可以很容易地在这些值之间应用所需的任何排序标准——因此,如果你想按优先顺序查找,比如PRF、USA、PRI,那么你只需要按该顺序进行排序。
如果您的表中有其他列也要检索,那么您可以将相同的min() keep ()
机制应用于其他列,但这会变得乏味;因此,您可以使用相同的大小写表达式在子查询中进行排名,然后过滤结果:
select emplid, name_type, other
from (
select emplid, name_type, other, -- whatever columns you need to retrieve
dense_rank() over (partition by emplid order by
case name_type when 'PRF' then 1 when 'PRI' then 2 end) as rnk
from your_table
where name_type in ('PRF', 'PRI')
)
where rnk = 1
order by emplid;
db<gt;小提琴
应用于示例代码,可能看起来像:
select emplid, national_id, name, last_name_srch, first_name_srch,
xxx, birthdate
from (
-- your original query now as a subquery...
select a.emplid
, b.national_id
, a.name
, a.last_name_srch
, a.first_name_srch
, 'xxxxx' || substr(b.national_id
,6
,4) as xxx -- added alias to refer to later
, c.birthdate
-- plus new ranking expression...
, dense_rank() over (partition by emplid order by
case name_type when 'prf' then 1 when 'pri' then 2 end) as rnk
from qr_names a
, qr_pers_nid b
, qr_person c
where a.emplid = b.emplid
and a.emplid = c.emplid
and b.country = (
select max(b1.country)
from qr_pers_nid b1
where b1.emplid = b.emplid)
-- check for both values here
-- and a.name_type ='pri'
and a.name_type in ('pri', 'prf')
and a.effdt = (
select max(a1.effdt)
from qr_names a1
where a1.emplid = a.emplid
and a1.name_type = a.name_type)
-- end of your query
)
where rnk = 1;
(你可能想看看现代的联接语法;你可以通过联接和类似的排名来获得国家和生效日期,而不是子查询——尽管这都是偶然的…(