各位大神,第一出题,分不多,但我感觉题有点挑战哟,数据如下,
需求:
1.必须按照RegionId分组取得最大的Year,Month的TurnOver
(这里不能直接select max(YEAR),max(Month) from XX GROUP BY RegionId,这样year和month就分开取最大了,不能分开,YEAR加month就是个日期,取日期最大的)
TurnOver 不能用SUM,AVG 就取 当前日期最大对应的TurnOver值
RecordId 是主键 2.取到最大日期的值了 然后取到每个分组最大日期前面最大日期对应的值(就是说 第一步取到分组后最大日期对应的TrunOver的值了,再把这个最大日期往前挪的最大日期所对应的TrunOver的值,就是要小于第一步获取的最大日期) 注意 这是mysql 对 mssql很多函数用不了的
我想给两百分的 怎么发布只能100分啊。。o(︶︿︶)o 唉新手 哈哈
RecordId,RegionId,Year,Month,TurnOver
5151 , 09 , 2009 , 10 , 120
5152 , 09 , 2009 , 11 , 107.67
5153 , 09 , 2010 , 11 , 23423
6152 , 09 , 2010 , 11 , 23
5154 , 10 , 2009 , 5 , 97
5155 , 10 , 2009 , 7 , 67.75
5156 , 10 , 2009 , 9 , 494
6153 , 10 , 2009 , 9 , 2925
5157 , 10 , 2011 , 3 , 400
5158 , 11 , 2009 , 7 , 128
5159 , 11 , 2009 , 9 , 82.33
6154 , 11 , 2009 , 9 , 3200
5160 , 11 , 2009 , 10 , 111.5
5161 , 11 , 2009 , 11 , 134.25
5162 , 11 , 2011 , 3 , 146
5164 , 12 , 2008 , 7 , 90
6156 , 12 , 2009 , 3 , 5000
第一步我已经做了 如下(我新手,写得不好勿喷)
select * from jos_regionbidprice a
where RecordId = (
select RecordId FROM jos_regionbidprice
where RegionId = a.RegionId order by Year desc,Month desc limit 0,1
)
select * from jos_regionbidprice a
where RecordId = (
select RecordId FROM jos_regionbidprice
where RegionId = a.RegionId and UnitType=1 and Status=1 order by Year desc,Month desc limit 1,1
)
(这里不能直接select max(YEAR),max(Month) from XX GROUP BY RegionId,这样year和month就分开取最大了,不能分开,YEAR加month就是个日期,取日期最大的)
TurnOver 不能用SUM,AVG 就取 当前日期最大对应的TurnOver值
RecordId 是主键select max(year*100+Month) from XX GROUP BY RegionId
select RecordId,Year,Month,TurnOver
from XX t
where not exists (select 1 from XX where RecordId=t.RecordId and Year*100+Month>t.Year*100+t.Month)
SELECT * FROM tt1 a WHERE NOT EXISTS(SELECT 1 FROM tt1 WHERE a.`RegionId`=`RegionId`
AND a.`Year`*100+a.`Month`<`Year`*100+`Month`);如果年月相同取TurnOver最大
SELECT * FROM tt1 a WHERE NOT EXISTS(SELECT 1 FROM tt1 WHERE a.`RegionId`=`RegionId`
AND (
(a.`Year`*100+a.`Month`<`Year`*100+`Month`)
OR
(a.`Year`*100+a.`Month`=`Year`*100+`Month` AND a.`TurnOver`<`TurnOver`)));2、取第2大日期对应的TurnOver值?
SELECT a.* FROM tt1 a INNER JOIN (
SELECT a.`RegionId`,MAX(a.`Year`) AS ma,MAX(a.`Month`) AS mm FROM tt1 a INNER JOIN aa b ON a.`RegionId`=b.`RegionId`
AND (a.`Year`*100+a.`Month`)<(b.`Year`*100+b.`Month`)
GROUP BY a.`RegionId`) b ON
a.`Year`*100+a.`Month`=ma*100+mm AND a.`RegionId`=b.`RegionId`