sql-server - 根据可以具有值或为空的字段选择组内的行

Question

对于每个 IssID FieldID 组，从下表中，我想选择包含最低 ChgGrpID 值的行，如果组中存在该字段为 NULL 的行，则仍然选择最低的非空值。如果仅存在带有 NULL 的行，则为该组选择该行。

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() 的任何数字都大，并且比 max() 的任何数字都小。这意味着您想要的行为是默认的。

第二个技巧是你不能加入空值，所以你需要使用 isnull()。 (如果 ID 可能为 -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