获取ID和最大值ORACLE SQL

Question

我目前有以下内容：

TABLE "QUARTO":
CREATE TABLE Quarto (
Id                        number(2) NOT NULL, 
LotacaoMaxima             number(1) NOT NULL, 
TipoQuartoId              number(1) NOT NULL, 
NumeroQuartoNumSequencial number(3) NOT NULL, 
NumeroQuartoAndarId       varchar2(1) NOT NULL, 
PRIMARY KEY (Id));
TABLE RESERVA:
CREATE TABLE Reserva (
Id               number(3) NOT NULL, 
ClienteNif       number(9) NOT NULL, 
QuartoId         number(2) NOT NULL, 
DataInicio       date NOT NULL, 
DataFim          date NOT NULL, 
NumPessoas       number(1) NOT NULL, 
Estado           varchar2(15) NOT NULL, 
DataCancelamento date, 
PRIMARY KEY (Id));

我在两者中都有一些数据：

QUARTO:
| ID | LOTACAOMAXIMA | TIPOQUARTOID | NUMEROQUARTONUMSEQUENCIAL | NUMEROQUARTOANDARID |
| :--- | :--- | :--- | :--- | :--- |
| 1 | 1 | 1 | 1 | 1 |
| 2 | 1 | 1 | 2 | 1 |
| 3 | 1 | 1 | 3 | 1 |
| 4 | 1 | 1 | 4 | 1 |
| 5 | 1 | 1 | 5 | 1 |
| 6 | 1 | 1 | 6 | 1 |
| 7 | 1 | 1 | 7 | 1 |
| 8 | 1 | 1 | 8 | 1 |
| 9 | 1 | 1 | 9 | 1 |
| 10 | 1 | 1 | 10 | 1 |
| 11 | 2 | 2 | 11 | 1 |
| 12 | 2 | 2 | 12 | 1 |
| 13 | 2 | 2 | 13 | 1 |
| 14 | 2 | 2 | 14 | 1 |
| 15 | 2 | 2 | 15 | 1 |
| 16 | 2 | 2 | 16 | 1 |
| 17 | 2 | 2 | 17 | 1 |
| 18 | 2 | 2 | 18 | 1 |
| 19 | 2 | 2 | 19 | 1 |
| 20 | 2 | 2 | 20 | 1 |

RESERVA:
| ID | CLIENTENIF | QUARTOID | DATAINICIO | DATAFIM | NUMPESSOAS | ESTADO | DATACANCELAMENTO |
| :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- |
| 1 | 296837970 | 11 | 2020-06-01 00:00:00 | 2020-06-12 00:00:00 | 1 | Finalizada | NULL |
| 2 | 275784703 | 17 | 2020-06-13 00:00:00 | 2020-06-21 00:00:00 | 1 | Finalizada | NULL |
| 3 | 220347654 | 11 | 2020-07-07 00:00:00 | 2020-07-15 00:00:00 | 2 | Finalizada | NULL |
| 4 | 294772545 | 12 | 2020-08-01 00:00:00 | 2020-08-15 00:00:00 | 2 | Finalizada | NULL |
| 5 | 220347654 | 3 | 2020-01-01 00:00:00 | 2020-01-16 00:00:00 | 1 | Finalizada | NULL |

WITH CONTAGEM_QUARTO_POR_ID AS (SELECT q.ID, COUNT(r.QUARTOID) AS NUM_RESERVAS, q.TIPOQUARTOID, q.NUMEROQUARTOANDARID
FROM RESERVA r
INNER JOIN QUARTO q on q.ID = r.QUARTOID
GROUP BY q.ID, q.TIPOQUARTOID, q.NUMEROQUARTOANDARID)
SELECT t.TIPOQUARTOID, t.NUMEROQUARTOANDARID, MAX(t.NUM_RESERVAS) AS MAX
FROM CONTAGEM_QUARTO_POR_ID t
GROUP BY t.TIPOQUARTOID, t.NUMEROQUARTOANDARID

输出如下：

| TIPOQUARTOID | NUMEROQUARTOANDARID | MAX |
| :----------- | :------------------ | :-- |
| 1            |                   2 |   2 |
| 2            |                   1 |   8 |
| 1            |                   1 |   1 |

我想在显示当前数据的同时，还显示每行的toe ID，但当我将t.ID添加到SELECT时，它会迫使我将其添加到GROUP BY，输出为：

