主页 / dba / 问题 / 165560
Accepted
Mio
Asked: 2017-02-27 09:03:27 +0800 CST

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

  • 772

我有 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);
postgresql join

2 个回答

  1. Best Answer

    使用DISTINCT ON，它更短、更简单，也可能更快：

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

    详细解释：

    如果您的表很大，每行有很多device_id行并且您需要优化性能，那么递归 CTE 可能会更快。详细说明：

    使其成为一个子查询以集成到更大的查询中：

    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 上的演示。

    在排除不相关的行后加入更多的表通常更便宜。

    • 3 
  2. with maxVer as
(
    select id, version, device_id, shop_id, updated_at,
           row_number() over (partition by device_id order by version desc) rn
    from devices_shop_versions
    where shop_id = 1
)
select id, version, device_id, shop_id, updated_at
from   maxVer
where  rn = 1;

    添加按设备分区的行号，并按降序排列版本。

    row_number() over (partition by device_id order by version desc)

    然后获取 rn = 1 的行（最大版本）

    这是结果：

    +----+---------+-----------+---------+---------------------+
| id | version | device_id | shop_id | updated_at          |
+----+---------+-----------+---------+---------------------+
| 2  | 702     | 1         | 1       | 05.05.2015 00:00:00 |
+----+---------+-----------+---------+---------------------+
| 3  | 410     | 2         | 1       | 30.05.2014 00:00:00 |
+----+---------+-----------+---------+---------------------+

    在这里查看：http ://rextester.com/SXUMZ94595

    • 2

相关问题