有一表字段如下
用户,日期,读数我要求如2009-10-1至2009-10-30日的用量,并要求最大值 ,最小值,以及对应的日期
用量等于 30号读数-1号读数,我用以下语句可以实现可以select AREAGUID,max(ddate) as maxddate,min(ddate) as minddate,max(METERNLLJ)-min(METERNLLJ) as MeterNLLJ,max(METERNLLJ) as aa,min(METERNLLJ) as bb from ( select AREAGUID from TWater where
(to_char(DDate,'yyyy-mm-dd')='2009-10-1' or to_char(DDate,'yyyy-mm-dd')='2009-10-30') and AreaGuid = 407) xx group by AREAGUID但有一个问题,如果我1号的读数为1000,30号的读数为500,即1号>30号的读数(有很多这样的数),那么我取日期出来就错了,变成相反的了应该如何修改一下呢。谢谢
用户,日期,读数我要求如2009-10-1至2009-10-30日的用量,并要求最大值 ,最小值,以及对应的日期
用量等于 30号读数-1号读数,我用以下语句可以实现可以select AREAGUID,max(ddate) as maxddate,min(ddate) as minddate,max(METERNLLJ)-min(METERNLLJ) as MeterNLLJ,max(METERNLLJ) as aa,min(METERNLLJ) as bb from ( select AREAGUID from TWater where
(to_char(DDate,'yyyy-mm-dd')='2009-10-1' or to_char(DDate,'yyyy-mm-dd')='2009-10-30') and AreaGuid = 407) xx group by AREAGUID但有一个问题,如果我1号的读数为1000,30号的读数为500,即1号>30号的读数(有很多这样的数),那么我取日期出来就错了,变成相反的了应该如何修改一下呢。谢谢
解决方案 »
- SQL Server数据库的优化
- sqlserver 在存储过程中的游标内使用两个update出现错误
- 求一句最效率的SQL语句写法
- ====== XP SP2下装SQL2000个人版到最后总是“安装程序配置服务器失败,”,请教高手什么原因啊???
- opendatasource问题
- GROUP BY 子句中指定的表达式太多。当指定了 CUBE 或 ROLLUP 时,最大数目为 10。
- SQL Server2005 系统表的问题
- 这个sql语句怎么写?
- Alter Table Add Column时 可以将Column 加到指定的位置吗?
- 超过了触发器嵌套层数??
- sql关于密码的问题(超级菜鸟请教)
- 为什么sql2000里在sde查询在表前需要加sde.
ddate之类的字段在你from的查询子集里没有啊
select AREAGUID,max(ddate) as maxddate,min(ddate) as minddate,max(METERNLLJ)-min(METERNLLJ) as MeterNLLJ,max(METERNLLJ) as aa,min(METERNLLJ) as bb from ( select AREAGUID,ddate,METERNLLJ from TWater where
(to_char(DDate,'yyyy-mm-dd')='2009-10-1' or to_char(DDate,'yyyy-mm-dd')='2009-10-30') and AreaGuid = 407) xx group by AREAGUID我的意思是,比如我有两条记录
用户 读数 日期
1 1000 2009-10-1
1 500 2009-10-30
我查出的数据应该为
用户 用量 最大 最大对应日期 最小 最小对应日期
1 -500 1000 2009-10-1 500 2009-10-30但上面SQL实际查出是
用户 用量 最大 最大对应日期 最小 最小对应日期
1 500 1000 2009-10-30 500 2009-10-1不对呀
create table #TWater (
DDate datetime,
AREAGUID int,
MeterNLLJ int
)insert into #TWater
select '2009-10-1',407,1000 union all
select '2009-10-30',407,500select AREAGUID,
ZZ.max_date as maxddate,
ZZ.min_date as minddate,
CASE WHEN ZZ.max_date > ZZ.min_date THEN
ZZ.max_METERNLLJ - ZZ.min_METERNLLJ
ELSE
ZZ.min_METERNLLJ - ZZ.max_METERNLLJ
END AS MeterNLLJ,
ZZ.max_METERNLLJ as max_METERNLLJ,
ZZ.min_METERNLLJ as min_METERNLLJ
from
(SELECT * FROM ( select TOP 1 AREAGUID,ddate as min_date,METERNLLJ as min_METERNLLJ
from #TWater
where (DDate ='2009-10-1' or DDate = '2009-10-30' ) and AreaGuid = 407
ORDER BY METERNLLJ ASC ) XX LEFT JOIN
( select TOP 1 AREAGUID AS AREAGUID_TMP,ddate as max_date,METERNLLJ as max_METERNLLJ
from #TWater
where (DDate ='2009-10-1' or DDate = '2009-10-30' ) and AreaGuid = 407
ORDER BY METERNLLJ DESC) YY ON
XX.AREAGUID = YY.AREAGUID_TMP) ZZ
自己看吧,MSSQL的代码
你用的应该不是吧?发错版面了
MSSQL里没有to_char函数的
不过还有一些条件,我还要过滤掉用量>1000 或者<-500的数据,并且要分组AreaGuid 不唯一的我原来SQL是这样的select AREAGUID,MeterID,DeviceType,AutoID,PoolAddr,MbusID,max(ddate) as maxddate,min(ddate) as minddate,max(METERNLLJ)-min(METERNLLJ) as MeterNLLJ,max(METERNLLJ) as aa,min(METERNLLJ) as bb from
( select AREAGUID,MeterID,DeviceType,AutoID,PoolAddr,MbusID,METERNLLJ,ddate from twater where (DDate='2009-10-01' or DDate='2009-10-30') and AreaGuid = 407)
xx group by AREAGUID,MeterID,DeviceType,AutoID,PoolAddr,MbusID
having max(METERNLLJ)-min(METERNLLJ)>7000 or max(METERNLLJ)-min(METERNLLJ)<-50)
create table #TWater (
DDate datetime,
AREAGUID int,
MeterNLLJ int
)insert into #TWater
select '2009-10-1',407,1000 union all
select '2009-10-30',407,500 union all
select '2009-10-1',408,1500 union all
select '2009-10-30',408,600
select AREAGUID,
ZZ.max_date as maxddate,
ZZ.min_date as minddate,
CASE WHEN ZZ.max_date > ZZ.min_date THEN
ZZ.max_METERNLLJ - ZZ.min_METERNLLJ
ELSE
ZZ.min_METERNLLJ - ZZ.max_METERNLLJ
END AS MeterNLLJ,
ZZ.max_METERNLLJ as max_METERNLLJ,
ZZ.min_METERNLLJ as min_METERNLLJ
from
(SELECT * FROM ( select AREAGUID,ddate as min_date,METERNLLJ as min_METERNLLJ
from (select row_number() over(PARTITION by AREAGUID ORDER BY METERNLLJ ASC) as row_num,
AREAGUID,
ddate,
METERNLLJ from #TWater
where (DDate ='2009-10-1' or DDate = '2009-10-30' ) ) aa
where row_num = 1) XX LEFT JOIN
( select AREAGUID AS AREAGUID_TMP,ddate as max_date,METERNLLJ as max_METERNLLJ
from (select row_number() over(PARTITION by AREAGUID ORDER BY METERNLLJ DESC) as row_num,
AREAGUID,
ddate,
METERNLLJ from #TWater
where (DDate ='2009-10-1' or DDate = '2009-10-30' ) ) aa
where row_num = 1) YY ON
XX.AREAGUID = YY.AREAGUID_TMP) ZZ
where CASE WHEN ZZ.max_date > ZZ.min_date THEN
ZZ.max_METERNLLJ - ZZ.min_METERNLLJ
ELSE
ZZ.min_METERNLLJ - ZZ.max_METERNLLJ
END <= 1000
and CASE WHEN ZZ.max_date > ZZ.min_date THEN
ZZ.max_METERNLLJ - ZZ.min_METERNLLJ
ELSE
ZZ.min_METERNLLJ - ZZ.max_METERNLLJ
END >= -500
我不知道你要最后过滤,还是一开始就过滤,上面的写法是最后过滤掉1000和-500范围外的纪录
如果你要一开始在同AREAGUID内过滤,把条件加到aa,bb那两个表的where条件里就行了