根据值集合和一对多关系筛选一个表

Question

我有Web应用程序，允许通过Name集合过滤Person。这应该返回其Name具有所有已发送值的所有Person(扩展时：对于values中的所有value存在一个包含该value的Name(

模型定义为两个表：Person和Name(以及匹配的 C# 类((为清楚起见，省略了其他列/属性(：

CREATE TABLE [dbo].[Person](
[Id] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED 
(
[Id] ASC
)
)
CREATE TABLE [dbo].[Name](
[Id] [int] IDENTITY(1,1) NOT NULL,
[PersonId] [int] NOT NULL,
[Name] [nvarchar](max) NULL,
CONSTRAINT [PK_Name] PRIMARY KEY CLUSTERED 
(
[Id] ASC
),
CONSTRAINT [FK_Name_Person] FOREIGN KEY([PersonId]) REFERENCES [dbo].[Person]([Id]) ON DELETE CASCADE
)

我编写了简单的 EF Core 查询

// basic
_context.Set<Person>().Where(p => values.All(value => p.Names.Contains(value))); 
// extended
_context.Set<Person>().Where(p => values.All(value => p.Names.Any(n => n.Contains(value)))); 

但它们都没有被转换为 SQL，过滤是在内存中执行的。所以我尝试编写SQL查询(如果可能的话，可能会将其翻译回来(，但我无法找到如何正确执行此操作。

其中一个想法是：

select * from Person
where 
Id in 
(
select distinct PersonId from PersonName where CHARINDEX('value1', Name, 0) > 0
)
and
Id in 
(
select distinct PersonId from PersonName where CHARINDEX('value2', Name, 0) > 0
)

(并在values中为每个value添加新的in条款(

但它不是效率低下吗？

我也 https://blog.marcgravell.com/2014/04/technical-debt-case-study-tags.html 作为研究案例阅读，但首先我想在不改变模型的情况下实现它

-------------------------示例数据-------------------------

Person      Name
------      ---------------------------
| Id |      | Id | PersonId | Name    |
------      ---------------------------
|  1 |      |  1 |        1 | James   |
------      ---------------------------
|  2 |      |  2 |        1 | Jacob   |
------      ---------------------------
|  3 |      |  3 |        2 | Jacob   |
------      ---------------------------
|  4 |        2 | Michael |
---------------------------
|  5 |        3 | Mike    |
---------------------------

// basic (exact matching)
-------------------
| Input | Result  |
-------------------
| Jacob | 1, 2    | Both 1 and 2 has Jacob in Name
-------------------
| Mike  | 3       | Only 3 has Mike in Name
-------------------
// extended (any contains)
-------------------
| Input | Result  |
-------------------
| j, m  | 1, 2    | 1: james (both "j" and "m"), 2: jacob, michael 
-------------------
| m     | 1, 2, 3 | All persons have name containing "m"
-------------------
| mi    | 2, 3    | 2: michael, 3: mike
-------------------

Answer 1

首先是一些易于使用的示例数据：

INSERT dbo.person VALUES (1),(2),(3); -- note: for this I dropped/re-added the identity column
INSERT dbo.[Name] (PersonId, [Name]) VALUES 
(1 ,'James'  ),
(1 ,'Jacob'  ),  
(2 ,'Jacob'  ), 
(2 ,'Michael'),
(3 ,'Mike'   );

接下来，您在这里问两件事，每件事都有"子请求"。这不是一个值得注意的抱怨;让我们一次拿一个。

精确匹配逻辑

(1( 返回我们需要的行：

DECLARE @name VARCHAR(100) = 'Jacob';
SELECT   n.PersonId
FROM     dbo.[Name] AS n
WHERE    n.[Name] = @name;

(2( 将返回值连接成一行：

DECLARE @name VARCHAR(100) = 'Jacob';
SELECT Input = @name, Result = STUFF((
SELECT   CONCAT(',',n.PersonId)
FROM     dbo.[Name] AS n
WHERE    n.[Name] = @name
FOR XML PATH('')),1,1,'')

返回：

Input     Result
--------- ---------
Jacob     1,2

(3( 检索 dbo 中所有名称的正确结果。名字：

SELECT f.Input, f.Result 
FROM dbo.[Name] AS np
CROSS APPLY 
(
SELECT Input = np.[Name], Result = STUFF((
SELECT   CONCAT(',',n.PersonId)
FROM     dbo.[Name] AS n
WHERE    n.[Name] = np.[Name]
FOR XML PATH('')),1,1,'')
) AS f
GROUP BY f.Input, f.Result;

返回：

Input       Result
----------- --------
Jacob       1,2
James       1
Michael     2
Mike        3

包含逻辑

(1(为此，我们需要将结果伪枢轴化为全名，如下所示：

SELECT n.PersonId, FullName = 
(
SELECT   ' '+n2.[Name] 
FROM     dbo.[Name] AS n2
WHERE    n.PersonId = n2.PersonId
ORDER BY n2.id
FOR XML PATH('')
)
FROM   dbo.[Name] AS n
GROUP BY n.PersonId

返回：

PersonId  FullName
--------- ---------------------
1          James Jacob
2          Jacob Michael
3          Mike

(2(要将字符串拆分为值数组，我们需要拆分(标记化(输入并修剪结果。为此，我正在使用STRING_SPLIT如果您使用的是2016年之前的系统，则需要其他东西(Google：delimitsplit8k(：

DECLARE @name VARCHAR(100) = 'j, m';
SELECT      v = LTRIM(s.[value])
FROM        STRING_SPLIT(@name,',') AS s

(3(现在我们将结合此逻辑来拆分输入并在每条记录中搜索匹配项。

请注意，我在输入中添加了一个新值：

DECLARE @name VARCHAR(100) = 'j, m, i';
WITH a AS
(
SELECT n.PersonId, FullName = 
(
SELECT   ' '+n2.[Name] 
FROM     dbo.[Name] AS n2
WHERE    n.PersonId = n2.PersonId
ORDER BY n2.id
FOR XML PATH('')
)
FROM   dbo.[Name] AS n
GROUP BY n.PersonId
)
SELECT      a.PersonId--, a.FullName--, x.x
FROM        STRING_SPLIT(@name,',') AS s
JOIN        a ON CHARINDEX(LTRIM(s.[value]),a.fullname) > 0
GROUP BY    a.PersonId;

返回：

PersonId
-----------
1
2
3

如您所见，它返回了所有人，因为它返回了任何字母匹配的值。我们只想要包含所有字母的行。为此，我们将添加：HAVING COUNT(*) >= LEN(@name)-LEN(REPLACE(@name,',',''))+1

(4( 这里的最终解决方案，输入@name = 'j, m, i'应该只返回 ID 2，对于JacobMichael

DECLARE @name VARCHAR(100) = 'j, m, i';
WITH fullnames AS
(
SELECT n.PersonId, FullName = 
(
SELECT   ' '+n2.[Name] 
FROM     dbo.[Name] AS n2
WHERE    n.PersonId = n2.PersonId
ORDER BY n2.id
FOR XML PATH('')
)
FROM   dbo.[Name] AS n
GROUP BY n.PersonId
)
SELECT      a.PersonId--, a.FullName--, x.x
FROM        STRING_SPLIT(@name,',') AS s
JOIN        fullnames AS a ON CHARINDEX(LTRIM(s.[value]),a.fullname) > 0
GROUP BY    a.PersonId
HAVING      COUNT(*) >= LEN(@name)-LEN(REPLACE(@name,',',''))+1;

Answer 2

基于我创建的Alan Burstein答案(扩展版本(

select N.PersonId from (values ('j'), ('m'), ('i')) AS Input(Value)
join Name as N on CHARINDEX(Input.Value, N.Name) > 0
group by N.PersonId
having count(distinct Input.Value) = 3

不使用串联。在 125kNames 和 125kPersons 上进行比较

This:
SQL Server Execution Times:
CPU time = 672 ms,  elapsed time = 700 ms.
Alan's: (with replaces splitting input to table by "values" and counting of elements by "3")
SQL Server Execution Times:
CPU time = 2547 ms,  elapsed time = 2560 ms.

但仍然 700ms 可能很多

Answer 3

不明白为什么"|J， M |1,2|"涵盖"迈克尔"，但不涵盖"迈克">

DECLARE @PersonName TABLE (name [nvarchar](max), PersonId INT)
INSERT INTO @PersonName(name, PersonId ) VALUES  ('James',1),('Jacob',1),('Jacob',2),('Michael',2),('Mike',3);
SELECT CAST((select CAST(PersonID AS VARCHAR) + ', '
FROM @PersonName as n 
WHERE name like '%Jacob%' 
FOR XML PATH('')
) AS VARCHAR(MAX))
SELECT CAST((select CAST(PersonID AS VARCHAR) + ', '
FROM @PersonName as n 
WHERE name like '%m%' and name like '%j%' 
FOR XML PATH('')
) AS VARCHAR(MAX))
SELECT CAST((select CAST(PersonID AS VARCHAR) + ', '
FROM @PersonName as n 
WHERE name like '%m%' 
FOR XML PATH('')
) AS VARCHAR(MAX))
SELECT CAST((select CAST(PersonID AS VARCHAR) + ', '
FROM @PersonName as n 
WHERE name like '%mi%' 
FOR XML PATH('')
) AS VARCHAR(MAX))