SQL语句优化,求教大家帮忙。

0

 请教一个问题
我有两个表
主表名term ,包含termId,termMsg
从表名alarm,包含alarmId,termId,rvcTime(时间),status
term和alarm一对多关系
请问,我如果想按天统计stauts=0的情况下,term的数量怎么办?我自己写的查询太慢了 
select 
CONVERT(varchar(10), rvcTime, 120 ) as termtime,
(select count(*)
from cfg_term where exists (select d1.termId from cfg_term t1
inner join data_alarm d1 on t1.termId = d1.termId and rvcTime between
('2015-10-11 00:00:00') and ('2015-10-14 23:59:59') where deviceId
in(254,255) and dataId = 1 and CONVERT(varchar(10), d1.rvcTime, 120)
=CONVERT(varchar(10), d.rvcTime, 120) and t1.termid = cfg_term.termId
group by d1.termId)) as num

from cfg_term t inner join data_alarm d on t.termId = d.termId where
rvcTime between('2015-10-11 00:00:00')
and ('2015-10-14 23:59:59') group by CONVERT(varchar(10), rvcTime, 120 )

 
已邀请:
2

- 取是能力,舍是境界 2015-10-20 回答

没有示例数据,无法测试,权当提供个思路吧。
 
select CONVERT(varchar(10), rvcTime, 120 ) as termtime,
sum(case when t.deviceId
in(254,255) and dataId = 1 and CONVERT(varchar(10), d1.rvcTime, 120)
=CONVERT(varchar(10), d.rvcTime, 120) and t1.termid = cfg_term.termId then 1 else 0 end) num
from cfg_term t
inner join data_alarm d on t.termId = d.termId
where rvcTime between('2015-10-11 00:00:00')
and ('2015-10-14 23:59:59')
group by CONVERT(varchar(10), rvcTime, 120 )
2

gogodiy - 天善智能数据库专家、Tableau爱好者 2015-10-21 回答

根据你的要求,代码如下:
SELECT    CONVERT(VARCHAR(10),rvcTime,120) AS StaticDate,
        termId,
        COUNT(*) AS total
FROM    cfg_term WITH(NOLOCK)
WHERE    [status]=0
AND        rvcTime>='2015-10-11'
AND        rvcTime<'2015-10-15'
GROUP BY convert(VARCHAR(10),rvcTime,120),termId
ORDER BY convert(VARCHAR(10),rvcTime,120),termId;
关系数据库的核心是建立在数据集合之上,因此要尽量有集合的概念去处理数据。
0

BILizzy - .net开发,SQLServer 2015-10-21 回答

select CONVERT(varchar(10), rvcTime, 120 ) as termtime,count(termId) as num
from data_alarm 
where status=0 and 
rvcTime between '2015-10-11 00:00:00' and '2015-10-14 23:59:59'
group by CONVERT(varchar(10), rvcTime, 120 )
 
这样不行吗?

要回复问题请先登录注册