sql - SQL 中的复杂 RANK

Question

我有一个复杂的查询，它真的超出了我的头脑。我认为需要 RANK 的 RANK，但必须有更好的现有方式。

这里我有一个简单的表:

Manufacturer    DateOF  Status              Prefer
Dell            05-2014 ComputerInstalled   30
Dell            05-2014 ComputerUninstalled 70
Dell            05-2014 ComputerUninstalled 70
Dell            05-2014 ComputerUninstalled 70
Dell            05-2014 ComputerInstalled   30
Dell            05-2014 ComputerUninstalled 70
Dell            05-2014 ComputerNew         26
Dell            05-2014 ComputerNew         26
Dell            05-2014 ComputerInstalled   30
Dell            05-2014 ComputerInstalled   30

我需要做的是按 MANUFACTURER 和 DATEOF 列对表进行 GROUP BY，然后选择具有最低 PREFER 编号的行(在本例中为 26)。

使用 RANK 函数很容易:

SELECT sq.*
FROM
(
SELECT
*,
RANK() OVER (PARTITION BY Manufacturer,DateOF ORDER BY Prefer) AS RankPrefer
FROM
table1
WHERE
RankPrefer = 1
) sq

所以我将得到状态为 ComputerNew 的 2 行的结果。

Manufacturer    DateOF  Status              Prefer
Dell            05-2014 ComputerNew         26
Dell            05-2014 ComputerNew         26

这很简单，而不是问题。

问题是:

我必须执行以下规则:

如果具有最低 Prefer 值的行(例如:26)结果在他们的 Status 字段中有 ComputerNew 值，然后我必须包含更多具有 ComputerInstalled 值的行。

结果应该是这样的:

Manufacturer    DateOF  Status              Prefer
Dell            05-2014 ComputerInstalled   30
Dell            05-2014 ComputerInstalled   30
Dell            05-2014 ComputerNew         26
Dell            05-2014 ComputerNew         26
Dell            05-2014 ComputerInstalled   30
Dell            05-2014 ComputerInstalled   30

类似这个规则，我还有一个:

如果具有最低 Prefer 值的行(例如:26)结果在他们的 Status 字段中有 ComputerOld 值，然后我必须包含更多具有 ComputerUninstalled 值的行。

我认为 RANK of RANKING 可以解决这个问题，但现在我真的迷路了。

感谢任何帮助解决这个谜题。

谢谢

---

编辑1:

Gordon 的解决方案几乎不错，但并不完美。

我给你更多的测试数据，你可以看到哪里失败了。要测试的 SQLFiddle 是 here .

我在这里也包含了测试数据:

