mysql select join

mysql join 语法


mysql join 示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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;