查询两种不同的场景

Question

我需要编写一个查询，实现以下要求

1.) Need to get one row for the combination of "PrimaryID1" and "PrimaryID2", there are more than one field for this combination.
2.) There are two sub conditions here we need to take in to account.
    2.a) For fields with "FlagField = 1"
         In some combination of "PrimaryID1" and "PrimaryID2" we have "FlagField = 1" in this case  only that particular field needs to be taken
    2.b) For fields with "FlagField IS NULL"
         If "FlagField IS NULL" then the requirement is like we need to pull the record for min of "PrimaryID3"(its a diff. field)

下面是数据的样子

-----------------------------------------------------------------------------------------------
PrimaryID1       PrimaryID2       PrimaryID3       FlagField       DataField1       DataField2
-----------------------------------------------------------------------------------------------
PPID11             PPID21           1                  0             DF111            DF211
PPID11             PPID21           2                  1             DF112            DF212
PPID11             PPID21           3                  0             DF113            DF213
PPID12             PPID22           1                 NULL           DF121            DF221
PPID12             PPID22           2                 NULL           DF122            DF222
PPID12             PPID22           3                 NULL           DF123            DF223

样本结果

-----------------------------------------------------------------------------------------------
PrimaryID1       PrimaryID2       PrimaryID3       FlagField       DataField1       DataField2
-----------------------------------------------------------------------------------------------
PPID11             PPID21           2                  1             DF112            DF212
PPID12             PPID22           1                 NULL           DF121            DF221

Answer 1

您可以使用ROW_NUMBER来标识具有最低PrimaryID3的行。将其包装在case语句中将允许您将其与FlagField"有条件地组合"。然后，您只需要对表达式的结果进行过滤。

WITH cte AS
(
    SELECT *, r = CASE WHEN FlagField IS NOT NULL THEN FlagField ELSE ROW_NUMBER() OVER(PARTITION BY PrimaryID1, PrimaryID2 ORDER BY CAST(REPLACE(PrimaryID3, 'PPID', '') AS INT)) END
    FROM tbl
)
SELECT *
FROM cte
WHERE r = 1