id Comid CompanyName Cname CreateDate flag
1 1 A a1 2010-8-1 1
2 1 A a2 2010-8-2 1
3 1 A a3 2010-8-3 0
4 1 A a4 2010-8-4 0
5 2 B b1 2010-8-1 1
6 2 B b2 2010-8-2 0
7 2 B b3 2010-8-3 1
8 2 B b4 2010-8-4 0
9 3 C c1 2010-8-1 0
10 3 C c2 2010-8-2 1
11 3 C c3 2010-8-3 1
12 3 C c4 2010-8-4 0Select结果 (flag=1、CreateDate最后)
2 1 A a2 2010-8-2 1
7 2 B b3 2010-8-3 1
11 3 C c3 2010-8-3 1
1 1 A a1 2010-8-1 1
2 1 A a2 2010-8-2 1
3 1 A a3 2010-8-3 0
4 1 A a4 2010-8-4 0
5 2 B b1 2010-8-1 1
6 2 B b2 2010-8-2 0
7 2 B b3 2010-8-3 1
8 2 B b4 2010-8-4 0
9 3 C c1 2010-8-1 0
10 3 C c2 2010-8-2 1
11 3 C c3 2010-8-3 1
12 3 C c4 2010-8-4 0Select结果 (flag=1、CreateDate最后)
2 1 A a2 2010-8-2 1
7 2 B b3 2010-8-3 1
11 3 C c3 2010-8-3 1
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (id int,Comid int,CompanyName varchar(1),Cname varchar(2),CreateDate datetime,flag int)
insert into #tb
select 1,1,'A','a1','2010-8-1',1 union all
select 2,1,'A','a2','2010-8-2',1 union all
select 3,1,'A','a3','2010-8-3',0 union all
select 4,1,'A','a4','2010-8-4',0 union all
select 5,2,'B','b1','2010-8-1',1 union all
select 6,2,'B','b2','2010-8-2',0 union all
select 7,2,'B','b3','2010-8-3',1 union all
select 8,2,'B','b4','2010-8-4',0 union all
select 9,3,'C','c1','2010-8-1',0 union all
select 10,3,'C','c2','2010-8-2',1 union all
select 11,3,'C','c3','2010-8-3',1 union all
select 12,3,'C','c4','2010-8-4',0select * from #tb t
where flag=1
and not exists(select 1 from #tb where flag=1 and CompanyName=t.CompanyName and CreateDate>t.CreateDate)id Comid CompanyName Cname CreateDate flag
----------- ----------- ----------- ----- ----------------------- -----------
2 1 A a2 2010-08-02 00:00:00.000 1
7 2 B b3 2010-08-03 00:00:00.000 1
11 3 C c3 2010-08-03 00:00:00.000 1(3 行受影响)
(
SELECT *,rn=row_number()OVER (PARTITION BY Comid ORDER BY CreateDate desc )
FROM tb
WHERE flag=1
)
SELECT id, Comid, CompanyName,Cname ,CreateDate ,flag
FROM cte
WHERE rn=1
from tb a
join
(select Comid,max(CreateDate) as CreateDate from tb where flag=1 group by comid) b
on a.comid=b.comid and a.CreateDate=b.CreateDate
where a.flag=1
order by id
(
SELECT *,rn=row_number()OVER (PARTITION BY Comid ORDER BY CreateDate desc )
FROM tb
WHERE flag=1
)
SELECT id, Comid, CompanyName,Cname ,CreateDate ,flag
FROM cte
WHERE rn=1
select
max(id) id,comid,max(CompanyName) CompanyName,max(Cname) Cname,
max(createdate) CreateDate,max(flag) flag
from #tb where flag=1
group by comid
where flag=1
group by id, Comid, CompanyName, Cname,flag