SELECT build_code, Count(device_code) 设备个数 FROM 告警信息表 WHERE alarm_time BETWEEN 起始时间 AND 结束时间 GROUP BY build_code
select count(distinct device_code) from tablename where alarm_time between to_date('20110116','yyyymmdd') and to_date('20110117','yyyymmdd')
--大概是这样 SELECT build_code, Count(device_code) 设备个数 FROM 告警信息表 WHERE alarm_time BETWEEN 起始时间 AND 结束时间 GROUP BY build_code
这样统计出来的是 总的次数,就是一个build_code下,所有天线总的次数
distinct 还能这样用啊,受教了
--哦 ,可能有重复的楼宇,需要distinct SELECT build_code, Count( distinct device_code) 设备个数 FROM 告警信息表 WHERE alarm_time BETWEEN 起始时间 AND 结束时间 GROUP BY build_code
with tab as ( select 'A' louyu, 'a' shebei, '20110117' gaojing from dual union all select 'A', 'a', '20110118' from dual union all select 'A', 'a', '20110119' from dual union all select 'A', 'b', '20110117' from dual union all select 'A', 'b', '20110118' from dual union all select 'B', 'a', '20110117' from dual union all select 'B', 'b', '20110117' from dual ) select count(shebei) from tab where to_date(gaojing,'yyyymmdd') between to_date('20110101','yyyymmdd') and to_date('20110119','yyyymmdd') group by louyu ------------------------------------ 5 2
SELECT build_code, Count(device_code) 设备个数
FROM 告警信息表
WHERE alarm_time BETWEEN 起始时间 AND 结束时间
GROUP BY build_code
from tablename
where alarm_time between to_date('20110116','yyyymmdd')
and to_date('20110117','yyyymmdd')
--大概是这样
SELECT build_code, Count(device_code) 设备个数
FROM 告警信息表
WHERE alarm_time BETWEEN 起始时间 AND 结束时间
GROUP BY build_code
这样统计出来的是 总的次数,就是一个build_code下,所有天线总的次数
distinct 还能这样用啊,受教了
SELECT build_code, Count( distinct device_code) 设备个数
FROM 告警信息表
WHERE alarm_time BETWEEN 起始时间 AND 结束时间
GROUP BY build_code
with tab as
(
select 'A' louyu, 'a' shebei, '20110117' gaojing from dual
union all
select 'A', 'a', '20110118' from dual
union all
select 'A', 'a', '20110119' from dual
union all
select 'A', 'b', '20110117' from dual
union all
select 'A', 'b', '20110118' from dual
union all
select 'B', 'a', '20110117' from dual
union all
select 'B', 'b', '20110117' from dual
)
select count(shebei) from tab where to_date(gaojing,'yyyymmdd') between to_date('20110101','yyyymmdd') and to_date('20110119','yyyymmdd') group by louyu
------------------------------------
5
2