sql—如果两列值相同，则选择行，否则在postgresql 11.0中选择其他组合

xkftehaa 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(339)

我在postgresql中有下表。对于一个试用id的每个id，我想保留那些name\u split=digest\u part\u匹配的行。如果对于试用id的id，name\u split=digest\u part\u matched条件不满足，我希望保留name\u split=normalized\u name所在的行（如果存在）。

id          trial_id    intervention_name                                           name_split                             digest_part_matched  normalized_name code
98262908855 NCT02582996 acetaminophen                                               acetaminophen                          acetaminophen        acetaminophen   A
98262908855 NCT02582996 acetaminophen                                               acetaminophen                          (null)               acetaminophen    B
98262908901 NCT02582996 acetaminophen+caffeine+dihydroergotamine+metoclopramide.    acetaminophen                          dihydroergotamine    acetaminophen+caffeine+dihydroergotamine+metoclopramide.  C
98262908901 NCT02582996 acetaminophen+caffeine+dihydroergotamine+metoclopramide.    acetaminophen                          acetaminophen        acetaminophen   D
98262908901 NCT02582996 acetaminophen+caffeine+dihydroergotamine+metoclopramide.    caffeine                               caffeine acetaminophen+caffeine+dihydroergotamine+metoclopramide.   E1
98262908901 NCT02582996 acetaminophen+caffeine+dihydroergotamine+metoclopramide.    caffeine                               caffeine acetaminophen+caffeine+dihydroergotamine+metoclopramide.   E2
98262908901 NCT02582996 acetaminophen+caffeine+dihydroergotamine+metoclopramide.    dihydroergotamine                      dihydroergotamine    acetaminophen+caffeine+dihydroergotamine+metoclopramide.  F
98262908902 NCT02582996 metoclopramide.+dihydroergotamine                           dihydroergotamine                      metoclopramide.  dihydroergotamine  F

所需输出为：

id          trial_id    intervention_name   name_split  digest_part_matched normalized_name
98262908855 NCT02582996 acetaminophen       acetaminophen   acetaminophen   acetaminophen  A
98262908901 NCT02582996 acetaminophen+caffeine+dihydroergotamine+metoclopramide.    acetaminophen   acetaminophen   acetaminophen   D
98262908901 NCT02582996 acetaminophen+caffeine+dihydroergotamine+metoclopramide.    caffeine    caffeine    acetaminophen+caffeine+dihydroergotamine+metoclopramide.   E1
98262908901 NCT02582996 acetaminophen+caffeine+dihydroergotamine+metoclopramide.    caffeine    caffeine    acetaminophen+caffeine+dihydroergotamine+metoclopramide.   E2
98262908901 NCT02582996 acetaminophen+caffeine+dihydroergotamine+metoclopramide.    dihydroergotamine   dihydroergotamine   acetaminophen+caffeine+dihydroergotamine+metoclopramide.  F
98262908902 NCT02582996 metoclopramide.+dihydroergotamine                           dihydroergotamine                      metoclopramide.  dihydroergotamine  F

我正在尝试以下查询。

select distinct on (id, trial_id) * from tbl
where name_split = digest_part_matched

sql postgresql greatest-n-per-group sql-order-by

来源：https://stackoverflow.com/questions/63210069/select-rows-if-the-two-column-values-are-identical-else-select-another-combinati

1条答案

按热度按时间

zdwk9cvp1#

distinct on 我能做到。我想你想要：

select distinct on (id, trial_id) * 
from tbl
where name_split in (digest_part_matched, normalized_name)
order by 
    id, 
    trial_id,
    (name_split = digest_part_matched) desc

中的第三个表达式 order by 子句优先于 name_split 上的匹配项 digest_part_matched .
您没有指定如何处理 (id, trial_id) 两列都不匹配的元组 name_split . 上面的查询只会消除它们。如果您有另一个条件来决定应保留哪一行，则可以很容易地调整查询以在每一行组中保留一行：

select distinct on (id, trial_id) * 
from tbl
order by 
    id, 
    trial_id,
    (name_split = digest_part_matched) desc,
    (name_split = normalized_name) desc,
    -- another criteria here

赞(0）回复(0）举报 2021-07-24

我来回答

sql—如果两列值相同，则选择行，否则在postgresql 11.0中选择其他组合

1条答案

相关问题

热门标签

最新问答