新手上路,求助SQL循环

[peng008007]

SELECT count(*) FROM tbl_AlarmHistory where year(iResTime)=2006 and month(iResTime)=1 and iAlarmType=0
SELECT count(*) FROM tbl_AlarmHistory where year(iResTime)=2006 and month(iResTime)=1 and iAlarmType=1
SELECT count(*) FROM tbl_AlarmHistory where year(iResTime)=2006 and month(iResTime)=1 and iAlarmType=2
SELECT count(*) FROM tbl_AlarmHistory where year(iResTime)=2006 and month(iResTime)=1 and iAlarmType=3
SELECT count(*) FROM tbl_AlarmHistory where year(iResTime)=2006 and month(iResTime)=2 and iAlarmType=0
SELECT count(*) FROM tbl_AlarmHistory where year(iResTime)=2006 and month(iResTime)=2 and iAlarmType=1
SELECT count(*) FROM tbl_AlarmHistory where year(iResTime)=2006 and month(iResTime)=2 and iAlarmType=2
SELECT count(*) FROM tbl_AlarmHistory where year(iResTime)=2006 and month(iResTime)=2 and iAlarmType=3
SELECT count(*) FROM tbl_AlarmHistory where year(iResTime)=2006 and month(iResTime)=3 and iAlarmType=0
SELECT count(*) FROM tbl_AlarmHistory where year(iResTime)=2006 and month(iResTime)=3 and iAlarmType=1
SELECT count(*) FROM tbl_AlarmHistory where year(iResTime)=2006 and month(iResTime)=3 and iAlarmType=2
SELECT count(*) FROM tbl_AlarmHistory where year(iResTime)=2006 and month(iResTime)=3 and iAlarmType=3
SELECT count(*) FROM tbl_AlarmHistory where year(iResTime)=2006 and month(iResTime)=4 and iAlarmType=0
SELECT count(*) FROM tbl_AlarmHistory where year(iResTime)=2006 and month(iResTime)=4 and iAlarmType=1
SELECT count(*) FROM tbl_AlarmHistory where year(iResTime)=2006 and month(iResTime)=4 and iAlarmType=2
SELECT count(*) FROM tbl_AlarmHistory where year(iResTime)=2006 and month(iResTime)=4 and iAlarmType=3
SELECT count(*) FROM tbl_AlarmHistory where year(iResTime)=2006 and month(iResTime)=5 and iAlarmType=0
SELECT count(*) FROM tbl_AlarmHistory where year(iResTime)=2006 and month(iResTime)=5 and iAlarmType=1
SELECT count(*) FROM tbl_AlarmHistory where year(iResTime)=2006 and month(iResTime)=5 and iAlarmType=2
SELECT count(*) FROM tbl_AlarmHistory where year(iResTime)=2006 and month(iResTime)=5 and iAlarmType=3
SELECT count(*) FROM tbl_AlarmHistory where year(iResTime)=2006 and month(iResTime)=6 and iAlarmType=0
SELECT count(*) FROM tbl_AlarmHistory where year(iResTime)=2006 and month(iResTime)=6 and iAlarmType=1
SELECT count(*) FROM tbl_AlarmHistory where year(iResTime)=2006 and month(iResTime)=6 and iAlarmType=2
SELECT count(*) FROM tbl_AlarmHistory where year(iResTime)=2006 and month(iResTime)=6 and iAlarmType=3
SELECT count(*) FROM tbl_AlarmHistory where year(iResTime)=2006 and month(iResTime)=7 and iAlarmType=0
SELECT count(*) FROM tbl_AlarmHistory where year(iResTime)=2006 and month(iResTime)=7 and iAlarmType=1
SELECT count(*) FROM tbl_AlarmHistory where year(iResTime)=2006 and month(iResTime)=7 and iAlarmType=2
SELECT count(*) FROM tbl_AlarmHistory where year(iResTime)=2006 and month(iResTime)=7 and iAlarmType=3
SELECT count(*) FROM tbl_AlarmHistory where year(iResTime)=2006 and month(iResTime)=8 and iAlarmType=0
SELECT count(*) FROM tbl_AlarmHistory where year(iResTime)=2006 and month(iResTime)=8 and iAlarmType=1
SELECT count(*) FROM tbl_AlarmHistory where year(iResTime)=2006 and month(iResTime)=8 and iAlarmType=2
SELECT count(*) FROM tbl_AlarmHistory where year(iResTime)=2006 and month(iResTime)=8 and iAlarmType=3
SELECT count(*) FROM tbl_AlarmHistory where year(iResTime)=2006 and month(iResTime)=9 and iAlarmType=0
SELECT count(*) FROM tbl_AlarmHistory where year(iResTime)=2006 and month(iResTime)=9 and iAlarmType=1
SELECT count(*) FROM tbl_AlarmHistory where year(iResTime)=2006 and month(iResTime)=9 and iAlarmType=2
SELECT count(*) FROM tbl_AlarmHistory where year(iResTime)=2006 and month(iResTime)=9 and iAlarmType=3
SELECT count(*) FROM tbl_AlarmHistory where year(iResTime)=2006 and month(iResTime)=10 and iAlarmType=0
SELECT count(*) FROM tbl_AlarmHistory where year(iResTime)=2006 and month(iResTime)=10 and iAlarmType=1
SELECT count(*) FROM tbl_AlarmHistory where year(iResTime)=2006 and month(iResTime)=10 and iAlarmType=2
SELECT count(*) FROM tbl_AlarmHistory where year(iResTime)=2006 and month(iResTime)=10 and iAlarmType=3
SELECT count(*) FROM tbl_AlarmHistory where year(iResTime)=2006 and month(iResTime)=11 and iAlarmType=0
SELECT count(*) FROM tbl_AlarmHistory where year(iResTime)=2006 and month(iResTime)=11 and iAlarmType=1
SELECT count(*) FROM tbl_AlarmHistory where year(iResTime)=2006 and month(iResTime)=11 and iAlarmType=2
SELECT count(*) FROM tbl_AlarmHistory where year(iResTime)=2006 and month(iResTime)=11 and iAlarmType=3
SELECT count(*) FROM tbl_AlarmHistory where year(iResTime)=2006 and month(iResTime)=12 and iAlarmType=0
SELECT count(*) FROM tbl_AlarmHistory where year(iResTime)=2006 and month(iResTime)=12 and iAlarmType=1
SELECT count(*) FROM tbl_AlarmHistory where year(iResTime)=2006 and month(iResTime)=12 and iAlarmType=2
SELECT count(*) FROM tbl_AlarmHistory where year(iResTime)=2006 and month(iResTime)=12 and iAlarmType=3
上面是查询2006年1-12月告警类型分别为0.1.2.3的条数,哪位大虾帮忙能写的简单点吗?我用的是死办法,写了48条语句,小弟在此先谢谢了!

