[大数据]SQL复杂应用实际使用总结

?1.多个表信息关联查询

涉及表：dev,level,template

SQL语句：

select a.id,b.name nameRoom,a.catagory,a.sound,a.ledid,group_concat(a.id_level) idLevel,group_concat(c.name) title, group_concat(c.info) info,a.template,d.json from dev a left join level b on a.id_level = b.id left join level c on b.parentid =c.id left join template d on a.`template` = d.`template` WHERE a.id = '40:2C:76:6C:2A:1E' group by a.id 

?查询结果：

将上面SQL语句简化：

select * from dev a left join level b on a.id_level = b.id left join level c on b.parentid =c.id left join template d on a.`template` = d.`template` WHERE a.id = '40:2C:76:6C:2A:1E'

查询到是两行记录：

left join level b on a.id_level = b.id?

?left join level c on b.parentid =c.id

?

?left join template d on a.`template` = d.`template`

?然后加上前面的条件：

select a.id,b.name nameRoom,a.catagory,a.sound,a.ledid,group_concat(a.id_level) idLevel,group_concat(c.name) title, group_concat(c.info) info,a.template,d.json from dev a left join level b on a.id_level = b.id left join level c on b.parentid =c.id left join template d on a.`template` = d.`template` WHERE a.id = '40:2C:76:6C:2A:1E'

?group_concat函数把符合条件的结果用逗号分隔多个记录值到查询结果，比如上图中idLevel、title的值。

就得到最初查询结果。group by a.id发现没有实际作用