mysql - Multi conditional if statement in SQL query
问题描述
I have a long list of names formatted like below. A person may have a few different representations of their name in the table (e.g. 'Smith, Tom' vs 'Tom Smith').
A query will come through using a like search (e.g. "SELECT * FROM table WHERE person_name LIKE "%smith%"
).
If a person has the boolean field is_display
checked as true I want only the row with is_display
set to true, if the person ID does not have an is_display
checked off, I then want to fall back on the is_preferred
field.
So ideally, a search for "smith" would only return rows 1 and 4.
ID person_id person_name is_preferred is_display
1 123456 Tom Smith 0 1
2 223456 Madonna 1 1
3 123456 Smith, Tom 1 0
4 423456 Smith, Frank 1 0
5 423456 Smith, Frank R. 0 0
I've looked into SQL if / else and CASE statements, but haven't found a command that works for this need yet.
Right the closest I've gotten is:
SELECT *
FROM artist_aliases
WHERE ( (alias LIKE '%smith%' AND display=1) OR
(alias LIKE '%smith%' AND preferred=1) )
Then I massage the results in code after SQL. But I imagine there's a much more elegant way to do this only in SQL.
解决方案
You seem to want a prioritization by person.
In MySQL 8+, this would look like:
select aa.*
from (select aa.*,
row_number() over (partition by person_id order by display desc, preferred desc) as seqnum
from artist_aliases aa
where alias like '%smith%'
) aa
where seqnum = 1;
In earlier versions, you can use a correlated subquery:
select aa.*
from artist_aliases aa
where aa.alias like '%smith%' and
aa.id = (select aa2.id
from artist_aliases aa2
where aa2.alias like '%smith%' and
aa2.person_id = aa.person_id
order by aa2.display desc, aa2.preferred desc
limit 1
);
推荐阅读
- jquery - 如何使用自定义 div 元素在 Jquery 中显示工具提示
- drools - Drools:如何比较两个列表中的属性的最佳实践
- c++ - 如何阻止函数的内存泄漏
- oop - API 或包装器不是应用程序的边界吗?
- python - 如何使用请求库自动化浏览器(不是硒导致性能)
- python-3.x - 输入必须在通过前至少 5 秒开启
- javascript - 打字稿道具可以是字符串数组或具有特定键的对象数组:值
- javascript - 单击(图像/按钮)将数据存储在 ... 以实时呈现另一个 html 模板
- http - EIdOSSLUnderlyingCryptoError - SSL3_GET_RECORD:错误的版本号(TLS 1.2)
- coq - 逻辑:evenb_double_conv