[caixia615]

用动态语句执行..

[akuzou]

这不是按年,月，类型分组吗？
试试
SELECT count(*) FROM tbl_AlarmHistory group by year(iResTime),month(iResTime),iAlarmType

[ALLEN625314]

顶
关注

[peng008007]

2楼的，查出来了，但是数据却不知道是几月的什么类型

[akuzou]

SELECT year(iResTime),month(iResTime),iAlarmType,count(*)
FROM tbl_AlarmHistory
group by year(iResTime),month(iResTime),iAlarmType
order by year(iResTime),month(iResTime)

[rookie_one]

上面的有各弟兄说的对阿

SELECT count(*)
FROM tbl_AlarmHistory
group by datepart(yy,iResTime),datepart(mm,iResTime)
and iAlarmType IN (1,2,3)--如果是字符类型的就'1','2','3'

[xiangyu120]

create table #t(id int , Result varchar(10),iResTime datetime,iAlarmType int )
insert into #t(id,result,iResTime,iAlarmType)values (1,'a','2006-01-01',1)
insert into #t(id,result,iResTime,iAlarmType)values (1,'b','2006-02-01',1)
insert into #t(id,result,iResTime,iAlarmType)values (1,'a','2006-03-01',1)
insert into #t(id,result,iResTime,iAlarmType)values (1,'a','2006-04-01',2)
insert into #t(id,result,iResTime,iAlarmType)values (1,'a','2006-05-01',2)
insert into #t(id,result,iResTime,iAlarmType)values (1,'a','2006-06-01',2)
insert into #t(id,result,iResTime,iAlarmType)values (1,'a','2006-07-01',2)
insert into #t(id,result,iResTime,iAlarmType)values (1,'a','2006-08-01',3)
insert into #t(id,result,iResTime,iAlarmType)values (1,'a','2006-01-01',1)
insert into #t(id,result,iResTime,iAlarmType)values (1,'a','2006-08-01',3)
insert into #t(id,result,iResTime,iAlarmType)values (1,'a','2006-08-01',3)
select * from #t
SELECT count(*),month(iResTime),iAlarmType FROM #t where year(iResTime)=2006
group by month(iResTime),iAlarmType
order by month(iResTime)
drop table #t

[Andy-W]

借鉴几位楼上朋友方法，写写：
SELECT year(iResTime),month(iResTime),iAlarmType,SUM(1)
FROM tbl_AlarmHistory
WHERE iAlarmType BETWEEN 0 AND 3
group by year(iResTime),month(iResTime),iAlarmType
order by year(iResTime),month(iResTime)