SELECT @ptplace2=ptplace,@bjPTSTATE=COUNT(PTSTATE),@BJTIME1=SUM(datediff(s,BEGINTIME,ENDTIME))
FROM KJ95CXDT
WHERE PTSTATE=1 AND KJID=@KJID AND BEGINTIME> =@TIMESTRAT AND ENDTIME <=@TIMEEND AND PTPROP='1' AND ptplace=@ptplace
GROUP BY PTPLACE
FROM KJ95CXDT
WHERE PTSTATE=1 AND KJID=@KJID AND BEGINTIME> =@TIMESTRAT AND ENDTIME <=@TIMEEND AND PTPROP='1' AND ptplace=@ptplace
GROUP BY PTPLACE
select *
FROM KJ95CXDT
WHERE PTSTATE=1 AND KJID=@KJID AND BEGINTIME> =@TIMESTRAT AND ENDTIME <=@TIMEEND AND PTPROP='1' AND ptplace=@ptplace
GROUP BY PTPLACE
SELECT @ptplace2=ptplace,@bjPTSTATE=COUNT(PTSTATE),@BJTIME1=SUM(datediff(s,BEGINTIME,ENDTIME))
FROM KJ95CXDT
WHERE PTSTATE=1 AND KJID=@KJID AND BEGINTIME> =@TIMESTRAT AND ENDTIME <=@TIMEEND AND PTPROP='1' AND ptplace=@ptplace
GROUP BY PTPLACE
-------------------------------------
已经存在条件ptplace=@ptplace了,楼主觉得这时候group by ptplace还有意义吗?
潇洒老乌龟(爱新觉罗.毓华)
等级:z这样不是,聚合以后是一条记录,没问题吧
改为:SUM(datediff(D,BEGINTIME,ENDTIME))
改为:SUM(datediff(D,BEGINTIME,ENDTIME))
--
5楼不算,看错了,不好意思.
我是想取SELECT @ptplace2=ptplace,这样写没问题的,至少不会报错把
GROUP BY PTPLACE 没有意义,但这个没错.没关系.如果:select *
FROM KJ95CXDT
WHERE PTSTATE=1 AND KJID=@KJID AND BEGINTIME> =@TIMESTRAT AND ENDTIME <=@TIMEEND AND PTPROP='1' AND ptplace=@ptplace
或
select *
FROM KJ95CXDT
WHERE PTSTATE=1 AND KJID=@KJID AND BEGINTIME> =@TIMESTRAT AND ENDTIME <=@TIMEEND AND PTPROP='1' AND ptplace=@ptplace
GROUP BY PTPLACE 是一条记录,就不应该有错.
FROM KJ95CXDT
WHERE PTSTATE=1 AND KJID=@KJID AND BEGINTIME> =@TIMESTRAT AND ENDTIME <=@TIMEEND AND PTPROP='1' AND ptplace=@ptplace
GROUP BY PTPLACE
FROM KJ95CXDT
WHERE PTSTATE=1 AND KJID=@KJID AND BEGINTIME> =@TIMESTRAT AND ENDTIME <=@TIMEEND AND PTPROP='1' AND ptplace=@ptplace
GROUP BY PTPLACE
GO
SET ANSI_NULLS ON
GO/****** 对象: 存储过程 dbo.rep_daqjcrb 脚本日期: 2008-3-5 9:43:13 ******//****** 对象: 存储过程 dbo.rep_daqjcrb 脚本日期: 2008-3-4 16:26:56 ******//****** 对象: 存储过程 dbo.rep_daqjcrb 脚本日期: 2008-3-4 9:36:41 ******//****** 对象: 存储过程 dbo.rep_daqjcrb 脚本日期: 2008-3-3 17:41:23 ******//****** 对象: 存储过程 dbo.rep_daqjcrb 脚本日期: 2008-3-3 16:50:50 ******/ALTER procedure rep_daqjcrb
--漳村煤矿安全监控日报表
(
@kjid int,
@year int,
@month int,
@day int
)
as
SET NOCOUNT ON
create table #temp_daqjcrb(prow int ,tptid nvarchar(100),t1 nvarchar(100),t2 decimal(18,2),t3 decimal(18,2),t4 nvarchar(100),t5 int,t6 nvarchar(100),t7 int,t8 nvarchar(100),t9 int,tptid1 nvarchar(100),t10 nvarchar(100),t11 decimal(18,2),t12 decimal(18,2),t13 nvarchar(100),t14 int,t15 nvarchar(100),t16 int,t17 nvarchar(100),t18 int,t19 nvarchar(100),t20 decimal(18,2),t21 decimal(18,2),t22 nvarchar(100),t23 nvarchar(100),t24 nvarchar(100),t25 nvarchar(100),t26 int,t27 nvarchar(100),t28 nvarchar(100))
create table #temp_co(prow int IDENTITY(1,1) PRIMARY KEY CLUSTERED,t1 nvarchar(100),t2 decimal(18,2),t3 decimal(18,2));
create table #temp_kd(prow int IDENTITY(1,1) PRIMARY KEY CLUSTERED,t1 nvarchar(100));
declare @timestrat smalldatetime,@timeend smalldatetime,@cxcount int,@kdcount int;
declare @ptplace nvarchar(100),@ptplace1 nvarchar(100),@ptplace2 nvarchar(100), @ptdata real,@pjdata real,@pttime nvarchar(100),@ddptstate int,@ddtime nvarchar(100), @ddtime1 bigint,@bjptstate int,@bjtime nvarchar(100),@bjtime1 bigint;
declare @coptplace nvarchar(100),@coptdata real,@copjdata real,@kdr int;
declare @kdptplace nvarchar(100),@kdptstate int,@kdyc nvarchar(100),@kdtime nvarchar(100);
declare @isHead int,@isHead1 int,@isHead2 int ,@pid int,@bjt nvarchar(100),@bjt1 nvarchar(100),@ptplacetest nvarchar(100);
declare @rows int,@cor int,@ch4r int ,@r int;
declare @ptid nvarchar(100),@ptprop int;
--处理日期
set @timestrat=cast(cast(@year as varchar)+'-'+cast(@month as varchar)+'-'+cast(@day as varchar) as smalldatetime);
set @timeend=DATEADD(day,+1,@timestrat);
set @pid = 1;
set @isHead = 0;
set @isHead1=0;
set @isHead2=0;
set @bjt='';
set @bjt1='';
set @ptplacetest='';
set @coptplace='';
set @coptdata=0;
set @copjdata=0;
insert into #temp_co select Ptplace ,max(ptdata) Bcoptdata,avg(pjdata) Bcopjdata from KJ95daymax where ptprop='4' and kjid=@kjid and jctime>=@timestrat and jctime<=@timeend GROUP BY Ptplace order by Ptplace
insert into #temp_kd select Distinct Ptplace from KJ95daymax where ptprop='41' and kjid=@kjid and jctime>=@timestrat and jctime<=@timeend order by Ptplace
select top 1 @cor=max(prow) from #temp_co;
select top 1 @kdr=max(prow) from #temp_kd;
if(@ch4r>@cor) set @rows = @ch4r;
else set @rows = @cor;
set @r=0;
------CH4%---------------------------------------------------------------------------------------------------------------------------------------------------------
declare myCursor1 cursor for
select ptplace ,max(ptdata) ptdata,avg(pjdata) pjdata,ptid,ptprop
from KJ95daymax
where kjid=@kjid and jctime>=@timestrat and jctime<=@timeend and ptprop='1'
group by ptplace ,ptid,ptprop order by Ptplace,ptid
open myCursor1 FETCH NEXT FROM myCursor1 into @ptplace,@ptdata,@pjdata,@ptid , @ptprop
WHILE @@FETCH_STATUS = 0
begin
--执行
if @isHead=0
begin
set @pid=@pid+1;
insert into #temp_daqjcrb(prow,t1,t2,t3,tptid) values(@pid,@ptplace,@ptdata,@pjdata,@ptid)
set @isHead=1;
end
else
begin
update #temp_daqjcrb set t10=@ptplace,t11=@ptdata,t12=@pjdata,tptid1=@ptid where prow=@pid-1
set @isHead=0;
end
----最大值出现时间
SELECT TOP 1 @ptplace1=ptid, @PTTIME=CONVERT(NVARCHAR(10),JCTIME,108) FROM KJ95DAYMAX WHERE KJID=@KJID
AND ptid=@PTID AND PTPROP=@PTPROP AND PTDATA=@PTDATA AND JCTIME>=@TIMESTRAT AND JCTIME<=@TIMEEND
order by jctime
set @ptplacetest='';
if @ptplace1=@ptid
begin
set @ptplacetest=(select top 1 tptid from #temp_daqjcrb where tptid=@ptplace1)
if @ptplacetest=@ptplace1
begin
update #temp_daqjcrb set t4=@pttime where prow=@pid and tptid=@ptplace1
end
else
begin
update #temp_daqjcrb set t13=@pttime where prow=@pid-1 and tptid1=@ptplace1 end
end
--------报警次数@DDPTSTAT,报警时间@DDTIME
SELECT @ptplace2=ptplace,@bjPTSTATE=COUNT(PTSTATE),@BJTIME1=SUM(datediff(s,BEGINTIME,ENDTIME)) --error '2008-1-17' 其他日期代入无措 FROM KJ95CXDT
WHERE PTSTATE=1 AND KJID=@KJID AND BEGINTIME>=@TIMESTRAT AND ENDTIME<=@TIMEEND AND PTPROP='1' AND ptplace=@ptplace
GROUP BY PTPLACE
if @ptplace2=@ptplace
begin
set @BJTIME=dbo.hhtimestr(dbo.f_SecondsToHourMinuteSecond(@BJTIME1));
set @ptplacetest=(select t1 from #temp_daqjcrb where t1=@ptplace2)
if @ptplacetest=@ptplace2
begin
update #temp_daqjcrb set t5=@bjptstate,t6=@BJTIME where prow=@pid and t1=@ptplace2
end
else
begin
update #temp_daqjcrb set t14=@bjptstate,t15=@BJTIME where prow=@pid-1 and t10=@ptplace2
end
end
-------断电次数@BJPTSTATE,断电时间
SELECT @ptplace2=ptplace ,@ddPTSTATE=COUNT(PTSTATE),@DDTIME1=sum(datediff(s,BEGINTIME,ENDTIME)) --error '2008-1-17' 其他日期代入无措
FROM KJ95CXDT
WHERE PTSTATE=3 AND KJID=@KJID AND BEGINTIME>=@TIMESTRAT AND ENDTIME<=@TIMEEND AND PTPROP='1' AND ptplace=@ptplace
GROUP BY PTPLACE
if @ptplace2=@ptplace
begin
set @DDTIME=dbo.hhtimestr(dbo.f_SecondsToHourMinuteSecond(@DDTIME1));
set @ptplacetest=(select t1 from #temp_daqjcrb where t1=@ptplace2)
if @ptplacetest=@ptplace2
begin
update #temp_daqjcrb set t7=@ddptstate,t8= @DDTIME where prow=@pid and t1=@ptplace2
end
else
begin
update #temp_daqjcrb set t16=@ddptstate,t17= @DDTIME where prow=@pid-1 and t10=@ptplace2
end
end
if @pid<=@cor+1
begin
select @coptplace=t1,@coptdata=t2 ,@copjdata=t3 from #temp_co where prow=@pid-1;
update #temp_daqjcrb set t19=@coptplace,t20=@coptdata,t21=@copjdata where prow=@pid;
---------CO最大值出现时间
select top 1 @ptplace1=ptplace, @pttime=CONVERT(nvarchar(20),jctime,108)
from KJ95daymax
where kjid=@kjid and jctime>=@timestrat and jctime<=@timeend and ptprop='4' and ptplace=@coptplace and
ptdata=(select top 1 max(ptdata) from KJ95daymax where kjid=@kjid and jctime>=@timestrat and jctime<=@timeend and ptprop='4' and ptplace=@coptplace)
group by ptplace,jctime
if @ptplace1=@coptplace
begin
update #temp_daqjcrb set t22=@pttime where prow=@pid and t19=@ptplace1
end
---------CO报警次数
select @ddptstate=count(ptstate) ,@ptplace2=ptplace
from kj95cxdt
where ptstate=1 and kjid=@kjid and begintime>=@timestrat and endtime<=@timeend and ptprop='4' and ptplace=@coptplace
group by ptplace
if @ptplace2=@coptplace
begin
update #temp_daqjcrb set t23=@ddptstate where prow=@pid and t19=@ptplace2
end
-----CO报警时间
declare myCursor9 cursor for
select dbo.difdatetime(begintime,endtime),ptplace from kj95cxdt
where ptstate=1 and kjid=@kjid and begintime>=@timestrat and endtime<=@timeend and ptprop='4' and ptplace=@coptplace
set @ptplace2='';
set @bjt='';
set @bjt1='';
set @ptplacetest='';
open myCursor9 FETCH NEXT FROM myCursor9 into @bjtime,@ptplace2
if @ptplace2=@coptplace
begin
WHILE @@FETCH_STATUS = 0
begin
set @bjt= (select dbo.sumdatetime(@bjt,@bjtime));
update #temp_daqjcrb set t24= substring(@bjt,8,6) where prow=@pid and t19=@ptplace2
FETCH NEXT FROM myCursor9 into @bjtime,@ptplace2
end
end
DEALLOCATE myCursor9;
end
--------馈电-------------------------------------------------------------------------------------------------------------------------------------------------------------------
if @pid<=@kdr+1
begin
select @kdcount= count(ptplace) from kj95cxdt where ptstate>0 and kjid=@kjid and begintime>=@timestrat and endtime<=@timeend and ptprop='41' and ptplace=@kdptplace
group by ptplace
if @kdcount>0
begin
select @kdptplace=t1 from #temp_kd where prow=@pid-1;
update #temp_daqjcrb set t25=@kdptplace where prow=@pid;
---------馈电异常次数
select @ptplace2=ptplace,@kdptstate=count(ptstate)
from kj95cxdt
where ptstate>0 and kjid=@kjid and begintime>=@timestrat and endtime<=@timeend and ptprop='41' and ptplace=@kdptplace
group by ptplace
if @ptplace2=@kdptplace
begin
update #temp_daqjcrb set t26=@kdptstate where prow=@pid and t25=@ptplace2
end
---------馈电异常时间
select @kdyc=diftime,@ptplace2=ptplace from kj95cxdt
where ptstate>0 and kjid=@kjid and begintime>=@timestrat and endtime<=@timeend and ptprop='41' and ptplace=@kdptplace
group by ptplace,diftime ;
if @ptplace2=@kdptplace
begin
update #temp_daqjcrb set t27=@kdyc where prow=@pid and t25=@ptplace2
end
-----馈电异常累计时间
declare myCursor12 cursor for
select dbo.difdatetime(begintime,endtime),ptplace from kj95cxdt
where ptstate>0 and kjid=@kjid and begintime>=@timestrat and endtime<=@timeend and ptprop='41' and ptplace=@kdptplace
set @ptplace2='';
set @kdtime='';
set @bjt='';
open myCursor12 FETCH NEXT FROM myCursor12 into @kdtime,@ptplace2
if @ptplace2=@kdptplace
begin
WHILE @@FETCH_STATUS = 0
begin
set @bjt= (select dbo.sumdatetime(@bjt,@kdtime));
update #temp_daqjcrb set t28= substring(@bjt,8,6) where prow=@pid and t25=@ptplace2
FETCH NEXT FROM myCursor12 into @kdtime,@ptplace2
end
end
DEALLOCATE myCursor12;
end
end
--------------------------------------------------------------------------------------------------------------------------------------------------------
FETCH NEXT FROM myCursor1 into @ptplace,@ptdata,@pjdata, @ptid ,@ptprop
end
DEALLOCATE myCursor1;
select * from #temp_daqjcrb ;
return
--exec rep_daqjcrb 2,2008,1,17GOSET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO/*
SELECT ptplace,COUNT(PTSTATE),SUM(datediff(s,BEGINTIME,ENDTIME))
FROM KJ95CXDT
WHERE PTSTATE=1 AND KJID=2 AND BEGINTIME>='2008-1-15' AND ENDTIME<='2008-1-16' AND PTPROP='1'
GROUP BY PTPLACE select ptplace,COUNT(PTSTATE),SUM(datediff(s,BEGINTIME,ENDTIME))
FROM KJ95CXDT
WHERE PTSTATE=1 AND KJID=2 AND BEGINTIME>='2008-1-17' AND ENDTIME<='2008-1-18' AND PTPROP='1' and ptplace='11103轨道'
GROUP BY PTPLACE select *
FROM KJ95CXDT WHERE PTSTATE=1 AND KJID=2 AND BEGINTIME>='2008-1-15' AND ENDTIME<='2008-1-16' AND PTPROP='1' and ptplace='11103轨道'
group by ptplace
*/
why only '2008-1-17'????
select @kdyc=diftime,@ptplace2=ptplace from kj95cxdt
where ptstate>0 and kjid=@kjid and begintime>=@timestrat and endtime<=@timeend and ptprop='41' and ptplace=@kdptplace
group by ptplace,diftime ;--select @kdyc=diftime这里错了吧?倘若group by ptplace,diftime 以后diftime 有2个值...