有一个 table
有 id, recvdate , value 3个字段
有 1000 多个 不同的id
每个相同 id 有 200-300 条记录
现在 需要 统计
id max(recvdate) , value ( max(recvdate) 时候的 )，max(recvdate) , value(max(recvdate) 时候的), min(value), recvdate( min(value) 时候的recvdate) , max(value) , recvdate( max(value） 时候的recvdate))
看起来简单，但是 做起来比较麻烦。 有办法吗?
------解决方案--------------------
- SQL code
select *from(select id , recvdate , value where id = xx order by recvdate desc limit 1)ainner join(select id , recvdate , value where id = xx order by recvdate asc limit 1)bon a.id=b.idinner join(select id , recvdate , value where id = xx order by value desc limit 1)con b.id=c.idinner join(select id , recvdate , value where id = xx order by value desc limit 1)don c.id=d.id
------解决方案--------------------
参考:
- SQL code
select convert(varchar(10),pushtime,120)时间,code as 代码,(select top 1 price from tb b where code=a.code and convert(varchar(10),pushtime,120)=convert(varchar(10),a.pushtime,120) and not exists(select 1 from tb where code=a.code and convert(varchar(10),pushtime,120)=convert(varchar(10),a.pushtime,120) and pushtime<b.pushtime)) as 开盘价,(select top 1 price from tb b where code=a.code and convert(varchar(10),pushtime,120)=convert(varchar(10),a.pushtime,120) and not exists(select 1 from tb where code=a.code and convert(varchar(10),pushtime,120)=convert(varchar(10),a.pushtime,120) and pushtime>b.pushtime)) as 收盘价,(select max(price) from tb where code=a.code and convert(varchar(10),pushtime,120)=convert(varchar(10),a.pushtime,120)) as 最高价,(select min(price) from tb where code=a.code and convert(varchar(10),pushtime,120)=convert(varchar(10),a.pushtime,120)) as 最低价from tb a
------解决方案--------------------
select convert(varchar(10),pushtime,120)时间,code as 代码,
(select top 1 price from tb b where code=a.code and convert(varchar(10),pushtime,120)=convert(varchar(10),a.pushtime,120) and not exists(select 1 from tb where code=a.code and convert(varchar(10),pushtime,120)=convert(varchar(10),a.pushtime,120) and pushtime<b.pushtime)) as 开盘价,
(select top 1 price from tb b where code=a.code and convert(varchar(10),pushtime,120)=convert(varchar(10),a.pushtime,120) and not exists(select 1 from tb where code=a.code and convert(varchar(10),pushtime,120)=convert(varchar(10),a.pushtime,120) and pushtime>b.pushtime)) as 收盘价,
(select max(price) from tb where code=a.code and convert(varchar(10),pushtime,120)=convert(varchar(10),a.pushtime,120)) as 最高价,
(select min(price) from tb where code=a.code and convert(varchar(10),pushtime,120)=convert(varchar(10),a.pushtime,120)) as 最低价
from tb a
楼主的想法说的不是很清楚。
你直接告诉我们你想获取的结果是什么信息，直接用文字描述就可以了
------解决方案--------------------
- SQL code
CREATE TABLE #temp( id int, recvdate datetime, value int)INSERT INTO #temp(id,recvdate,value)VALUES(1,'2011-12-1',100)INSERT INTO #temp(id,recvdate,value)VALUES(1,'2011-11-8',50)INSERT INTO #temp(id,recvdate,value)VALUES(1,'2011-10-21',150)INSERT INTO #temp(id,recvdate,value)VALUES(2,'2011-09-10',200)INSERT INTO #temp(id,recvdate,value)VALUES(2,'2011-12-5',10)INSERT INTO #temp(id,recvdate,value)VALUES(2,'2011-01-5',245)INSERT INTO #temp(id,recvdate,value)VALUES(3,'2010-12-5',10)INSERT INTO #temp(id,recvdate,value)VALUES(3,'2011-12-6',100)INSERT INTO #temp(id,recvdate,value)VALUES(3,'2011-12-7',50)INSERT INTO #temp(id,recvdate,value)VALUES(4,'2011-12-10',12)SELECT t.id,max(t.value),(SELECT recvdate FROM #temp WHERE t.id=id AND max(t.value)=value)FROM #temp AS tGROUP BY idUNIONSELECT t.id,min(t.value),(SELECT recvdate FROM #temp WHERE t.id=id AND min(t.value)=value)FROM #temp AS tGROUP BY idUNION SELECT t.id,(SELECT value FROM #temp WHERE t.id=id AND max(t.recvdate)=recvdate),max(recvdate)FROM #temp AS tGROUP BY idUNIONSELECT t.id,(SELECT value FROM #temp WHERE t.id=id AND min(t.recvdate)=recvdate),min(recvdate)FROM #temp AS tGROUP BY id
------解决方案--------------------
- SQL code
create table tb(id int,recvdate datetime,value decimal(10,2))insert into tb select 1,'2011-12-01 09:30:06',12.56insert into tb select 1,'2011-12-01 11:05:32',12.68insert into tb select 1,'2011-12-01 11:25:37',12.84insert into tb select 1,'2011-12-01 13:25:17',12.62insert into tb select 1,'2011-12-01 15:00:00',12.44insert into tb select 1,'2011-12-02 11:00:00',12.90goselect a.id,'2011-12-01' as dt,a.value as 开盘价,b.value as 最高价,c.value as 最低价,d.value as 收盘价from (select * from tb a where convert(varchar(10),recvdate,120)='2011-12-01' and not exists(select 1 from tb where id=a.id and convert(varchar(10),recvdate,120)='2011-12-01' and recvdate<a.recvdate))a inner join (select * from tb a where convert(varchar(10),recvdate,120)='2011-12-01' and not exists(select 1 from tb where id=a.id and convert(varchar(10),recvdate,120)='2011-12-01' and value>a.value))b on a.id=b.idinner join (select * from tb a where convert(varchar(10),recvdate,120)='2011-12-01' and not exists(select 1 from tb where id=a.id and convert(varchar(10),recvdate,120)='2011-12-01' and value<a.value))c on c.id=a.idinner join (select * from tb a where convert(varchar(10),recvdate,120)='2011-12-01' and not exists(select 1 from tb where id=a.id and convert(varchar(10),recvdate,120)='2011-12-01' and recvdate>a.recvdate))d on d.id=a.id/*id dt 开盘价 最高价 最低价 收盘价----------- ---------- -------------- -------------- -------------- --------------------1 2011-12-01 12.56 12.84 12.44 12.44(1 行受影响)*/godrop table tb
------解决方案--------------------
或许说的卡死是个错误的认识，只不过是执行时间比较久罢了，因为你不是一个简单的查询。max，group by等注定要经过多次的筛选和运算。有个误区，初学者总是不论几万，几十万，几百万数据，总期望在一个语句中实现，或者期望在几秒钟得到结果。其实是不现实的。耗用的时间，很大部分集中在从众多的数据中筛选符合条件的行，并根据你的数据规模而成倍增加，比较复杂的运算耗时几分，几十分，几个小时都有可能。
建议：1.进行必要的数据规模的控制，比如每批只处理10个id或者100个id。并提取数据到临时的表中再进行运算，这样数据规模小很多。以往的经验看，往往10万行内进行统计，数据库的效率都比较满意。上到百万条，io和索引，内存等是存在瓶颈的。
2. 可以增补一些字段，然后做update将一些数据设置标记，并清除掉，或者挪到其他历史表中。
3. 可以做一些计算字段，辅助计算。
4. 对数据的预处理和清洗也是必要的。