SQL.Text := 'select a.USERID,a.USERNAME,b.METERID,min(b.LASTHEAT) as LASTHEAT,Max(b.TOTALHEAT) '
+ 'as TOTALHEAT, sum(b.TOTALHEATMONEY) as TOTALHEATMONEY, '
+ 'a.AREANAME,a.HOUSENAME,a.HOUSENO '
+ 'from TB_USERINFO a,TB_COSTINVENTORY b '
+ 'where a.METERID=b.METERID and (b.TODAYTIME between :startday and :endday) Group by b.METERID';
这是我的一条出错的SQL语句,我想要得到LASTHEAT的最新值和TOTALHEAT的最大值,还要对TOTALHEATMONEY进行统计,这个语句中因为group by b.METERID而导致出错,我想改下,但是不知道怎么改,所以求助。
+ 'as TOTALHEAT, sum(b.TOTALHEATMONEY) as TOTALHEATMONEY, '
+ 'a.AREANAME,a.HOUSENAME,a.HOUSENO '
+ 'from TB_USERINFO a,TB_COSTINVENTORY b '
+ 'where a.METERID=b.METERID and (b.TODAYTIME between :startday and :endday) Group by b.METERID';
这是我的一条出错的SQL语句,我想要得到LASTHEAT的最新值和TOTALHEAT的最大值,还要对TOTALHEATMONEY进行统计,这个语句中因为group by b.METERID而导致出错,我想改下,但是不知道怎么改,所以求助。
--1、LASTHEAT的最新值(从你的SQL语句看“最新”就是最小值)、TOTALHEAT的最大值
select min(b.LASTHEAT) as LASTHEAT, Max(b.TOTALHEAT) as TOTALHEAT from TB_USERINFO a, TB_COSTINVENTORY b where a.METERID = b.METERID and (b.TODAYTIME between :startday and :endday)--2、按METERID 分组对TOTALHEATMONEY 求和统计
sum(b.TOTALHEATMONEY) as TOTALHEATMONEY from TB_USERINFO a, TB_COSTINVENTORY b where a.METERID = b.METERID and (b.TODAYTIME between :startday and :endday) group by b.METERID
select min(b.LASTHEAT) as LASTHEAT, Max(b.TOTALHEAT) as TOTALHEAT from TB_USERINFO a, TB_COSTINVENTORY b where a.METERID = b.METERID and (b.TODAYTIME between :startday and :endday)2、Max:
sum(b.TOTALHEATMONEY) as TOTALHEATMONEY from TB_USERINFO a, TB_COSTINVENTORY b where a.METERID = b.METERID and (b.TODAYTIME between :startday and :endday) group by b.METERID
把以上兩個查詢結果放在表中,再進行關聯查詢即可
a.USERID,a.USERNAME,b.METERID,a.AREANAME,a.HOUSENAME,a.HOUSENO
应该放到Group by子句里面,
或者去掉
a.USERID,a.USERNAME,a.AREANAME,a.HOUSENAME,a.HOUSENO
select a.USERID,a.USERNAME,b.METERID, c.LASTHEAT,c.CURRENTHEAT,d.TOTALHEATMONEY,a.AREANAME from TB_USERINFO a,TB_COSTINVENTORY b, (select METERID,min(LASTHEAT) as LASTHEAT,Max(CURRENTHEAT)
as CURRENTHEAT from TB_COSTINVENTORY group by METERID) c,(select METERID,sum(TOTALHEATMONEY) as TOTALHEATMONEY from TB_COSTINVENTORY group by METERID) d where a.METERID=b.METERID and b.METERID = c.METERID and c.METERID=d.METERID现在这样结果有点差距,只要把相同USERID合并为一条记录就OK了