mysql select join

mysql join 语法

• mysql join语法分析和性能
• mysql join示例

---

mysql join 示例

select
    from_unixtime(hi.clock,'%Y-%m') as Date,   //from_unixtime(时间戳,格式),时间格式化. 列名为Date
    g.name as Group_Name,  //列名为Group_name
    h.host as Host,        //列名为Host
    round(avg(hi.value_avg),1) as Cpu_Avg_Idle //avg(字段名),求字段平均值;round(小数,保留位),四舍五入函数;列名为Cpu_Avg_Idle
from
    hosts_groups hg  //表明的别名是hg
    join groups g  on g.groupid = hg.groupid //groups 表明别名是g,(join...table...on..),g.groupid = hg.groupid表示groups.groupid字段=hosts_groups.groupid字段
    join items  i  on hg.hostid = i.hostid
    join hosts  h  on h.hostid  = i.hostid
    join trends hi on i.itemid  = hi.itemid  //多个join表示满足所有条件
where
     i.key_='system.cpu.util[,idle]'
     and
     hi.clock >= UNIX_TIMESTAMP('${year}-${month}-01 00:00:00') //UNIX_TIMESTAMP 将时间转换为时间戳
     and
     hi.clock < UNIX_TIMESTAMP('${year}-0${next_month}-01 00:00:00')
group by h.host;