postgresql - postgresql 和子查询作为列（重复行）

Question

我在使用子查询作为列的 postgresql 查询时遇到了一些问题。我不是 DBA，因为我通常依靠 ORM 来处理我的数据库内容，所以请耐心等待.....这是我的缩写表定义：

\d profile
                                           Table "public.profile"
          Column           |           Type           |                      Modifiers
---------------------------+--------------------------+------------------------------------------------------
 id                        | integer                  | not null default nextval('profile_id_seq'::regclass)
 _tracking_number_obj_id   | integer

\d trackingnumber;
                                     Table "public.trackingnumber"
     Column      |         Type          |                          Modifiers
-----------------+-----------------------+-------------------------------------------------------------
 id              | integer               | not null default nextval('trackingnumber_id_seq'::regclass)
 tracking_number | character varying(16) | not null

 \d dd;
                                    Table "public.dd"
     Column     |         Type          |                         Modifiers
----------------+-----------------------+-----------------------------------------------------------
 id             | integer               | not null default nextval('dd_id_seq'::regclass)
 created_date   | date                  | not null
 completed      | boolean               | not null
 completed_date | date                  |
 type           | character varying(10) | not null
 profile_id     | integer               | not null

我正在尝试的查询：

select tn.tracking_number,
last_c,
last_l
from 
trackingnumber as tn, profile as p, dd as dd,
(select max(completed_date) from dd as dd, profile as p where profile_id = p.id and type='C') as last_c,
(select max(completed_date) from dd as dd, profile as p where profile_id = p.id and type='L') last_l
where p._tracking_number_obj_id = tn.id and dd.profile_id = p.id;

预期结果将是每个 p.id 一行，但我为 dd 表中的每一行得到一行。

例子：

select count(*) from dd;
 count
-------
    10

上述查询的结果：

tracking_number | last_c  |  last_l
-----------------+--------------+--------------
 5004            | (2019-10-23) | (2019-10-23)
 5004            | (2019-10-23) | (2019-10-23)
 5004            | (2019-10-23) | (2019-10-23)
 5004            | (2019-10-23) | (2019-10-23)
 5004            | (2019-10-23) | (2019-10-23)
 5004            | (2019-10-23) | (2019-10-23)
 5004            | (2019-10-23) | (2019-10-23)
 5003            | (2019-10-23) | (2019-10-23)
 5003            | (2019-10-23) | (2019-10-23)
 5003            | (2019-10-23) | (2019-10-23)

期望的结果是：

tracking_number | last_c  |  last_l
-----------------+--------------+--------------
 5004            | (2019-10-23) | (2019-10-23)
 5003            | (2019-10-23) | (2019-10-23)

标签： postgresql

Answer 1

看起来加入dd是不必要的。

尝试删除

dd as dd,

和

and dd.profile_id = p.id

从查询中查看您是否更喜欢结果。