brokerid zc date
001 1000.00 20100401
001 1000.00 20100402
002 2000.00 20100403
002 2000.00 20100404需要的结果
brokerid zc date
001 1000.00 20100401002 2000.00 20100403
001 1000.00 20100401
001 1000.00 20100402
002 2000.00 20100403
002 2000.00 20100404需要的结果
brokerid zc date
001 1000.00 20100401002 2000.00 20100403
where not exists (select 1 from tb B where A.brokerid=B.brokerid and A.zc=B.zc and A.date<B.date)
select * from tb t where
not exists
(
select 1 from tb where brokerid=t.brokerid and date<t.date
)
--2
select * from tb t where date=
(
select min(date) from tb where brokerid=t.brokerid
)
go
create table tb
(
brokerid varchar(10),
zc decimal(19,2),
date datetime
)
insert into tb select '001',1000.00,'20100401'
insert into tb select '001',1000.00,'20100402'
insert into tb select '002',2000.00,'20100403'
insert into tb select '002',2000.00,'20100404'
goselect * from tb b
where not exists(select * from tb where brokerid=b.brokerid and date<b.date)
brokerid zc date
---------- --------------------------------------- -----------------------
001 1000.00 2010-04-01 00:00:00.000
002 2000.00 2010-04-03 00:00:00.000(2 行受影响)
(
brokerid varchar(20),
zc float,
date varchar(20)
)insert into a
select '001',1000.00,'20100401' union all
select '001',1000.00,'20100402' union all
select '002',2000.00,'20100403' union all
select '002',2000.00,'20100404'select * from a where date in(select min(date) from a group by brokerid)
group by brokerid, zc
go
create table tb
(
brokerid varchar(10),
zc decimal(19,2),
date datetime
)
insert into tb select '001',1000.00,'20100401'
insert into tb select '001',1000.00,'20100402'
insert into tb select '002',2000.00,'20100403'
insert into tb select '002',2000.00,'20100404'
goSELECT DISTINCT brokerid ,zc ,max(date)OVER(PARTITION BY brokerid )[mdate]
FROM tb t brokerid zc mdate
---------- --------------------------------------- -----------------------
001 1000.00 2010-04-02 00:00:00.000
002 2000.00 2010-04-04 00:00:00.000(2 row(s) affected)
if object_id('tb') is not null drop table tb
go
create table tb
(
brokerid varchar(10),
zc decimal(19,2),
date datetime
)
insert into tb select '001',1000.00,'20100401'
insert into tb select '001',1000.00,'20100402'
insert into tb select '002',2000.00,'20100403'
insert into tb select '002',2000.00,'20100404'
goSELECT DISTINCT brokerid ,zc ,min(date)OVER(PARTITION BY brokerid )[mdate]
FROM tb t brokerid zc mdate
---------- --------------------------------------- -----------------------
001 1000.00 2010-04-01 00:00:00.000
002 2000.00 2010-04-03 00:00:00.000(2 row(s) affected)