| TIPOQUARTOID | NUMEROQUARTOANDARID | MAX | ID |
| :----------- | :------------------ | :-- | :- |
| 2            | 1                   | 2   | 11 |
| 1            | 1                   | 1   | 1  |
| 1            | 1                   | 1   | 3  |
| 1            | 2                   | 2   | 21 |
| 2            | 1                   | 1   | 17 |
| 2            | 1                   | 1   | 12 |
| 2            | 1                   | 8   | 16 |

我只想得到最大值和与该最大值相关的ID。

Answer 1

您可以在查询中使用KEEP子句，而无需对其进行如下更改：

WITH CONTAGEM_QUARTO_POR_ID AS 
(SELECT q.ID, COUNT(r.QUARTOID) AS NUM_RESERVAS, q.TIPOQUARTOID, q.NUMEROQUARTOANDARID
FROM RESERVA r
INNER JOIN QUARTO q on q.ID = r.QUARTOID
GROUP BY q.ID, q.TIPOQUARTOID, q.NUMEROQUARTOANDARID)
SELECT t.TIPOQUARTOID, t.NUMEROQUARTOANDARID, MAX(t.NUM_RESERVAS) AS MAX, 
max(t.ID) keep(dense_rank first order by t.NUM_RESERVAS desc nulls last) as ID -- this
FROM CONTAGEM_QUARTO_POR_ID t
GROUP BY t.TIPOQUARTOID, t.NUMEROQUARTOANDARID

Answer 2

您需要NUM_RESERVAS列和PARTITION BY TIPOQUARTOID, NUMEROQUARTOANDARID的MAX() OVER ()分析函数，以便按列表(如(对分区内的列进行分组

WITH CONTAGEM_QUARTO_POR_ID AS
(
SELECT q.ID,
COUNT(r.QUARTOID) AS NUM_RESERVAS,
q.TIPOQUARTOID,
q.NUMEROQUARTOANDARID
FROM RESERVA r
JOIN QUARTO q
on q.ID = r.QUARTOID
GROUP BY q.ID, q.TIPOQUARTOID, q.NUMEROQUARTOANDARID
), t AS
(
SELECT t.TIPOQUARTOID, t.NUMEROQUARTOANDARID, t.NUM_RESERVAS, 
MAX(t.NUM_RESERVAS) 
OVER (PARTITION BY t.TIPOQUARTOID, t.NUMEROQUARTOANDARID) AS MAX, 
t.ID
FROM CONTAGEM_QUARTO_POR_ID t
)
SELECT TIPOQUARTOID, NUMEROQUARTOANDARID, NUM_RESERVAS, ID
FROM t
WHERE NUM_RESERVAS = MAX

或更直接地使用HAVING子句

SELECT q.TIPOQUARTOID,
q.NUMEROQUARTOANDARID,
COUNT(r.QUARTOID) AS NUM_RESERVAS,
q.ID
FROM RESERVA r
JOIN QUARTO q
on q.ID = r.QUARTOID
GROUP BY q.ID, q.TIPOQUARTOID, q.NUMEROQUARTOANDARID
HAVING COUNT(r.QUARTOID) = q.TIPOQUARTOID

Answer 3

我不建议使用两个级别的聚合。只需使用窗口功能：

WITH CONTAGEM_QUARTO_POR_ID AS (
SELECT q.ID, COUNT(*) AS NUM_RESERVAS, q.TIPOQUARTOID, q.NUMEROQUARTOANDARID,
ROW_NUMBER() OVER (PARTITION BY q.TIPOQUARTOID, q.NUMEROQUARTOANDARID ORDER BY COUNT(*) DESC) as seqnum
FROM RESERVA r INNER JOIN
QUARTO q 
ON q.ID = r.QUARTOID
GROUP BY q.ID, q.TIPOQUARTOID, q.NUMEROQUARTOANDARID
)
SELECT cq.*
FROM CONTAGEM_QUARTO_POR_ID cq
WHERE seqnum = 1;

我认为这会比两个聚合的性能稍好(但值得检查(。

这种方法的一个优点是它更灵活。如果您想要领带，只需在子查询中将ROW_NUMBER()更改为RANK()即可。

也许更重要的是，CCD_；成语"在SQL中，用于每个组返回一行(或特定数量的行(。学习如何使用它是非常有价值的。