关键词：
【中文标题】跨多个表的 SQL 简单搜索功能【英文标题】：SQL simple search functionality across multiple tables 【发布时间】：2014-03-26 13:48:43 【问题描述】：我使用的是 SQL Server 2012。我需要使用单个文本字段实现搜索功能。
假设我有下表：
--------------------------------------------------------------------------------
FIRSTNAME LASTNAME CITY PROMOYEAR EMPLOYOR
--------------------------------------------------------------------------------
John Doe Boston 2005 Mc Donald
Marc Forestier Bruxelle 2010 Private bank
Céline Durand Paris 1999 Food SA
Simon Forestier Toulouse 2001 Forestier SARL
John Smith New York 1992 Events Org.
Sonia Grappe Toulon 2010 Forestier SARL
--------------------------------------------------------------------------------
行为如下：
必须在整个列中搜索所有单词（空格分隔）。 应为每个单词应用LIKE
如果只搜索一个词，则返回包含该词的所有记录
如果搜索了多个单词，则只返回包含最多不同单词的记录（参见下面的“forestier”示例）
我需要一个查询，不需要 TSQL
我尝试了很多东西，但并不像看起来那么简单。
一些例子：
“约翰”：
-------------------------------------------------------------------------------
FIRSTNAME LASTNAME CITY PROMOYEAR EMPLOYOR
-------------------------------------------------------------------------------
John Doe Boston 2005 Mc Donald
John Smith New York 1992 Events Org.
-------------------------------------------------------------------------------
“约翰·多伊”：
-------------------------------------------------------------------------------
FIRSTNAME LASTNAME CITY PROMOYEAR EMPLOYOR
-------------------------------------------------------------------------------
John Doe Boston 2005 Mc Donald
-------------------------------------------------------------------------------
“森林人”：
-------------------------------------------------------------------------------
FIRSTNAME LASTNAME CITY PROMOYEAR EMPLOYOR
-------------------------------------------------------------------------------
Marc Forestier Bruxelle 2010 Private bank
Simon Forestier Toulouse 2001 Forestier SARL
Sonia Grappe Toulon 2010 Forestier SARL
-------------------------------------------------------------------------------
“2010 xelle”：
FIRSTNAME LASTNAME CITY PROMOYEAR EMPLOYOR
--------------------------------------------------------------------------------
Marc Forestier Bruxelle 2010 Private bank
--------------------------------------------------------------------------------
此示例使用单个表；实际上我的 5 列来自 4 个不同的表，所以实现全文搜索有点复杂！
【问题讨论】：
“我需要一个查询，没有 TSQL”是什么意思？为什么不展示您尝试过的许多事情中的一两个。 听起来像是全文搜索的工作。 technet.microsoft.com/en-us/library/ms142571.aspx @KarlKieninger 抱歉，我的意思是没有动态查询，因为我需要使用表值函数来运行查询。 @KlasLindbäck 你是对的，但我以一个表为例，实际上我的 5 列来自 4 个不同的表，所以实现全文搜索有点复杂！ @Yann39 跨多个表搜索字段是一个非常不同的问题。我认为你的例子过于简单化了。相反，您应该提供一张至少有两张桌子的桌子。我怀疑您可以通过索引视图的全文搜索来完成此操作。 ***.com/questions/8486703/… 【参考方案1】：这似乎是 Sql Full Text Indexing 设计的工作。
AFAIK 全文索引不适用于数字类型，因此您可能需要为任何日期或数字类型添加计算列，例如如果PromoYear 是数字：
ALTER TABLE MyTable
ADD TextPromoYear AS CAST(PromoYear AS NVARCHAR(4))
PERSISTED;
您需要在数据库中设置全文目录：
CREATE FULLTEXT CATALOG CAT_MyCat AS DEFAULT;
假设您有一个名为PK_MyTable 的主键，请创建全文索引：
CREATE FULLTEXT INDEX ON MyTable(FirstName, LastName, City, TextPromoYear,
Employer)
KEY INDEX PK_MyTable;
编辑
这并不像这样简单。通配符 * 只能用作后缀，您还需要聚合列以搜索所有列。
完整条款：
SELECT *
FROM mytable
WHERE CONTAINS(*, 'John and Doe');
对于部分搜索，您可能需要使用普通的 LIKE 混合来测试未知前缀 (*xelle)：
SELECT *
FROM mytable
WHERE CONTAINS(*, '"for*" and 2010') AND SearchableComputedColumn like '%elle%';
Updated SqlFiddle here
【讨论】：
我认为这是解决最初问题的正确方法；）但我正在寻找更快/更简单的解决方案。我过度简化了我的示例，但实际上我的 5 列来自 4 个不同的表，我认为它对于我们的需求来说太复杂了。【参考方案2】：如何添加另一个字段，例如包含来自其他字段的所有信息的文本字段。
FIRSTNAME LASTNAME CITY PROMOYEAR EMPLOYOR SEARCHFIELD
John Doe Boston 2005 Mc Donald John Doe Boston 2005 Mc Donald
并在此字段上进行搜索。它并不优雅，但它可以工作。
在下面添加：
我认为 SQL 语法不能满足您的所有需求，但您可以采用另一种解决方法。创建一个包含您要搜索的所有单词的表格：
create table searchtable
(
rowid int, --key to the id for the row in your table
mothertableName varchar(), -- name of the table if necessary
motherfieldName varchar(), -- name of field
word varchar() -- the actual word to be searchable
)
搜索单词及其出现次数最多的位置：
SELECT * FROM myTable WHERE id IN(
SELECT rid as id, MAX(c) FROM (
SELECT rowid as rid, COUNT(rowid) as c FROM Searchtable WHERE word IN ('john','doe')
)
)
上面的 SQL 肯定行不通，但我希望你能明白我的建议。你应该得到一个搜索词数量最多的行。但是 SQL 中的“IN”运算符要求您动态创建一些 SQL。
正如您所写，您几乎尝试了所有方法，我认为 SQL 无法单独完成。
【讨论】：
我在第一个答案中添加了一些内容。见上文。 你给了我一些想法！所以我终于设法找到了一个解决方案，我将它作为答案发布，因为我认为这是一个更好的解决方案，它涵盖了所有的初始要求。【参考方案3】：这里有一个解决方案。
我已将搜索限制为 6 个单词。 对于每个单词，我检查它是否存在于连接列中。 每次在其中找到一个单词时，我都会通过添加 +1 来获得每条记录的“分数”。 我返回得分最高的记录。
功能：
CREATE FUNCTION [dbo].[SEARCH_SINGLE] (
@langId INT = 4,
@searchString VARCHAR(MAX) = NULL
)
RETURNS TABLE
AS
RETURN
WITH words AS (
SELECT Name as Val, ROW_NUMBER() OVER(ORDER BY Name) as Num FROM [dbo].splitstring(@searchString, ' ')
),
results AS (
SELECT DISTINCT
...
CASE WHEN EXISTS(SELECT 1 FROM words WHERE Num = 1 AND (ISNULL(a.[FIRSTNAME], '') + ' ' + ISNULL(a.[LASTNAME], '') + ' ' + ISNULL(c.[CITY], '') + ' ' + ISNULL(j.[PROMO_YEAR], '') + ' ' + ISNULL(e.[EMPLOYOR], '')) like '%'+Val+'%') THEN 1 ELSE 0 END +
CASE WHEN EXISTS(SELECT 1 FROM words WHERE Num = 2 AND (ISNULL(a.[FIRSTNAME], '') + ' ' + ISNULL(a.[LASTNAME], '') + ' ' + ISNULL(c.[CITY], '') + ' ' + ISNULL(j.[PROMO_YEAR], '') + ' ' + ISNULL(e.[EMPLOYOR], '')) like '%'+Val+'%') THEN 1 ELSE 0 END +
CASE WHEN EXISTS(SELECT 1 FROM words WHERE Num = 3 AND (ISNULL(a.[FIRSTNAME], '') + ' ' + ISNULL(a.[LASTNAME], '') + ' ' + ISNULL(c.[CITY], '') + ' ' + ISNULL(j.[PROMO_YEAR], '') + ' ' + ISNULL(e.[EMPLOYOR], '')) like '%'+Val+'%') THEN 1 ELSE 0 END +
CASE WHEN EXISTS(SELECT 1 FROM words WHERE Num = 4 AND (ISNULL(a.[FIRSTNAME], '') + ' ' + ISNULL(a.[LASTNAME], '') + ' ' + ISNULL(c.[CITY], '') + ' ' + ISNULL(j.[PROMO_YEAR], '') + ' ' + ISNULL(e.[EMPLOYOR], '')) like '%'+Val+'%') THEN 1 ELSE 0 END +
CASE WHEN EXISTS(SELECT 1 FROM words WHERE Num = 5 AND (ISNULL(a.[FIRSTNAME], '') + ' ' + ISNULL(a.[LASTNAME], '') + ' ' + ISNULL(c.[CITY], '') + ' ' + ISNULL(j.[PROMO_YEAR], '') + ' ' + ISNULL(e.[EMPLOYOR], '')) like '%'+Val+'%') THEN 1 ELSE 0 END +
CASE WHEN EXISTS(SELECT 1 FROM words WHERE Num = 6 AND (ISNULL(a.[FIRSTNAME], '') + ' ' + ISNULL(a.[LASTNAME], '') + ' ' + ISNULL(c.[CITY], '') + ' ' + ISNULL(j.[PROMO_YEAR], '') + ' ' + ISNULL(e.[EMPLOYOR], '')) like '%'+Val+'%') THEN 1 ELSE 0 END as Nb
FROM
...
WHERE
...
)
SELECT
...
FROM
results
WHERE
Nb = (SELECT MAX(Nb) FROM results)
AND Nb <> 0
评论？
【讨论】：
跨多个表的 MySQL 全文搜索
】跨多个表的MySQL全文搜索【英文标题】：MySQLfulltextsearchacrossmultipletables【发布时间】：2010-11-1003:52:13【问题描述】：我有一系列表，其中包含我想要全文搜索的数据。我尝试将这些表与UNION组合在一起，但结果会丢失其全文索... 查看详情
跨多个表的列的 SQL 唯一约束
】跨多个表的列的SQL唯一约束【英文标题】：SQLUniqueConstraintwithcolumnsspanningmultipletables【发布时间】：2021-12-1310:39:18【问题描述】：我有一个场景，我需要使用来自2个不同表的列添加唯一约束。我有2张桌子。第一个表是这样的，... 查看详情
跨多个工作表的 VBA 宏
】跨多个工作表的VBA宏【英文标题】：VBAMacroacrossmultipleworksheets【发布时间】：2012-10-2417:55:26【问题描述】：我正在尝试运行一个在多个工作表上执行功能的宏。假设我在工作表4上分配了宏按钮。我已经列出了我希望它逐步执行... 查看详情
在多个表的所有列中搜索的最简单方法？ [复制]
】在多个表的所有列中搜索的最简单方法？[复制]【英文标题】：Easiestwaytosearchinallcolumnsofmultipletables?[duplicate]【发布时间】：2013-05-2804:21:42【问题描述】：我有很多桌子：d_customersd_customersd_newsd_pagesd_productsd_projectsd_sms并且我想创... 查看详情
在多个表的所有列中搜索的最简单方法？ [复制]
】在多个表的所有列中搜索的最简单方法？[复制]【英文标题】：Easiestwaytosearchinallcolumnsofmultipletables?[duplicate]【发布时间】：2013-05-2804:21:42【问题描述】：我有很多桌子：d_customersd_customersd_newsd_pagesd_productsd_projectsd_sms并且我想创... 查看详情
SQLPlus - 跨多个表的计数函数
】SQLPlus-跨多个表的计数函数【英文标题】：SQLPlus-Countfunctionacrossseveraltables【发布时间】：2009-12-0505:32:58【问题描述】：我正在尝试计算我的数据中的骑手人数。我很难弄清楚这一点。我的输出示例如下所示。数据来自许多不同... 查看详情
Rails 跨多个模型搜索
】Rails跨多个模型搜索【英文标题】：RailsSearchAcrossMultipleModels【发布时间】：2011-01-0321:42:04【问题描述】：我有一个问题。我有一个显示视图，它充当仪表板，并从其他模型中引入记录，然后是与之关联的模型。我有一个简单的... 查看详情
跨 3 个表的多个记录的特定字符串的顺序查找并按最新条目显示
】跨3个表的多个记录的特定字符串的顺序查找并按最新条目显示【英文标题】：SequentialLookupofspecificstringacross3tablesformultiplerecordsanddisplaybylatestentry【发布时间】：2020-06-1303:50:32【问题描述】：我有3个表，其中我需要按顺序搜索... 查看详情
跨多个表聚合 SQL 查询
】跨多个表聚合SQL查询【英文标题】：AggregateSQLqueryacrossmultipletables【发布时间】：2016-01-1708:09:06【问题描述】：我使用的是MSAccess2010。该数据库有一组12个相同的设备表，一个用于公司中12个不同部门中的每一个。该表跟踪需要... 查看详情
跨多个列 PostgreSQL/Rails 的慢速通配符搜索 LIKE
】跨多个列PostgreSQL/Rails的慢速通配符搜索LIKE【英文标题】：SlowwildcardsearchLIKEacrossmultiplecolumnsPostgreSQL/Rails【发布时间】：2021-12-0520:44:47【问题描述】：我正在尝试优化这些慢查询（请原谅SQL与RubyonRails混合）：WHEREnameILIKE%<t... 查看详情
多个表的功能
】多个表的功能【英文标题】：Functiontomultipletables【发布时间】：2020-07-1020:06:11【问题描述】：我有这个函数，但我想传递一个表，以便使用相同的函数来完成多个表的工作。例如，我希望这个函数适用于table1和table2。但它目前... 查看详情
用于搜索具有无限个位域的表的 SQL 设计方法
...表：使用该接口的客户端选择在数据库中表示为位字段的多个条件，例如：允许宠物有停车场有甲板现代厨房等等。我们面临的情况是，我们软件的每个新客户都有额外的字段，他 查看详情
跨多个表的 DAX 乘法总和 (sumproduct)
】跨多个表的DAX乘法总和(sumproduct)【英文标题】：DAXsumofmultiplication(sumproduct)acrossmultipletables【发布时间】：2020-02-0720:20:28【问题描述】：我正在努力解决我认为非常愚蠢的问题。我正在尝试从任意日期选择中获取“MTD目标”值。... 查看详情
MySQL ORDER BY 跨多个连接表的列
】MySQLORDERBY跨多个连接表的列【英文标题】：MySQLORDERBYcolumnsacrossmultiplejoinedtables【发布时间】：2017-08-0918:42:16【问题描述】：我有一个由三个表组成的数据库，结构如下：餐厅餐桌：restaurant_id、location_id、评级。示例：1325、77、... 查看详情
跨多个工作表的独特数据整合
】跨多个工作表的独特数据整合【英文标题】：UniquedataconsolidationacrossmultipleWorksheets【发布时间】：2016-02-2201:50:41【问题描述】：我有六个工作表，我想从特定列中获取唯一ID，并将它们合并到1个主文件中（以便进行一些分析和... 查看详情
跨三个表的 t-sql 外连接
】跨三个表的t-sql外连接【英文标题】：t-sqlouterjoinacrossthreetables【发布时间】：2011-05-1318:42:54【问题描述】：我有三张桌子：CREATETABLEperson(idint,namechar(50))CREATETABLEeventtype(idint,descriptionchar(50))CREATETABLEevent(person_idint,eventtype_idint 查看详情
如果在 Excel 中跨多个工作表的语句
】如果在Excel中跨多个工作表的语句【英文标题】：IfStatementacrossmultiplesheetsinExcel【发布时间】：2019-02-2416:24:42【问题描述】：在过去的2个小时里，我一直试图让它工作，但我被卡住了。背景：我正在对我们的站点进行审核，并... 查看详情
如何管理跨多个表的大型数据集？ UNION 与大表？
】如何管理跨多个表的大型数据集？UNION与大表？【英文标题】：HowdoImanagelargedatasetspanningmultipletables?UNIONsvs.BigTables?【发布时间】：2013-05-2215:13:35【问题描述】：我有一个跨越多年的聚合数据集。每一年的数据都存储在一个名为D... 查看详情