有三张表 hosts,items和trends_uint
本人现在要做的是从hosts里提取出hostid，然后通过这个hostid取items里面提取出key_为“vm.memory.size[available]”和“vm.memory.size[total]”的两个itemid，然后再通过这两个itemid去trends_uint里查询value_avg的值，不过要将这个值取出最近7天的进行平均，然后用”vm.memory.size[total]”的值-”vm.memory.size[available]“的值，再除以”vm.memory.size[available]“的值（算百分比）
本人现在能够实现查出这两个值的最近7天的平均值，现在想将计算也整合进sql命令里，不知道怎么写了。
求帮助！
本人构想的代码如下：
Select host,((round(avg(trends_uint.value_avg)/1048576) from items ,trends_uint where hostid=”10084″ and items.key_=”vm.memory.size[total]” and items.itemid=c.itemid and clock >= date_format(date_sub(date_sub(now(), INTERVAL WEEKDAY(NOW()) DAY), INTERVAL 1 WEEK),”Y-%m-%d”))-(round(avg(trends_uint.value_avg)/1048576) from items,trends_uint where hostid=”10084″ and b.key_=”vm.memory.size[available]” and items.itemid=trends_uint.itemid and clock >= date_format(date_sub(date_sub(now(), INTERVAL WEEKDAY(NOW()) DAY), INTERVAL 1 WEEK),”Y-%m-%d”)))/(round(avg(trends_uint.value_avg)/1048576) from items ,trends_uint where hostid=”10084″ and b.key_=”vm.memory.size[total]” and items.itemid=trends_uint.itemid and clock >= date_format(date_sub(date_sub(now(), INTERVAL WEEKDAY(NOW()) DAY), INTERVAL 1 WEEK),”Y-%m-%d”)) as MEM_USAGE from hosts where hostid=10084;
但是报错：
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near “from items ,trends_uint where hostid=”10084″ and items.key_=”vm.memory.size[tota” at line 1
不知道本人的思路能否正确？应该怎么改才可以？
新手求指导了
本人现在要做的是从hosts里提取出hostid，然后通过这个hostid取items里面提取出key_为“vm.memory.size[available]”和“vm.memory.size[total]”的两个itemid，然后再通过这两个itemid去trends_uint里查询value_avg的值，不过要将这个值取出最近7天的进行平均，然后用”vm.memory.size[total]”的值-”vm.memory.size[available]“的值，再除以”vm.memory.size[available]“的值（算百分比）
本人现在能够实现查出这两个值的最近7天的平均值，现在想将计算也整合进sql命令里，不知道怎么写了。
求帮助！
本人构想的代码如下：
Select host,((round(avg(trends_uint.value_avg)/1048576) from items ,trends_uint where hostid=”10084″ and items.key_=”vm.memory.size[total]” and items.itemid=c.itemid and clock >= date_format(date_sub(date_sub(now(), INTERVAL WEEKDAY(NOW()) DAY), INTERVAL 1 WEEK),”Y-%m-%d”))-(round(avg(trends_uint.value_avg)/1048576) from items,trends_uint where hostid=”10084″ and b.key_=”vm.memory.size[available]” and items.itemid=trends_uint.itemid and clock >= date_format(date_sub(date_sub(now(), INTERVAL WEEKDAY(NOW()) DAY), INTERVAL 1 WEEK),”Y-%m-%d”)))/(round(avg(trends_uint.value_avg)/1048576) from items ,trends_uint where hostid=”10084″ and b.key_=”vm.memory.size[total]” and items.itemid=trends_uint.itemid and clock >= date_format(date_sub(date_sub(now(), INTERVAL WEEKDAY(NOW()) DAY), INTERVAL 1 WEEK),”Y-%m-%d”)) as MEM_USAGE from hosts where hostid=10084;
但是报错：
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near “from items ,trends_uint where hostid=”10084″ and items.key_=”vm.memory.size[tota” at line 1
不知道本人的思路能否正确？应该怎么改才可以？
新手求指导了
解决方案
5
Select host,
((
(
select round(avg(trends_uint.value_avg)/1048576) from items ,trends_uint where hostid=”10084″ and items.key_=”vm.memory.size[total]” and items.itemid=c.itemid and clock >= date_format(date_sub(date_sub(now(), INTERVAL WEEKDAY(NOW()) DAY), INTERVAL 1 WEEK),”Y-%m-%d”)
)
–
(
select round(avg(trends_uint.value_avg)/1048576) from items,trends_uint where hostid=”10084″ and items.key_=”vm.memory.size[available]” and items.itemid=trends_uint.itemid and clock >= date_format(date_sub(date_sub(now(), INTERVAL WEEKDAY(NOW()) DAY), INTERVAL 1 WEEK),”Y-%m-%d”)
)
)
/
(
select round(avg(trends_uint.value_avg)/1048576) from items ,trends_uint where hostid=”10084″ and items.key_=”vm.memory.size[total]” and items.itemid=trends_uint.itemid and clock >= date_format(date_sub(date_sub(now(), INTERVAL WEEKDAY(NOW()) DAY), INTERVAL 1 WEEK),”Y-%m-%d”)
)) as MEM_USAGE
from hosts where hostid=10084;
1、你的排版要好看点
2、你的b.key_本人没有找到，不过根据你的第一个语句，应该是items.key_。
你先试下可不可以，假如结果是正确的，还可以修改的简单点的。
((
(
select round(avg(trends_uint.value_avg)/1048576) from items ,trends_uint where hostid=”10084″ and items.key_=”vm.memory.size[total]” and items.itemid=c.itemid and clock >= date_format(date_sub(date_sub(now(), INTERVAL WEEKDAY(NOW()) DAY), INTERVAL 1 WEEK),”Y-%m-%d”)
)
–
(
select round(avg(trends_uint.value_avg)/1048576) from items,trends_uint where hostid=”10084″ and items.key_=”vm.memory.size[available]” and items.itemid=trends_uint.itemid and clock >= date_format(date_sub(date_sub(now(), INTERVAL WEEKDAY(NOW()) DAY), INTERVAL 1 WEEK),”Y-%m-%d”)
)
)
/
(
select round(avg(trends_uint.value_avg)/1048576) from items ,trends_uint where hostid=”10084″ and items.key_=”vm.memory.size[total]” and items.itemid=trends_uint.itemid and clock >= date_format(date_sub(date_sub(now(), INTERVAL WEEKDAY(NOW()) DAY), INTERVAL 1 WEEK),”Y-%m-%d”)
)) as MEM_USAGE
from hosts where hostid=10084;
1、你的排版要好看点
2、你的b.key_本人没有找到，不过根据你的第一个语句，应该是items.key_。
你先试下可不可以，假如结果是正确的，还可以修改的简单点的。
35
-- 建表
CREATE TABLE `hosts` (
`hostid` INT(11) DEFAULT NULL,
`host` VARCHAR(50) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `items` (
`key_` VARCHAR(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`hostid` INT(11) DEFAULT NULL,
`itemid` INT(11) DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `trend_uint` (
`itemid` INT(11) DEFAULT NULL,
`value_avg` INT(11) DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
-- 插入数据
INSERT INTO `hosts`(`hostid`, `host`) VALUES ("10084", "123456789ABC");
INSERT INTO `items`(`key_`, `hostid`, `itemid`) VALUES ("vm.memory.size[available]", "10084", "105");
INSERT INTO `items`(`key_`, `hostid`, `itemid`) VALUES ("vm.memory.size[total]", "10084", "106");
INSERT INTO `trend_uint`(`itemid`, `value_avg`) VALUES ("105", "3000");
INSERT INTO `trend_uint`(`itemid`, `value_avg`) VALUES ("106", "8000");
-- 查询
SELECT T1.host, (T2.value_avg - T1.value_avg)*1.00/T2.value_avg AS valavg FROM
(
SELECT c.host, a.key_, a.itemid, a.hostid, b.value_avg FROM `items` AS a
INNER JOIN trend_uint AS b
ON a.itemid = b.itemid
INNER JOIN `hosts` AS c
ON a.hostid = c.hostid
WHERE a.key_ = "vm.memory.size[available]" AND a.hostid = "10084"
) AS T1
INNER JOIN
(
SELECT a.key_, a.itemid, a.hostid, b.value_avg FROM `items` AS a
INNER JOIN trend_uint AS b
ON a.itemid = b.itemid
INNER JOIN `hosts` AS c
ON a.hostid = c.hostid
WHERE a.key_ = "vm.memory.size[total]" AND a.hostid = "10084"
) AS T2
ON T1.hostid = T2.hostid
-- 结果
HOST valavg
123456789ABC 0.625000
CodeBye 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权 , 转载请注明急！求帮助多表联查的结果进行计算该怎么写？！
