Question

在下表中,每个信道发组织外地信息数据库小组,如果这个领域仍然选择最低无价证券,我想选择哪一行。如果只有民族解放军的一行,该团体就选择该行。

create table #Projects
(ProjectID int, IssID int, PtID int, PTY varchar(10), TypeID int, TypeName varchar(20), FieldID int, FieldName varchar(20),         STRINGVALUE varchar(50), NUMBERVALUE int,ChgGrpID int,ChgGrpIssID int,ChgItemID int,ChgItemGrpID int,FIELD varchar(20),            NEWVALUE varchar(20), NEWSTRING varchar(20))
insert into #Projects values 
(10879,107930,3, Super ,22, A ,10648, ADH ,NULL,666,501040,107930,852895,501040, ADH ,NULL, 666 )
,(10879,107930,3, Super ,22, A ,10571, DLV , No ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
,(10879,107930,3, Super ,22, A ,10541, CMPLX , Large ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
,(10879,107930,3, Super ,22, A ,10542, EWF , Orange ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
,(10879,107930,3, Super ,22, A ,10654, WKFL , UAT ,NULL,501034,107930,852889,501034, WKFL , DVP , CRV )
,(10879,107930,3, Super ,22, A ,10654, WKFL , UAT ,NULL,501037,107930,852892,501037, WKFL , CRV , UAT )
,(10879,107930,3, Super ,22, A ,10654, WKFL , UAT ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
,(10879,107971,3, Super ,103, B ,10648, ADH ,NULL,999,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
,(10879,107971,3, Super ,103, B ,10571, DLV , No ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
,(10879,107971,3, Super ,103, B ,10541, CMPLX , Large ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
,(10879,107971,3, Super ,103, B ,10542, EWF , Orange ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
,(10879,107971,3, Super ,103, B ,10654, WKFL , UAT ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)


select * from #Projects 
order by IssID, FieldID

在此,我希望看到:

insert into #Projects values 
(10879,107930,3, Super ,22, A ,10648, ADH ,NULL,666,501040,107930,852895,501040, ADH ,NULL, 666 )
,(10879,107930,3, Super ,22, A ,10571, DLV , No ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
,(10879,107930,3, Super ,22, A ,10541, CMPLX , Large ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
,(10879,107930,3, Super ,22, A ,10542, EWF , Orange ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
,(10879,107930,3, Super ,22, A ,10654, WKFL , UAT ,NULL,501034,107930,852889,501034, WKFL , DVP , CRV )
,(10879,107971,3, Super ,103, B ,10648, ADH ,NULL,999,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
,(10879,107971,3, Super ,103, B ,10571, DLV , No ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
,(10879,107971,3, Super ,103, B ,10541, CMPLX , Large ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
,(10879,107971,3, Super ,103, B ,10542, EWF , Orange ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
,(10879,107971,3, Super ,103, B ,10654, WKFL , UAT ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)

Answer 1

第一种陷阱是,无效是最后一种由min取的价值观,其数量大于任何min(min)的数值,低于任何最高值。这意味着你想要的行为是缺席。

第二点是,你不能加入无效,因此,你需要使用的是核心。 (如果有可能有一个-1的身份证,那么你必须获得不同的替换价值。)

因此,我们发现我们的最低价值和关键的其他项目,然后将它当作一个分局,来夺走我们想要的几行:

Select p.* from #Projects p
join
(Select isnull(MIN(ChgGrpID),-1) as ChgGrpID, IssID, FieldID
from #Projects 
group by IssID, FieldID) X
on isnull(P.ChgGrpID,-1)=X.ChgGrpID and P.IssID=X.IssID and P.FieldID=X.FieldID

友情链接