SQL 获取每个项目每个阶段的最新版本

USE Test1
GO
--创建表--
CREATE TABLE Test_Project
(
	ProjectVersionID UNIQUEIDENTIFIER PRIMARY KEY,
	ProjectID UNIQUEIDENTIFIER,
	ProjectName NVARCHAR(200),
	DataStage INT,
	Version INT,
	Status INT
)
--插入--
INSERT INTO Test_Project(ProjectVersionID,ProjectID,ProjectName,DataStage,Version,Status)
VALUES('87E9D2F7-677C-42C4-8428-4F41C82A9792','BA5E9ACA-C236-4B11-A523-1619A303172D','项目1',1,1,1);
INSERT INTO Test_Project(ProjectVersionID,ProjectID,ProjectName,DataStage,Version,Status)
VALUES('389D2FC8-3369-4EFF-9A5C-B3893DC2D5A4','BA5E9ACA-C236-4B11-A523-1619A303172D','项目1',1,2,1);
INSERT INTO Test_Project(ProjectVersionID,ProjectID,ProjectName,DataStage,Version,Status)
VALUES('14DC6E8A-6499-475C-A59A-0A643B9D3AD8','BA5E9ACA-C236-4B11-A523-1619A303172D','项目1',1,3,1);
INSERT INTO Test_Project(ProjectVersionID,ProjectID,ProjectName,DataStage,Version,Status)
VALUES('E7B11541-32C6-42E0-8F11-0F0196E6EB48','BA5E9ACA-C236-4B11-A523-1619A303172D','项目1',2,4,1);

INSERT INTO Test_Project(ProjectVersionID,ProjectID,ProjectName,DataStage,Version,Status)
VALUES('A4034734-2D85-4553-8C88-2BE22546D028','195900F2-92FD-48CE-8280-9B502B605D01','项目2',1,1,1);
INSERT INTO Test_Project(ProjectVersionID,ProjectID,ProjectName,DataStage,Version,Status)
VALUES('23EB8992-2A76-4891-9E3F-04AA97D77D60','195900F2-92FD-48CE-8280-9B502B605D01','项目2',1,2,1);
INSERT INTO Test_Project(ProjectVersionID,ProjectID,ProjectName,DataStage,Version,Status)
VALUES('39CFC1D6-61A3-427E-9EEF-C56F3A68ACCA','195900F2-92FD-48CE-8280-9B502B605D01','项目2',2,3,1);
INSERT INTO Test_Project(ProjectVersionID,ProjectID,ProjectName,DataStage,Version,Status)
VALUES('E4DCB41E-E44D-416F-B05A-D9AE6473C5D3','195900F2-92FD-48CE-8280-9B502B605D01','项目2',2,4,1);

INSERT INTO Test_Project(ProjectVersionID,ProjectID,ProjectName,DataStage,Version,Status)
VALUES('BE7E8975-8164-48FC-A85F-26839202004E','54F2103D-4F87-4345-A009-36828AD80636','项目3',1,1,1);
INSERT INTO Test_Project(ProjectVersionID,ProjectID,ProjectName,DataStage,Version,Status)
VALUES('B68C25C5-0D1B-463D-A14C-1BDF4968F23D','54F2103D-4F87-4345-A009-36828AD80636','项目3',1,2,1);
INSERT INTO Test_Project(ProjectVersionID,ProjectID,ProjectName,DataStage,Version,Status)
VALUES('7D96F133-7F7D-4248-97D1-FEFCC62A5BDC','54F2103D-4F87-4345-A009-36828AD80636','项目3',2,3,1);
INSERT INTO Test_Project(ProjectVersionID,ProjectID,ProjectName,DataStage,Version,Status)
VALUES('84BC90B7-94BC-45C1-94B6-44C22F9BA0A4','54F2103D-4F87-4345-A009-36828AD80636','项目3',2,4,0);

--普通查询--
SELECT * FROM Test_Project 
WHERE Status=1
ORDER BY ProjectName,DataStage,Version
--获取每个项目每个阶段的最新版本--
SELECT tp.* FROM Test_Project tp
WHERE tp.Status=1
AND tp.Version=
(
	SELECT MAX(Version) FROM Test_Project WHERE ProjectID=tp.ProjectID AND DataStage=tp.DataStage AND Status=1
)
ORDER BY tp.ProjectName,tp.DataStage,tp.Version
--获取每个项目的最新版本--
SELECT tp.* FROM Test_Project tp
WHERE tp.Status=1
AND tp.Version=(SELECT MAX(Version) FROM Test_Project WHERE ProjectID=tp.ProjectID AND Status=1)
ORDER BY tp.ProjectName,tp.DataStage,tp.Version
SELECT NEWID()
SELECT NEWID()
SELECT NEWID()
SELECT NEWID()
--删除表--
--DROP TABLE Test_Project