ID ProjectID Version ProjectName
1 1 1 P1
2 1 2 P1
3 1 3 P1
4 2 1 P2
5 2 2 P2
6 3 1 P3
我想要的结果是不同ProjectID中Version最大的那些数据:
ID ProjectID Version ProjectName
3 1 3 P1
5 2 2 P2
6 3 1 P3
1 1 1 P1
2 1 2 P1
3 1 3 P1
4 2 1 P2
5 2 2 P2
6 3 1 P3
我想要的结果是不同ProjectID中Version最大的那些数据:
ID ProjectID Version ProjectName
3 1 3 P1
5 2 2 P2
6 3 1 P3
*
from
tb t
where
not exists(select 1 from tb where ProjectID=t.ProjectID and Version>t.Version)
where not exists
(
select 1 from tb where t.ProjectID=ProjectId and t.ProjectName=ProjectName and
p.Version<Version
)
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-10-25 21:37:23
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[ProjectID] int,[Version] int,[ProjectName] varchar(2))
insert [tb]
select 1,1,1,'P1' union all
select 2,1,2,'P1' union all
select 3,1,3,'P1' union all
select 4,2,1,'P2' union all
select 5,2,2,'P2' union all
select 6,3,1,'P3'
--------------开始查询--------------------------
select
*
from
tb t
where
not exists(select 1 from tb where ProjectID=t.ProjectID and Version>t.Version)
----------------结果----------------------------
/*ID ProjectID Version ProjectName
----------- ----------- ----------- -----------
3 1 3 P1
5 2 2 P2
6 3 1 P3(3 行受影响)
*/
select *from tb t
where not exists(select *from tb where ProjectID=t.ProjectID and Version>t.Version)
declare @tb table([ID] int,[ProjectID] int,[Version] int,[ProjectName] varchar(2))
insert @tb
select 1,1,1,'P1' union all
select 2,1,2,'P1' union all
select 3,1,3,'P1' union all
select 4,2,1,'P2' union all
select 5,2,2,'P2' union all
select 6,3,1,'P3'select * from @tb t where not exists(select 1 from @tb where ProjectID=t.ProjectID and Version>t.Version)select * from @tb t where Version=(select max(Version) from @tb where ProjectID=t.ProjectID) order by id/*
ID ProjectID Version ProjectName
----------- ----------- ----------- -----------
3 1 3 P1
5 2 2 P2
6 3 1 P3
*/
*
from
表名 as a
where not exists
(select 1 from 表名 where ProjectID=a.ProjectID and Version>a.Version)
where not exists
(
select 1 from tb where t.ProjectID=ProjectId and t.ProjectName=ProjectName and
p.Version <Version
)
declare @tb table([ID] int,[ProjectID] int,[Version] int,[ProjectName] varchar(2))
insert @tb
select 1,1,1,'P1' union all
select 2,1,2,'P1' union all
select 3,1,3,'P1' union all
select 4,2,1,'P2' union all
select 5,2,2,'P2' union all
select 6,3,1,'P3'select * from @tb a where
not exists(select 1 from @tb where a.[ProjectName] = [ProjectName] and id > a.id )
---------------------------
ID ProjectID Version ProjectName
----------- ----------- ----------- -----------
3 1 3 P1
5 2 2 P2
6 3 1 P3(3 行受影响)
declare @tb table([ID] int,[ProjectID] int,[Version] int,[ProjectName] varchar(2))
insert @tb
select 1,1,1,'P1' union all
select 2,1,2,'P1' union all
select 3,1,3,'P1' union all
select 4,2,1,'P2' union all
select 5,2,2,'P2' union all
select 6,3,1,'P3'select * from @tb a where id in
(select max(id) from @tb where a.[ProjectName] = [ProjectName])
order by id
-------------------------------------------
ID ProjectID Version ProjectName
----------- ----------- ----------- -----------
3 1 3 P1
5 2 2 P2
6 3 1 P3(3 行受影响)