假設我有一個包含三列的 PET 表：名稱、類型和日期。我想按日期對三列進行排序，但只顯示特定特定類型（本例中為“狗”）的最新的一個，其餘的都顯示。例如，我的原始數據列表：

Name        Type        Date
Nemo        Fish        June 1, 2016
Fido        Dog         January 1, 2016
Felix       Dog         February 1, 2016
Whiskers    Cat         April 1, 2016
Marlin      Fish        August 1, 2016
Shifu       Cat         March 3, 2016

應用查詢時看起來像這樣。

Felix       Dog         February 1, 2016
Shifu       Cat         March 3, 2016
Whiskers    Cat         April 1, 2016
Nemo        Fish        June 1, 2016
Marlin      Fish        August 1, 2016

我確信有多種方法可以做到這一點，但從效率的角度來看，只有少數是最佳的。

解決方案 1

CREATE TABLE pet ( name text, type text, date date);

INSERT INTO pet VALUES
('Nemo', 'Fish', 'June 1, 2016'),
('Fido', 'Dog', 'January 1, 2016'),
('Felix', 'Dog', 'February 1, 2016'),
('Whiskers', 'Cat', 'April 1, 2016'),
('Marlin', 'Fish', 'August 1, 2016'),
('Shifu', 'Cat', 'March 3, 2016');

使用PostgreSQL 特定的 DISTINCT ON 子句，我們可以在單個表掃描中做到這一點：

SELECT name, type, date
FROM (
 SELECT DISTINCT ON (
   type,
   CASE WHEN type<>'Dog' THEN date END
 )
 name, type, date
 FROM pet
 ORDER BY
   type,
   CASE WHEN type<>'Dog' THEN date END,
   date DESC
) AS subq1
ORDER BY date;

解決方案 2

使用更多 ANSI 兼容的 SQL，如UNION和 LIMIT。它將適用於 MySQL、DB2 和其他一些。類似的解決方案可以在 Oracle 上完成，只需將 LIMIT 替換為 ROWNUM。

(
 SELECT * FROM pet
 WHERE type = 'Dog'
 ORDER BY date DESC
 LIMIT 1
) UNION ALL (
 SELECT * FROM pet
 WHERE type <> 'Dog'
)
ORDER BY date;

性能說明

對於小表（少於 1000000 行），任何解決方案都可以使用。DISTINCT ON稍微快一點：

Iterations: 100000
Query:   /*q1*/ SELECT name, type, date FROM ( SELECT DISTINCT ON ( type, CASE WHEN type<>'Dog' THEN date END ) name, type, date FROM pet ORDER BY type, CASE WHEN type<>'Dog' THEN date END, date DESC ) AS subq1 ORDER BY date; 
Time:    24.834 s (31%)
Average: 0.248 ms
Rows:    500000
Winner:  80914 times (80%)
Query:   /*q2*/ ( SELECT * FROM pet WHERE type = 'Dog' ORDER BY date DESC LIMIT 1 ) UNION ALL ( SELECT * FROM pet WHERE type <> 'Dog' ) ORDER BY date; 
Time:    26.778 s (33%)
Average: 0.268 ms
Rows:    500000
Winner:  12881 times (12%)
Query:   /*q3*/ select name, type, date from ( select pet.*, case when type = 'Dog' then rank() over (partition by type order by date desc) else 1 end as rnk from pet ) as x where rnk = 1 order by date; 
Time:    27.490 s (34%)
Average: 0.275 ms
Rows:    500000
Winner:  6205 times (6%)

使用“大數據”，確保您的查詢只進行一次索引表掃描。

引用自：https://dba.stackexchange.com/questions/184356