将多个关键字搜索成多个组合成一个(Search multiple keywords into multiple rows combining into one)

将多个关键字搜索成多个组合成一个(Search multiple keywords into multiple rows combining into one)

我有一个数据库，结构是这样的。 http://prnt.sc/ed3uk5

Id是表的主键，Text_Type是Post id（forn键）。 一个长的描述被分成4行，并与forn键（Type）绑定，所以提到的screeshot基本上有4行与一个帖子有关，完整描述是“Kampanien Ferienwohnung in VillammareFerienwohnungenfür6Personen geschmackvoller Apartmentkomplex im Cilento Swimming-Pool，Garten ，Bar，zum Meer约150米“当我们合并4行时。 关于前端的描述也显示为我通过组合4行显示（描述是按类型组合）。

所以现在我想实现具有多个关键字和描述的搜索功能，所有关键字都将返回其“类型”。 例如，如果我搜索“Personen 150”，那么如果帖子的描述同时包含两个关键字，那么它应该返回“类型”。 如果我们使用select type from tblName where description like "%Personen %" and description like "%150 %"那么它将返回null，因为任何行没有两个关键字，但我想通过组合到一起搜索所有行“文本”因此如果两个关键字在完整描述中匹配（按文本组合4行），它将返回“文本”，因此在这种情况下，它应返回1，因为完整描述具有两个关键字。

请帮我摆脱这个。

I have a database and structure is like this. http://prnt.sc/ed3uk5

Id is the primary key of table, Text_Type is Post id(forn key). A long description is splitted into 4 rows and bind with forn key(Type) so basically 4 rows in mentioned screeshot is relates to one post and full description is "KAMPANIEN Ferienwohnung in Villammare Ferienwohnungen für 6 Personen geschmackvoller Apartmentkomplex im Cilento Swimming-Pool, Garten, Bar, zum Meer ca. 150 m" when we combine 4 rows. Description on frontend also showing as i showed by combining 4 rows(description is combining by Type).

So now I want to implement search functionality with multiple keywords and description having all keywords will return their "Type". For Example if i search "Personen 150 " then if a post's description having both keywords then it should return thier "Type". if we use select type from tblName where description like "%Personen %" and description like "%150 %" then it will return null because any of row not having both keywords, but i want to search into all rows once by combining into with "Text" so it will return "Text" if both keywords matches in full description(Combining 4 rows by Text), So in this case it should return 1 because full description having both keywords.

Please help me to get out of this.

最满意答案

你可以像这样使用聚合：

select type from your_table group by type having count(case when description like '%Personen%' then 1 end) > 0 and count(case when description like '%150%' then 1 end) > 0;

怎么运行的：

使用条件聚合，我们检查给定搜索字符串是否存在于给定类型的任何描述中。 如果字符串包含给定的搜索字符串，则CASE将返回1，否则返回null，并且由于我们正在聚合，因此我们使用COUNT来检查一个或多个描述是否包含搜索字符串。

You could use aggregation like this:

select type from your_table group by type having count(case when description like '%Personen%' then 1 end) > 0 and count(case when description like '%150%' then 1 end) > 0;

How it works:

Using conditional aggregation, we're checking if the given search string is present in any of the descriptions for a given type. The CASE will return 1 if the string contained the given search string otherwise it returns null and since we're aggregating, we use COUNT to check if one or more descriptions contained the search string.