表1:
boxId boxNo
1 1
2 2
3 3
表2:
id boxId name
1 1 a
2 1 b
3 2 a select a.*,group_concat(b.name)
from 表1 a, 表2 b where a.boxId = b.boxId group by a.boxId结果:
boxId boxNo name
1 1 a,b
2 2 a若要加上不存在关系:
boxId boxNo name
1 1 a,b
2 2 a
3 3
求对应的sql语句?
boxId boxNo
1 1
2 2
3 3
表2:
id boxId name
1 1 a
2 1 b
3 2 a select a.*,group_concat(b.name)
from 表1 a, 表2 b where a.boxId = b.boxId group by a.boxId结果:
boxId boxNo name
1 1 a,b
2 2 a若要加上不存在关系:
boxId boxNo name
1 1 a,b
2 2 a
3 3
求对应的sql语句?
from 表1 a left 表2 b on a.boxId = b.boxId group by a.boxId
from 表1 a left join 表2 b on a.boxId = b.boxId group by a.boxId
left join (select a.*,group_concat(b.name) as name1
from 表1 a, 表2 b where a.boxId = b.boxId group by a.boxId) b
on k.boxId=b.boxId and k.boxNo=b.boxNo
所以boxId为3时,name值为null:SELECT a.*,GROUP_CONCAT(b.name)
FROM 表1 a
LEFT JOIN 表2 b
ON a.boxId = b.boxId
GROUP BY a.boxId