CakePHP 与 MySQL 查询的问题

Question

我有两个表：

客户：id，名称 文本：id、文本

文本表中的字段文本可以包含客户名称。我想为每个文本找到名称包含在文本字段中的客户。例如：

CLIENTS
id | name
1  | John Smith
2  | Mark Jackson
3  | Ann Boo

TEXTS
id | text
1  | John Smith and Ann Boo are my best friends.
2  | I really don't like Mark Jackson and John Smith.

结果应该是：

RESULT
Text.id | Client.id | Client.name
1       | 1         | John Smith
1       | 3         | Ann Boo
2       | 1         | John Smith
2       | 2         | Mark Jackson

我希望你能帮助我:) 我想知道 find() 函数应该有哪些参数或者只是 mysql 查询

Answer 1

DECLARE @clients TABLE (ID int IDENTITY(1,1), name VARCHAR(20))
INSERT @clients (name)
SELECT 'John Smith' UNION ALL SELECT 'Mark Jackson' UNION ALL SELECT 'Ann Boo'

DECLARE @texts TABLE (ID int IDENTITY(1,1), [text] VARCHAR(200))

INSERT @texts ([text]) VALUES ('John Smith and Ann Boo are my best friends.')
INSERT @texts ([text]) VALUES ('I really don''t like Mark Jackson and John Smith.')

SELECT T.id  [Text.id], C.id [Client.id], C.name [Client.name] 
FROM @CLIENTS C 
JOIN @TEXTS T 
ON T.[text] LIKE '%' + C.NAME + '%'

RESULT

Text.id     Client.id   Client.name
----------- ----------- --------------------
1           1           John Smith
1           3           Ann Boo
2           1           John Smith
2           2           Mark Jackson