INSERT Table1 VALUES ('HP10011','04/01/2014','ComputerUninstalled',70)
INSERT Table1 VALUES ('HP10011','04/04/2014','ComputerOld',26)
INSERT Table1 VALUES ('HP10011','04/04/2014','ComputerOld',26)
INSERT Table1 VALUES ('HP10011','04/30/2014','ComputerUninstalled',70)
INSERT Table1 VALUES ('HP10011','05/23/2014','QuickDispose',10)
INSERT Table1 VALUES ('HP10011','06/03/2014','QuickDispose',10)
INSERT Table1 VALUES ('HP10077','04/01/2014','ComputerUninstalled',70)
INSERT Table1 VALUES ('HP1910','04/25/2014','QuickDispose',10)
INSERT Table1 VALUES ('HP1910','05/01/2014','ComputerInstalled',30)
INSERT Table1 VALUES ('HP1910','05/01/2014','ComputerInstalled',30)
INSERT Table1 VALUES ('HP1910','05/01/2014','ComputerInstalled',30)
INSERT Table1 VALUES ('HP1910','05/01/2014','ComputerInstalled',30)
INSERT Table1 VALUES ('HP1910','05/01/2014','ComputerUninstalled',70)
INSERT Table1 VALUES ('HP1910','05/01/2014','ComputerUninstalled',70)
INSERT Table1 VALUES ('HP1910','05/01/2014','ComputerUninstalled',70)
INSERT Table1 VALUES ('HP1910','05/01/2014','ComputerUninstalled',70)
INSERT Table1 VALUES ('HP1910','05/02/2014','ComputerInstalled',30)
INSERT Table1 VALUES ('HP1910','05/02/2014','ComputerInstalled',30)
INSERT Table1 VALUES ('HP3720','05/07/2014','ComputerInstalled',30)
INSERT Table1 VALUES ('HP3720','05/07/2014','ComputerInstalled',30)
INSERT Table1 VALUES ('HP3720','05/07/2014','ComputerUninstalled',70)
INSERT Table1 VALUES ('HP3720','05/07/2014','ComputerUninstalled',70)
INSERT Table1 VALUES ('HP3720','05/07/2014','ComputerUninstalled',70)
INSERT Table1 VALUES ('HP3720','05/07/2014','ComputerUninstalled',70)
INSERT Table1 VALUES ('HP3720','05/08/2014','ComputerInstalled',30)
INSERT Table1 VALUES ('HP3720','05/08/2014','ComputerInstalled',30)
INSERT Table1 VALUES ('HP3720','05/08/2014','ComputerInstalled',30)
INSERT Table1 VALUES ('HP3720','05/08/2014','ComputerUninstalled',70)
INSERT Table1 VALUES ('HP3720','06/06/2014','ComputerUninstalled',70)
INSERT Table1 VALUES ('HP3720','06/06/2014','ComputerUninstalled',70)
INSERT Table1 VALUES ('HP3720','06/10/2014','ComputerOld',26)
INSERT Table1 VALUES ('HP3720','06/10/2014','ComputerUninstalled',70)
INSERT Table1 VALUES ('HP3720','06/10/2014','ComputerUninstalled',70)
INSERT Table1 VALUES ('HP3720','06/11/2014','ComputerOld',26)
INSERT Table1 VALUES ('HP3720','06/11/2014','ComputerUninstalled',70)
INSERT Table1 VALUES ('HP3720','06/11/2014','ComputerUninstalled',70)

查询返回 ComputerInstalled 和 ComputerUninstalled 两行对于以下数据:

'HP1910','05/01/2014','ComputerInstalled',30
'HP1910','05/01/2014','ComputerUninstalled',70

它应该只选择 ComputerInstalled，因为对于那个 Manufacturer，在同一个月，它应该选择最低的 Prefer (30)。

这个数据集的结果应该是这样的:

Manufacturer    DateOF  Status          Prefer
HP10011 2014-04-01  ComputerUninstalled 70
HP10011 2014-04-04  ComputerOld         26
HP10011 2014-04-04  ComputerOld         26
HP10011 2014-04-30  ComputerUninstalled 70
HP10011 2014-05-23  QuickDispose        10
HP10011 2014-06-03  QuickDispose        10
HP10077 2014-04-01  ComputerUninstalled 70
HP1910  2014-04-25  QuickDispose        10
HP1910  2014-05-01  ComputerInstalled   30
HP1910  2014-05-01  ComputerInstalled   30
HP1910  2014-05-01  ComputerInstalled   30
HP1910  2014-05-01  ComputerInstalled   30
HP3720  2014-05-07  ComputerInstalled   30
HP3720  2014-05-07  ComputerInstalled   30
HP3720  2014-05-08  ComputerInstalled   30
HP3720  2014-05-08  ComputerInstalled   30
HP3720  2014-05-08  ComputerInstalled   30
HP3720  2014-06-06  ComputerUninstalled 70
HP3720  2014-06-06  ComputerUninstalled 70
HP3720  2014-06-10  ComputerOld         26
HP3720  2014-06-10  ComputerUninstalled 70
HP3720  2014-06-10  ComputerUninstalled 70
HP3720  2014-06-11  ComputerOld         26
HP3720  2014-06-11  ComputerUninstalled 70
HP3720  2014-06-11  ComputerUninstalled 70

Answer 1

这是一个想法。找出行的偏好排名。然后使用 exists 确定 rank = 1 的行是否满足您的条件。

最终的查询看起来像:

with r as (
      select t.*,
             rank() over (partition by manufacturer, dateof order by Prefer) as seqnum
      from table1 t
     ),
     r1 as (
      select r.*
      from r
      where seqnum = 1
     )
select r.*
from r
where r.seqnum = 1 or
      (exists (select 1 from r1 where status = 'ComputerNew' and r1.dateof = r.dateof) and r.status = 'ComputerInstalled' or
       exists (select 1 from r1 where status = 'ComputerOld' and r1.dateof = r.dateof) and r.status = 'ComputerUninstalled'
      );