例如Staff表有ID, Name, Date, Dype
001 aaa 2013-01-01 a
001 aaa 2013-01-02 b
002 bbb 2013-01-01 a
002 bbb 2013-01-02 a怎样能查出这样记录?,如下:
001 aaa 2013-01-01 a
002 bbb 2013-01-01 a
这个是按ID,Name分组查询,Date,Dype列显示对应第一行记录?
001 aaa 2013-01-01 a
001 aaa 2013-01-02 b
002 bbb 2013-01-01 a
002 bbb 2013-01-02 a怎样能查出这样记录?,如下:
001 aaa 2013-01-01 a
002 bbb 2013-01-01 a
这个是按ID,Name分组查询,Date,Dype列显示对应第一行记录?
select * from cte where xh = 1;
WITH cte
AS ( SELECT row_number() OVER ( PARTITION BY ID, Name ORDER BY Date ) AS xh ,
*
FROM Staff
)
SELECT ID, Name, Date, Dype
FROM cte
WHERE xh = 1 ;
select * from tb a
where not exists
(select 1 from tb where id=a.id and Date<a.Date)