求一个高效sql语句，在线等

[jzjrod]

有以下数据：
bpid pid price crtime
001 1 3.00 2009-05-01
002 1 2.00 2009-04-01
003 1 4.00 2009-03-01
004 1 5.00 2009-02-01
005 2 3.00 2009-04-30
006 2 2.00 2009-04-01
007 2 4.00 2009-03-01
008 2 5.00 2009-02-01
=====================================
要求同一个pid下的日期最新的记录的列表，结果入如下：
bpid pid price crtime
001 1 3.00 2009-05-01
005 2 3.00 2009-04-30

由于数据量比较大，求一个比较高效的sql语句！！

[aimee_99]

SELECT * FROM TB T
WHERE NOT EXISTS(SELECT 1 FROM TB WHERE PID=T.PID AND crtime>T.crtime)

[sgtzzc]

select *
from tb t
where not exists(select 1 from tb where pid=t.pid and crtime>t.crtime)

[aimee_99]

--> Title  : Generating test data [tb]
--> Author : 各位大大,本大俠只想要顆星星
--> Date   : 2009-11-30 11:39:41
if object_id('[tb]') is not null drop table [tb]
go
create table [tb] (bpid nvarchar(6),pid int,price numeric(3,2),crtime datetime)
insert into [tb]
select '001',1,3.00,'2009-05-01' union all
select '002',1,2.00,'2009-04-01' union all
select '003',1,4.00,'2009-03-01' union all
select '004',1,5.00,'2009-02-01' union all
select '005',2,3.00,'2009-04-30' union all
select '006',2,2.00,'2009-04-01' union all
select '007',2,4.00,'2009-03-01' union all
select '008',2,5.00,'2009-02-01'
SELECT * FROM TB T
WHERE NOT EXISTS(SELECT 1 FROM TB WHERE PID=T.PID AND crtime>T.crtime)
/*
bpid   pid         price                                   crtime
------ ----------- --------------------------------------- -----------------------
001    1           3.00                                    2009-05-01 00:00:00.000
005    2           3.00                                    2009-04-30 00:00:00.000


*/

[--小F--]

SELECT * FROM TB T
WHERE crtime=(SELECT max(crtime) FROM TB WHERE PID=T.PID )

[--小F--]

--抄袭AMM的数据
--> Title  : Generating test data [tb]
--> Author : 各位大大,本大俠只想要顆星星
--> Date   : 2009-11-30 11:39:41
if object_id('[tb]') is not null drop table [tb]
go
create table [tb] (bpid nvarchar(6),pid int,price numeric(3,2),crtime datetime)
insert into [tb]
select '001',1,3.00,'2009-05-01' union all
select '002',1,2.00,'2009-04-01' union all
select '003',1,4.00,'2009-03-01' union all
select '004',1,5.00,'2009-02-01' union all
select '005',2,3.00,'2009-04-30' union all
select '006',2,2.00,'2009-04-01' union all
select '007',2,4.00,'2009-03-01' union all
select '008',2,5.00,'2009-02-01'

SELECT * FROM TB T
WHERE crtime=(SELECT max(crtime) FROM TB WHERE PID=T.PID )

/*bpid   pid         price                                   crtime
------ ----------- --------------------------------------- -----------------------
005    2           3.00                                    2009-04-30 00:00:00.000
001    1           3.00                                    2009-05-01 00:00:00.000

(2 行受影响)
*/

[sgtzzc]

[Quote=引用 3 楼 aimee_99 的回复:]
SQL code--> Title : Generating test data [tb]
--> Author : 各位大大,本大俠只想要顆星星--> Date : 2009-11-30 11:39:41ifobject_id('[tb]')isnotnulldroptable[tb]gocreatetable[tb] (bpidnvarchar(6),pidint,price nume?-
[/Quote]

[jylijie]

回帖有分

[dawugui]

[Quote=引用楼主 jzjrod 的回复:]
有以下数据：
bpid pid price crtime
001  1  3.00  2009-05-01
002  1  2.00  2009-04-01
003  1  4.00  2009-03-01
004  1  5.00  2009-02-01
005  2  3.00  2009-04-30
006  2  2.00  2009-04-01
007  2  4.00  2009-03-01
008  2  5.00  2009-02-01
=====================================
要求同一个pid下的日期最新的记录的列表，结果入如下：
bpid pid price crtime
001  1  3.00  2009-05-01
005  2  3.00  2009-04-30

由于数据量比较大，求一个比较高效的sql语句！！

[/Quote]

select t.* from tb t where crtime = (select max(crtime) from tb where pid = t.pid) order by t.pid

select t.* from tb t where not exists (select 1 from tb where pid = t.pid and crtime > t.crtime) order by t.pid