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

USE Test1GO--创建表--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=1ORDER BY ProjectName,DataStage,Version--获取每个项目每个阶段的最新版本--SELECT tp.* FROM Test_Project tpWHERE tp.Status=1AND 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 tpWHERE tp.Status=1AND tp.Version=(SELECT MAX(Version) FROM Test_Project WHERE ProjectID=tp.ProjectID AND Status=1)ORDER BY tp.ProjectName,tp.DataStage,tp.VersionSELECT NEWID()SELECT NEWID()SELECT NEWID()SELECT NEWID()--删除表----DROP TABLE Test_Project