删除查询中的重复结果

codevb 发表于 Dev

编码器

可以帮助我解决这个重复问题，它为同一条记录返回 1 个以上的结果我只想为每个 id 带来 1 个结果，并且只为每个记录带来最后的历史记录。

我的查询：

SELECT DISTINCT ON(tickets.ticket_id,ticket_histories.created_at)
        ticket.id AS ticket_id,
        tickets.priority,
        tickets.title,
        tickets.company,
        tickets.ticket_statuse,
        tickets.created_at AS created_ticket,
        group_user.id AS group_id,
        group_user.name AS user_group,
        ch_history.description AS ch_description,
        ch_history.created_at AS ch_history
      FROM
        tickets
      INNER JOIN company ON (company.id = tickets.company_id)
      INNER JOIN (SELECT id,
        tickets_id,
        description,
        user_id,
        MAX(tickets.created_at) AS created_ticket
       FROM
        ch_history
       GROUP BY id,
        created_at,
        ticket_id,
        user_id,
        description
      ORDER BY created_at DESC LIMIT 1) AS ch_history ON (ch_history.ticket_id = ticket.id)
      INNER JOIN users ON (users.id = ch_history.user_id)
      INNER JOIN group_users ON (group_users.id = users.group_user_id)
      WHERE company = 15
      GROUP BY
        tickets.id,
        ch_history.created_at DESC;

我的查询结果，但返回 3 或 5 个具有不同历史记录的相同 ID 我只想返回每张票的 1 个 ID，并且只返回每个滴答声的最后记录的历史记录

 ticket_id | priority |                title                | company_id | ticket_statuse |       created_ticket       |                     company                          |  user_group     | group_id |     ch_description      |        ch_history       
            -----------+------------+--------------------------------------+------------+-----------------+----------------------------+------------------------------------------------------+-----------------+----------+------------------------+----------------------------
                 49713 |          2 | REMOVE DATA                       |      1 | t               | 2019-12-09 17:50:35.724485 | SAME COMPANY                                         | people          |        5 | TEST 1                  | 2019-12-10 09:31:45.780667
                 49706 |          2 | INCLUDE DATA                      |      1 | f               | 2019-12-09 09:16:35.320708 | SAME COMPANY                                         | people          |        5 | TEST 2                  | 2019-12-10 09:38:52.769515
                 49706 |          2 | ANY TITLE                         |      1 | f               | 2019-12-09 09:16:35.320708 | SAME COMPANY                                         | people          |        5 | TEST 3                  | 2019-12-10 09:39:22.779473
                 49706 |          2 | NOTING ELSE MAT                   |      1 | f               | 2019-12-09 09:16:35.320708 | SAME COMPANY                                         | people          |        5 | TESTE 4                 | 2019-12-10 09:42:59.50332
                 49706 |          2 | WHITESTRIPES                      |      1 | f               | 2019-12-09 09:16:35.320708 | SAME COMPANY                                         | people          |        5 | TEST 5                  | 2019-12-10 09:44:30.675434

想返回如下

 ticket_id | priority |                title                | company_id | ticket_statuse |       created_ticket       |                     company                       |  user_group     | group_id |     ch_description      |        ch_history       
-----------+------------+--------------------------------------+------------+-----------------+----------------------------+------------------------------------------------------+-----------------+----------+------------------------+----------------------------
     49713 |          2 | REMOVE DATA                       |      1 | t               | 2019-12-09 17:50:10.724485 | SAME COMPANY                                         | people          |        5 | TEST 1                  | 2020-01-01 18:31:45.780667
     49707 |          2 | INCLUDE DATA                      |      1 | f               | 2019-12-11 19:22:21.320701 | SAME COMPANY                                         | people          |        5 | TEST 2                  | 2020-02-05 16:38:52.769515
     49708 |          2 | ANY TITLE                         |      1 | f               | 2019-12-15 07:15:57.320950 | SAME COMPANY                                         | people          |        5 | TEST 3                  | 2020-02-06 07:39:22.779473
     49709 |          2 | NOTING ELSE MAT                   |      1 | f               | 2019-12-16 08:30:28.320881 | SAME COMPANY                                         | people          |        5 | TESTE 4                 | 2020-01-07 11:42:59.50332
     49701 |          2 | WHITESTRIPES                      |      1 | f               | 2019-12-21 11:04:00.320450 | SAME COMPANY                                         | people          |        5 | TEST 5                  | 2020-01-04 10:44:30.675434

我想返回如下所示，看到字段 ch_description 和 ch_history 只带来最近的记录和列出的每张票中的最后一张，没有重复我想用这种方式带来的帮助可以帮助我。

火腿骨

有两件事让我眼前一亮：

您已将“创建于”列为“独特于”的一部分，这将本质上为每个票证 ID 提供多行（除非碰巧只有一个）
不同的 on 应该使对票证历史的子查询变得不必要......即使您选择这样做，您也会再次进入“创建于”列，这将为您提供多个结果。如果您选择这种方法，理想的子查询应该是按ticket_id 和仅按ticket_id 分组。

稍微相关：

子查询的另一种方法是分析函数（窗口函数），但我将其保存到另一天。

我认为您想要的查询，根据历史表的 created_at 字段，每个 ticket_id 将为您提供一行，如下所示：

select distinct on (t.id)
  <your fields here>
from
  tickets t
  join company c on t.company_id = c.id
  join ch_history ch on ch.ticket_id = t.id
  join users u on ch.user_id = u.ud
  join group_users g on u.group_user_id = g.id
where
  company = 15
order by
  t.id, ch.created_at  -- this is what tells distinct on which record to choose

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。