主从表是一对多的关系。如果高效获取主表信息及从表school 第一条记录。主表 users
+------+--------+--------+
| m_id | m_name | age
+------+--------+--------+
| 1 | 张一 |20
| 2 | 张二 |23
| 3 | 张三 |30
| 4 | 张四 |21
| 5 | 张五 |10
| 6 | 张六 |19
+------+--------+---------+
从表 school
+------+--------+--------+
| s_id | m_id | school
+------+--------+--------+
| 1 | 1 |北京一幼
| 2 | 1 |北京一小
| 3 | 1 |北京一中
| 4 | 2 |上海一小
| 5 | 3 |深圳一小
| 6 | 3 |深圳一中
+------+--------+---------+我通过下面的SQL语句获得目标结果,但当字段数量多或记录数据量大时,性能太差了,求更好效的方法。select u.*,(select `school` from school s where s.m_id=u.m_id LIMIT 1) as school
from users
目标结果:
+------+--------+--------+--------+
| m_id | m_name | age |school |
+------+--------+--------+--------
| 1 | 张一 |20 |北京一幼|
| 2 | 张二 |23 |上海一小|
| 3 | 张三 |30 |深圳一小|
| 4 | 张四 |21 | |
| 5 | 张五 |10 | |
| 6 | 张六 |19 | |
+------+--------+--------+--------+
+------+--------+--------+
| m_id | m_name | age
+------+--------+--------+
| 1 | 张一 |20
| 2 | 张二 |23
| 3 | 张三 |30
| 4 | 张四 |21
| 5 | 张五 |10
| 6 | 张六 |19
+------+--------+---------+
从表 school
+------+--------+--------+
| s_id | m_id | school
+------+--------+--------+
| 1 | 1 |北京一幼
| 2 | 1 |北京一小
| 3 | 1 |北京一中
| 4 | 2 |上海一小
| 5 | 3 |深圳一小
| 6 | 3 |深圳一中
+------+--------+---------+我通过下面的SQL语句获得目标结果,但当字段数量多或记录数据量大时,性能太差了,求更好效的方法。select u.*,(select `school` from school s where s.m_id=u.m_id LIMIT 1) as school
from users
目标结果:
+------+--------+--------+--------+
| m_id | m_name | age |school |
+------+--------+--------+--------
| 1 | 张一 |20 |北京一幼|
| 2 | 张二 |23 |上海一小|
| 3 | 张三 |30 |深圳一小|
| 4 | 张四 |21 | |
| 5 | 张五 |10 | |
| 6 | 张六 |19 | |
+------+--------+--------+--------+
from user a, (select m_id, school from school group by m_id) b
where a.m_id=b.m_id
inner join school a
on a.m_id=b.m_id
where not exists(select 1
from school where a.m_id=m_id and a.id>id)school:m_id id上建立索引