mysql - 根据 where 子句中匹配列的优先级从数据库中选择数据

Question

您好，我面临一个问题，我有一种情况，我需要根据 where 子句中匹配列的优先级从数据库中选择数据

例如。我有一张 table

Employee: FirstName, LastName, City, State.

我想根据条件搜索员工的记录。但情况是，首先我需要获取所有四个条件(列)匹配的记录，然后是四个条件中的三个条件(列)匹配的所有记录，然后是四个条件中的两个条件(列)匹配的所有记录匹配等等..

为此，我正在根据情况创建动态查询，但如果搜索条件扩展得比我需要在查询中放置的条件多，那么管理动态查询将很困难。那么有没有其他方法可以处理这个问题呢？

非常感谢。

Answer 1

这已经在 SQL Server 上进行了测试，但如果它不能立即运行，应该可以让您了解 MySQL；

测试数据

IF OBJECT_ID('tempdb..#TestData') IS NOT NULL DROP TABLE #TestData
GO
CREATE TABLE #TestData (FirstName varchar(20), LastName varchar(20), City varchar(20), State varchar(20))
INSERT INTO #TestData (FirstName, LastName, City, State)
VALUES
('Jon','Snow','Winterfell','Westeros')
,('Jaime','Lannister','Riverrun','Westeros')
,('Margaery','Tyrell','Kings Landing','Westeros')
,('Cersei','Lannister','Kings Landing','Westeros')
,('Daenerys','Targaryen','Mereen','Essos')
,('Davos','Seaworth','Winterfell','Westeros')
,('Theon','Greyjoy','Mereen','Essos')
,('Tyrion','Lannister','Mereen','Essos')
,('Petyr','Baelish','Winterfell','Westeros')

将搜索条件设置为变量进行查询。 case 将检查我们有多少匹配项并按该字段排序。

DECLARE @Variable1 varchar(20); SET @Variable1 = 'Jon'
DECLARE @Variable2 varchar(20); SET @Variable2 = 'Snow'
DECLARE @Variable3 varchar(20); SET @Variable3 = 'Winterfell'
DECLARE @Variable4 varchar(20); SET @Variable4 = 'Westeros'


SELECT 
FirstName
,LastName
,City
,State
,CASE WHEN FirstName = @Variable1 THEN 1 ELSE 0 END +
 CASE WHEN LastName = @Variable2 THEN 1 ELSE 0 END +
 CASE WHEN City = @Variable3 THEN 1 ELSE 0 END +
 CASE WHEN State = @Variable4 THEN 1 ELSE 0 END Matches

FROM #TestData
ORDER BY (
            CASE WHEN FirstName = @Variable1 THEN 1 ELSE 0 END +
            CASE WHEN LastName = @Variable2 THEN 1 ELSE 0 END +
            CASE WHEN City = @Variable3 THEN 1 ELSE 0 END +
            CASE WHEN State = @Variable4 THEN 1 ELSE 0 END
        ) DESC

结果为;

FirstName   LastName    City            State       Matches
Jon         Snow        Winterfell      Westeros    4
Davos       Seaworth    Winterfell      Westeros    2
Petyr       Baelish     Winterfell      Westeros    2
Jaime       Lannister   Riverrun        Westeros    1
Margaery    Tyrell      Kings Landing   Westeros    1
Cersei      Lannister   Kings Landing   Westeros    1
Daenerys    Targaryen   Mereen          Essos       0
Theon       Greyjoy     Mereen          Essos       0
Tyrion      Lannister   Mereen          Essos       0