表#SM_SLMC_R_D有数据
STCD TM (全为一天的) VVSWC
60436100 2009-09-16 01:00:00 1.3
60436100 2009-09-16 02:00:00 1.2
60436100 2009-09-16 03:00:00 1.1
60436100 2009-09-16 04:00:00 1.3
60436100 2009-09-16 05:00:00 1.1
60436100 2009-09-16 01:00:00 1.4
60436200 2009-09-16 02:00:00 1.5
60436200 2009-09-16 03:00:00 1.3
。 。 。
。 。 。
。 。 。
目的集为
STCD DT DASMC DMXSMC DMXSMCOT DMNSMC DMNSMCOT
60436100 2009-09-16 1.2 1.3 2009-09-16 01:00:00 1.1 2009-09-16 03:00:00
60436200 2009-09-16 1.4 1.5 2009-09-16 13:00:00 1.3 2009-09-16 15:00:00以下为按照你和小麦指点 我写的
SELECT A.STCD,CONVERT(VARCHAR(10),A.TM,120)AS DT,
AVG(C.VVSWC)AS DASMC,
B.VVSWC AS DMXSMC, B.TM AS DMXSMCOT,
A.VVSWC AS DMNSMC, A.TM AS DMNSMCOT
FROM
(SELECT *,RN=(SELECT COUNT(1)+1 FROM #SM_SLMC_R_D WHERE VVSWC<a.VVSWC and STCD=a.STCD)FROM #SM_SLMC_R_D a) A
JOIN
(SELECT *,RN=(SELECT COUNT(1)+1 FROM #SM_SLMC_R_D WHERE VVSWC>a.VVSWC and STCD=a.STCD)FROM #SM_SLMC_R_D a) B
ON A.STCD=B.STCD,
#SM_SLMC_R_D C
WHERE A.RN=1 AND B.RN=1
GROUP BY A.STCD,B.STCD,A.TM,B.TM,A.VVSWC,B.VVSWC
现有两个疑问:
第一:当每天的max、min出现相同值时 (发生时间不同) 想不出来 该如何去做 看看你有没有什么好想法
还有就是这段代码 A join B ON A.STCD=B.STCD 的时候 如果max、min出现相同值 就会出现很多交集
第二:算平均值 AVG(C.VVSWC)AS DASMC, 时,算出的总是所有值的平均值,而不是单一STCD的
不知道 #SM_SLMC_R_D C 该如何约束
STCD TM (全为一天的) VVSWC
60436100 2009-09-16 01:00:00 1.3
60436100 2009-09-16 02:00:00 1.2
60436100 2009-09-16 03:00:00 1.1
60436100 2009-09-16 04:00:00 1.3
60436100 2009-09-16 05:00:00 1.1
60436100 2009-09-16 01:00:00 1.4
60436200 2009-09-16 02:00:00 1.5
60436200 2009-09-16 03:00:00 1.3
。 。 。
。 。 。
。 。 。
目的集为
STCD DT DASMC DMXSMC DMXSMCOT DMNSMC DMNSMCOT
60436100 2009-09-16 1.2 1.3 2009-09-16 01:00:00 1.1 2009-09-16 03:00:00
60436200 2009-09-16 1.4 1.5 2009-09-16 13:00:00 1.3 2009-09-16 15:00:00以下为按照你和小麦指点 我写的
SELECT A.STCD,CONVERT(VARCHAR(10),A.TM,120)AS DT,
AVG(C.VVSWC)AS DASMC,
B.VVSWC AS DMXSMC, B.TM AS DMXSMCOT,
A.VVSWC AS DMNSMC, A.TM AS DMNSMCOT
FROM
(SELECT *,RN=(SELECT COUNT(1)+1 FROM #SM_SLMC_R_D WHERE VVSWC<a.VVSWC and STCD=a.STCD)FROM #SM_SLMC_R_D a) A
JOIN
(SELECT *,RN=(SELECT COUNT(1)+1 FROM #SM_SLMC_R_D WHERE VVSWC>a.VVSWC and STCD=a.STCD)FROM #SM_SLMC_R_D a) B
ON A.STCD=B.STCD,
#SM_SLMC_R_D C
WHERE A.RN=1 AND B.RN=1
GROUP BY A.STCD,B.STCD,A.TM,B.TM,A.VVSWC,B.VVSWC
现有两个疑问:
第一:当每天的max、min出现相同值时 (发生时间不同) 想不出来 该如何去做 看看你有没有什么好想法
还有就是这段代码 A join B ON A.STCD=B.STCD 的时候 如果max、min出现相同值 就会出现很多交集
第二:算平均值 AVG(C.VVSWC)AS DASMC, 时,算出的总是所有值的平均值,而不是单一STCD的
不知道 #SM_SLMC_R_D C 该如何约束
insert into tb values('60436100' , '2009-09-16 01:00:00' , 1.3)
insert into tb values('60436100' , '2009-09-16 02:00:00' , 1.2)
insert into tb values('60436100' , '2009-09-16 03:00:00' , 1.1)
insert into tb values('60436100' , '2009-09-16 04:00:00' , 1.3)
insert into tb values('60436100' , '2009-09-16 05:00:00' , 1.1)
insert into tb values('60436100' , '2009-09-16 01:00:00' , 1.4)
insert into tb values('60436200' , '2009-09-16 02:00:00' , 1.5)
insert into tb values('60436200' , '2009-09-16 03:00:00' , 1.3)
go--如果最大值或最小值出现相同时间时,取最小的时间
select STCD ,
convert(varchar(10),tm,120) dt ,
cast(avg(VVSWC) as decimal(18,1)) DASMC ,
max(VVSWC) DMXSMC ,
(select top 1 TM from tb where STCD = t.STCD and convert(varchar(10),tm,120) = convert(varchar(10),t.tm,120) and VVSWC = (select max(VVSWC) from tb where STCD = t.STCD and convert(varchar(10),tm,120) = convert(varchar(10),t.tm,120)) order by TM) DMXSMCOT,
min(VVSWC) DMNSMC ,
(select top 1 TM from tb where STCD = t.STCD and convert(varchar(10),tm,120) = convert(varchar(10),t.tm,120) and VVSWC = (select min(VVSWC) from tb where STCD = t.STCD and convert(varchar(10),tm,120) = convert(varchar(10),t.tm,120)) order by TM) DMNSMCOT
from tb t
group by STCD ,convert(varchar(10),tm,120)
/*
STCD dt DASMC DMXSMC DMXSMCOT DMNSMC DMNSMCOT
---------- ---------- -------------------- -------------------- ------------------------------------------------------ -------------------- ------------------------------------------------------
60436100 2009-09-16 1.2 1.4 2009-09-16 01:00:00.000 1.1 2009-09-16 03:00:00.000
60436200 2009-09-16 1.4 1.5 2009-09-16 02:00:00.000 1.3 2009-09-16 03:00:00.000(所影响的行数为 2 行)
*/--如果最大值或最小值出现相同时间时,取最大的时间
select STCD ,
convert(varchar(10),tm,120) dt ,
cast(avg(VVSWC) as decimal(18,1)) DASMC ,
max(VVSWC) DMXSMC ,
(select top 1 TM from tb where STCD = t.STCD and convert(varchar(10),tm,120) = convert(varchar(10),t.tm,120) and VVSWC = (select max(VVSWC) from tb where STCD = t.STCD and convert(varchar(10),tm,120) = convert(varchar(10),t.tm,120)) order by TM desc) DMXSMCOT,
min(VVSWC) DMNSMC ,
(select top 1 TM from tb where STCD = t.STCD and convert(varchar(10),tm,120) = convert(varchar(10),t.tm,120) and VVSWC = (select min(VVSWC) from tb where STCD = t.STCD and convert(varchar(10),tm,120) = convert(varchar(10),t.tm,120)) order by TM desc) DMNSMCOT
from tb t
group by STCD ,convert(varchar(10),tm,120)
/*
STCD dt DASMC DMXSMC DMXSMCOT DMNSMC DMNSMCOT
---------- ---------- -------------------- -------------------- ------------------------------------------------------ -------------------- ------------------------------------------------------
60436100 2009-09-16 1.2 1.4 2009-09-16 01:00:00.000 1.1 2009-09-16 05:00:00.000
60436200 2009-09-16 1.4 1.5 2009-09-16 02:00:00.000 1.3 2009-09-16 03:00:00.000(所影响的行数为 2 行)
*/drop table tb