MySQL:连接具有一对多关系的1:1记录

Question

我有一个包含属性和它们的类型的表:

INSERT INTO properties (property_id, year_built, type_id, code)
VALUES 
(1, 2000, 3, 'ABC'),
(2, 2001, 3, 'ABC'),
(3, 2002, 3, 'ABC'),
(4, 2003, 3, 'ABC'),
(5, 2004, 3, 'ABC'),
(6, 2005, 3, 'ABC'),
(7, 2000, 3, 'DEF'),
(8, 2001, 3, 'DEF'),
(9, 2002, 3, 'DEF'),
(10, 2003, 3, 'DEF'),
(11, 2004, 3, 'DEF'),
(12, 2005, 3, 'DEF'),
(13, 2000, 3, 'GHI'),
(14, 2001, 3, 'GHI'),
(15, 2002, 3, 'GHI'),
(16, 2003, 3, 'GHI'),
(17, 2004, 3, 'GHI'),
(18, 2005, 3, 'GHI');

我有第二个表'agents'，其记录数量与属性表相同。

INSERT INTO agents (agent_id, year_built, type_id)
VALUES 
(50, 2000, 3),
(51, 2001, 3),
(52, 2002, 3),
(53, 2003, 3),
(54, 2004, 3),
(55, 2005, 3),
(56, 2000, 3),
(57, 2001, 3),
(58, 2002, 3),
(59, 2003, 3),
(60, 2004, 3),
(61, 2005, 3),
(62, 2000, 3),
(63, 2001, 3),
(64, 2002, 3),
(65, 2003, 3),
(66, 2004, 3),
(67, 2005, 3);

属性表中有一个字段:'agent_id'，应该用相同年份和类型的单个代理填充。例如，以下是运行更新语句后2000年属性表的预期结果:

SELECT (*) FROM properties WHERE year_built = 2000;
property_id  year_built  type_id  code  agent_id
1            2000        3        ABC   50
7            2000        3        DEF   56
13           2000        3        GHI   62

我尝试的每个连接都会返回每个property_id的所有匹配代理记录。例如:

SELECT properties.*, agents.agent_id
FROM   properties
JOIN   agents
USING(year_built, type_id)
WHERE  properties.year_built = 2000;

将给出结果:

property_id  year_built  type_id  code  agent_id
1            2000        3        ABC   50
1            2000        3        ABC   56
1            2000        3        ABC   62
7            2000        3        DEF   50
7            2000        3        DEF   56
7            2000        3        DEF   62
13           2000        3        GHI   50
13           2000        3        GHI   56
13           2000        3        GHI   62

我知道一个简单的连接将返回所有代理记录，但我不确定如何将单个代理记录与我必须使用的字段匹配到单个属性记录。此外，我希望对这些进行排序-以便年份/类型的第一个property_id与相同年份/类型的第一个agent_id匹配。我还应该补充一点，表的字段、键和属性都不能修改。

Answer 1

作为数据从表properties可以均匀agents匹配的数据表,我们可以利用行号添加到每个表的精确匹配。这是在工作台中使用MySQL5.7编写和测试的:

select p.property_id,p.year_built,p.type_id,p.code,agent_id from 
(select property_id,year_built,type_id,code,@row_id:=@row_id+1 as rowid 
from properties,(select @row_id:=0) t ) p
join 
(select agent_id,year_built,type_id,@row_number:=@row_number+1 as rownumber 
from agents,(select @row_number:=0) t ) a
on p.year_built=a.year_built and p.type_id=a.type_id and p.rowid=a.rownumber
where p.year_built=2000
;