英文:
query for all rows and indicate which ones have a certain text in a column
问题
SELECT t1.id, t1.title, CASE WHEN t2.title LIKE '%foo%' THEN 1 ELSE 0 END as containsString
FROM news t1
LEFT JOIN news t2 ON t1.id = t2.id
英文:
I am trying to query a table to get all the rows, but also return which ones match a string search.
For example, here is what the table would look like.
| id | title |
|---|---|
| 1 | foo |
| 2 | bar |
| 3 | foobar |
| 4 | moo |
| 5 | cow |
And I want to query and indicate which ones contain "foo". The query result might look like the below. The 'containsString' could be 0|1 or null|id, just something to indicate that the row contains the string "foo".
| id | title | containsString |
|---|---|---|
| 1 | foo | 1 |
| 2 | bar | 0 |
| 3 | foobar | 1 |
| 4 | moo | 0 |
| 5 | cow | 0 |
The backup strategy is to query for all results and then programmatically Map over it. But I am hoping to do it in SQL.
Here is my first stab at it, but didn't work because it only returned the rows that contained 'foo'
SELECT t1.id, t1.title, t2.id as containsString
FROM `news` t1, `news` t2
where t1.id = t2.id
LEFT JOIN news t2 ON
WHERE t2.title LIKE '%foo%'
答案1
得分: 2
你可以使用 CASE WHEN 来实现：
SELECT id, title,
CASE
WHEN title like '%foo%' THEN 1
ELSE 0
END as containsString
FROM news;
英文:
You can use CASE WHEN like:
SELECT id, title,
CASE
WHEN title like '%foo%' THEN 1
ELSE 0
END as comtainsString
FROM news;
答案2
得分: 0
Chetan方法在这种情况下有效，但另一种方法是首先创建一个包含所有名称中包含foo的表，然后与其他表联合：
select *
from
(select *, 1 as containsFoo
from table
where title like "%foo%") as T1
union
(select *, 0 as containsFoo
from table
where title not like "%foo%")
order by id;
英文:
Chetan method in this case works, but another method is to first make a table with all the have a foo in their name, and union it with the others:
select *
from
(select *, 1 as containsFoo
from table
where title like "%foo%") as T1
union
(select *, 0 as containsFoo
from table
where title not like "%foo%")
order by id;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论