can you try (do I misunderstand you?)select id,max(intime) as intime
from mydb
where (intime>'2001-4-20') AND (intime<'2002-8-21')
GROUP BY id
ORDER BY id
from mydb
where (intime>'2001-4-20') AND (intime<'2002-8-21')
GROUP BY id
ORDER BY id
优化SQL语句:根据时间字段取最新纪录,能否进一步优化?
数据库有上千万条数据,我用了自连结查询
方法一:用时40秒
select * from mydb
where id+CONVERT(VARCHAR(26),intime,9) IN
(select id+CONVERT(VARCHAR(26),MAX(intime),9) from mydb
where (intime>'2001-4-20') AND (intime<'2002-8-21') GROUP BY id )
ORDER BY id
方法二:用时8秒
select * from mydb
where (intime>'2001-4-20') AND (intime<'2002-8-21')
and id+CONVERT(VARCHAR(26),intime,9) IN
(select id+CONVERT(VARCHAR(26),MAX(intime),9) from mydb
where (intime>'2001-4-20') AND (intime<'2002-8-21') GROUP BY id )
ORDER BY id
有没有更好的办法??
select id, max(intime) as intime from mydb
where (intime>'2001-4-20') AND (intime<'2002-8-21')
group by id order by id你为什么要把它弄得这么复杂呢?
主select 还有更多字段也要做。将我这个查询再去 join mydb这个表,取出与当前ID相同的记录就可以了。
回复人: mailyinjie(撒旦) ( ) 信誉:100
那就是用外部连结了?
但是max(intime)用了聚组函数,我不能实现连结,你能帮忙写一下查询语句吗?谢谢!
select t1.id,t1.intime
from mydb t1
where exists (select * from mydb t2 where t1.id+CONVERT(VARCHAR(26),t1.intime,9) = t2.id+CONVERT(VARCHAR(26),MAX(t2.intime),9)) and (t1.intime>'2001-4-20') AND (t1.intime<'2002-8-21')
from mydb join (select checksum(id,max(intime)) as chk
from mydb t1
where (intime>'2001-4-20')
AND (intime<'2002-8-21')
group by id) t0
on checksum(id,intime)=t0.chk
ORDER BY id我自己试了下,好象要快些。我的数据不多,用你上面的方法也没要那么长时间,所以你用你的试试看吧。
where (a.intime>'2001-4-20') AND (a.intime<'2002-8-21')
and a.id=b.id
and a.intime=b.intime
where (a.intime>'2001-4-20') AND (a.intime<'2002-8-21')
and a.id=b.id
and a.intime=b.intime
查询有错:
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference. 回复人: icevi(按钮工厂) ( ) 信誉:120
查询有错:'checksum' is not a recognized function name. 回复人: Yang_(扬帆破浪) ( ) 信誉:268
Ambiguous column name 'id'.
Ambiguous column name 'intime'. 回复人: Yang_(扬帆破浪) ( ) 信誉:268
我试了,需要10秒钟!!
查询优化问题看来很重要!!!嘿嘿!!
SELECT ID,MAX(intime) AS intime FROM mydb GROUP BY ID如果不是可以这样写
SELECT A.id, B.intime
FROM mydb A INNER JOIN
(SELECT ID,MAX(intime) AS intime FROM mydb GROUP BY ID) B ON A.ID=B.ID AND A.intime=B.intime
WHERE DATEDIFF(DY,'2001-4-20',A.intime)>=0 AND DATEDIFF(DY,'2002-8-21',A.intime)<0
where (intime>'2001-4-20') AND (intime<'2002-8-21') and
exists(select * from (select id,max(intime) as intime from mydb group by id) t2 where t1.id=t2.id and t1.intime=t2.intime)
where (a.intime>'2001-4-20') AND (a.intime<'2002-8-21')
and intime=
(select MAX(intime) as intime from mydb where (intime>'2001-4-20') AND (intime<'2002-8-21') and id=a.id )
select STCD,YMDHM,CONVERT(VARCHAR(10),ZR),CONVERT(VARCHAR(10),Q) from ST_RIVER_R
where (YMDHM>'2002-8-10') AND (YMDHM<'2002-8-21')
and STCD+CONVERT(VARCHAR(26),YMDHM,9) IN
(SELECT STCD+CONVERT(VARCHAR(26),MAX(YMDHM),9) FROM ST_RIVER_R
where (YMDHM>'2002-8-10') AND (YMDHM<'2002-8-21') GROUP BY STCD )
ORDER BY STCD查询1秒51条数据
select a.*, b.ymdhm from st_river_r a inner join
(select stcd, max(ymdhm) as ymdhm from st_river_r
where (ymdhm>'2002-8-10') AND (ymdhm<'2002-8-21')
group by stcd) b
on a.stcd = b.stcd and a.ymdhm=b.ymdhm
where (a.ymdhm>'2002-8-10') AND (a.ymdhm<'2002-8-21')
order by a.stcd查询6秒47条数据
select * from ST_RIVER_R t1
where (YMDHM>'2002-8-10') AND (YMDHM<'2002-8-21') and
exists(select * from (select STCD,max(YMDHM) as YMDHM from ST_RIVER_R group by STCD) t2 where t1.STCD=t2.STCD and t1.YMDHM=t2.YMDHM)
谢谢!!!