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 )
没有找到相关结果
重要提示:提问者不能发表回复,可以通过评论与回答者沟通,沟通后可以通过编辑功能完善问题描述,以便后续其他人能够更容易理解问题.
3 个回复
悟 - 取是能力,舍是境界 2015-10-20 回答
赞同来自: 郑大鹏 、梁勇
gogodiy - 天善智能数据库专家、Tableau爱好者 2015-10-21 回答
赞同来自: 梁勇 、chusheng
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;
关系数据库的核心是建立在数据集合之上,因此要尽量有集合的概念去处理数据。
BILizzy - .net开发,SQLServer 2015-10-21 回答
赞同来自:
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 )
这样不行吗?