在 Postgres 的 WHERE 和 JOIN 子句中使用其他表時避免重複

我有 3 張桌子（devices, shops, device_shops_versions）。devices可以有很多device_shops_versions。

device_shops_versions table:

+----+---------+-----------+---------+---------------------------+
| id | version | device_id | shop_id | updated_at                |
+----+---------+-----------+---------+---------------------------+
| 1  | 113     | 1         | 1       | 2014-05-05 17:03:25.04055 |
| 2  | 702     | 1         | 1       | 2015-05-05 17:03:25.04055 |
| 3  | 410     | 2         | 1       | 2014-05-30 09:29:44.88214 |
| 4  | 440     | 4         | 2       | 2013-06-30 08:28:42.98214 |
+----+---------+-----------+---------+---------------------------+

1）我想返回所有使用特定device_id的設備。device_shops_versions``shop_id

2）我想顯示最近version的每台設備中device_shops_versions的最新版本updated_at。

我應該有類似的東西：

+----+--------------+---------+-----------+---------+--------------------------+
| id | device_model | version | device_id | shop_id | updated_at               |
+----+--------------+---------+-----------+---------+--------------------------+
| 1  | 'iphone'     | 702     | 1         | 1       | 2015-05-05 17:03:25.040  |
| 2  | 'test'       | 410     | 2         | 1       | 2014-05-30 09:28:44.982  |
+----+--------------+---------+-----------+---------+--------------------------+

我的查詢看起來像這樣，但我不確定我是否正確，而且我想知道是否可以避免重複：

SELECT *
FROM devices
INNER JOIN
 (SELECT device_shops_versions.device_id,
         MAX(device_shops_versions.updated_at)
  FROM device_shops_versions
  GROUP BY device_id ) dcv ON devices.id = dcv.device_id
WHERE devices.id IN
   (SELECT device_shops_versions.device_id
    FROM device_shops_versions
    WHERE device_shops_versions.shop_id = 1);

使用DISTINCT ON，它更短，更簡單，可能也更快：

SELECT DISTINCT ON (device_id) *
FROM   devices_shop_versions
WHERE  shop_id = 1
ORDER  BY device_id, updated_at DESC NULLS LAST;

詳細解釋：

• 在每個 GROUP BY 組中選擇第一行？

如果您的表很大，每個表有很多device_id行並且您需要優化性能，那麼遞歸 CTE 可能會更快。詳細說明：

• 優化 GROUP BY 查詢以檢索每個使用者的最新記錄

使其成為子查詢以集成到更大的查詢中：

SELECT mv.id, d.device_name, u.user_name, mv.version, mv.device_id, mv.shop_id, mv.updated_at
FROM  (
  -- query from above
  ) mv
JOIN   devices d on d.id = mv.device_id
JOIN   users   u on u.id = d.user_id
-- ORDER BY ???;  -- optional

在 rextester 上展示。

在排除不相關的行之後加入更多表通常更便宜。

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