一个挺简单的有关问题，怎么进行统计

www.myexceptions.net  网友分享于：2013-03-23  浏览：4次

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)a
inner join
(select id , recvdate , value where id = xx order by recvdate asc limit 1)b
on
a.id=b.id
inner join
(
select id , recvdate , value where id = xx order by value desc limit 1
)c
on
b.id=c.id
inner join
(
select id , recvdate , value where id = xx order by value desc limit 1
)d
on
c.id=d.id
------解决方案--------------------参考:SQL codeselect 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 codeCREATE 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 t
GROUP BY id

UNION

SELECT
t.id,min(t.value),(SELECT recvdate FROM #temp WHERE t.id=id AND min(t.value)=value)
FROM #temp AS t
GROUP BY id

UNION

SELECT
t.id,(SELECT value FROM #temp WHERE t.id=id AND max(t.recvdate)=recvdate),max(recvdate)
FROM #temp AS t
GROUP BY id

UNION

SELECT
t.id,(SELECT value FROM #temp WHERE t.id=id AND min(t.recvdate)=recvdate),min(recvdate)
FROM #temp AS t
GROUP BY id
------解决方案--------------------SQL codecreate table tb(id int,recvdate datetime,value decimal(10,2))
insert into tb select 1,'2011-12-01 09:30:06',12.56
insert into tb select 1,'2011-12-01 11:05:32',12.68
insert into tb select 1,'2011-12-01 11:25:37',12.84
insert into tb select 1,'2011-12-01 13:25:17',12.62
insert into tb select 1,'2011-12-01 15:00:00',12.44
insert into tb select 1,'2011-12-02 11:00:00',12.90
go
select 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.id
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)
)c on c.id=a.id
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 recvdate>a.recvdate)
)d on d.id=a.id
/*
id          dt         开盘价         最高价         最低价         收盘价
----------- ---------- -------------- -------------- -------------- --------------------
1           2011-12-01 12.56          12.84          12.44          12.44

(1 行受影响)
*/
go
drop table tb
------解决方案--------------------或许说的卡死是个错误的认识，只不过是执行时间比较久罢了，因为你不是一个简单的查询。max，group by等注定要经过多次的筛选和运算。有个误区，初学者总是不论几万，几十万，几百万数据，总期望在一个语句中实现，或者期望在几秒钟得到结果。其实是不现实的。耗用的时间，很大部分集中在从众多的数据中筛选符合条件的行，并根据你的数据规模而成倍增加，比较复杂的运算耗时几分，几十分，几个小时都有可能。建议：1.进行必要的数据规模的控制，比如每批只处理10个id或者100个id。并提取数据到临时的表中再进行运算，这样数据规模小很多。以往的经验看，往往10万行内进行统计，数据库的效率都比较满意。上到百万条，io和索引，内存等是存在瓶颈的。2. 可以增补一些字段，然后做update将一些数据设置标记，并清除掉，或者挪到其他历史表中。3. 可以做一些计算字段，辅助计算。4. 对数据的预处理和清洗也是必要的。```