需要有sql查询，返回除了只有零的数据之外的所有数据

I have the two following tables :

我有以下两个表：

i need to get all rows from data table with the following conditions : - i don't want rows where for one id all values are 0 - i want to have instead of ip address the corresponding hostname retrieved from hosts table

我需要从以下条件获取数据表中的所有行： - 我不希望对于一个id所有值都为0的行 - 我想要而不是ip地址从hosts表中检索到的相应主机名

I have tried the following query:

我尝试了以下查询：

select time, hostname , id , value from data , hosts where hosts.ip_address = data.host group by time,id having avg(value) <> 0;

but it is not what i want. The expected result is : enter image description here

但这不是我想要的。预期结果是：在此输入图像描述

2 个解决方案

#1

Did you try this ?

你试过这个吗？

select d.time, h.hostname , d.id , d.value from data d join hosts h where
h.ip_address = d.host group by d.time,d.id having avg(value) <> 0;

#2

i hope this help you, i test this with your raw data

我希望这对你有帮助，我用原始数据测试一下

select [time] 
   ,hostname
   ,id
   ,value
  from @data as d
 left join @host h on d.host=h.ip_address
 where id not in (select id from @data where value=0  group by id having 
 count(value)>1)