删除重复项（一对多）或编写一个子查询来解决我的问题

发布于 2024-12-07 05:16:46 字数 522 浏览 0 评论 0原文

参考下图，记录表具有唯一的记录。每条记录都会通过更新表中的注释进行更新。当我加入两者时，我得到了很多重复项。

如何删除重复项？ Group By 对我不起作用，因为我在选择查询中有超过 10 个字段，其中一些是函数。
编写一个子查询，为特定月份更新的每条记录提取更新表中的最新更新。加入这个子查询将解决我的问题。

谢谢！

编辑感兴趣的表结构是

create table Records(
recordID int,
90more_fields various
)

create table Updates(
update_id int,
record_id int,
comment text,
byUser varchar(25),
datecreate datetime
)

在此处输入图像描述

原文

Referring to the diagram below the records table has unique Records. Each record is updated, via comments through an Update Table. When I join the two I get lots of duplicates.

How to remove duplicates? Group By does not work for me as I have more than 10 fields in select query and some of them are functions.
Write a sub query which pulls the last updates in the Update table for each record that is updated in a particular month. Joining with this sub query will solve my problem.

Thanks!

Edit
Table structure that is of interest is

create table Records(
recordID int,
90more_fields various
)

create table Updates(
update_id int,
record_id int,
comment text,
byUser varchar(25),
datecreate datetime
)

enter image description here

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

旧话新听 2024-12-14 05:16:46

这是一种方法。

SELECT * /*But list columns explicitly*/
FROM   Orange o
       CROSS APPLY (SELECT TOP 1 *
                    FROM   Blue b
                    WHERE  b.datecreate >= '20110901'
                           AND b.datecreate < '20111001'
                           AND o.RecordID = b.Record_ID2
                    ORDER  BY b.datecreate  DESC) b

Here's one way.

SELECT * /*But list columns explicitly*/
FROM   Orange o
       CROSS APPLY (SELECT TOP 1 *
                    FROM   Blue b
                    WHERE  b.datecreate >= '20110901'
                           AND b.datecreate < '20111001'
                           AND o.RecordID = b.Record_ID2
                    ORDER  BY b.datecreate  DESC) b

回复收藏 0 原文

我们只是彼此的过ke 2024-12-14 05:16:46

根据现有的有限信息...

WITH cteLastUpdate AS (
    SELECT Record_ID2, UpdateDateTime, 
           ROW_NUMBER() OVER(PARTITION BY Record_ID2 ORDER BY UpdateDateTime DESC) AS RowNUM
        FROM BlueTable
        /* Add WHERE clause if needed to restrict date range */
)
SELECT *
    FROM cteLastUpdate lu
        INNER JOIN OrangeTable o
            ON lu.Record_ID2 = o.RecordID
    WHERE lu.RowNum = 1

Based on the limited information available...

WITH cteLastUpdate AS (
    SELECT Record_ID2, UpdateDateTime, 
           ROW_NUMBER() OVER(PARTITION BY Record_ID2 ORDER BY UpdateDateTime DESC) AS RowNUM
        FROM BlueTable
        /* Add WHERE clause if needed to restrict date range */
)
SELECT *
    FROM cteLastUpdate lu
        INNER JOIN OrangeTable o
            ON lu.Record_ID2 = o.RecordID
    WHERE lu.RowNum = 1

回复收藏 0 原文

陈年往事 2024-12-14 05:16:46

每条记录和月份的最后更新：（

SELECT *
  FROM UPDATES outerUpd
 WHERE exists
 (
      -- Magic part
      SELECT 1
        FROM UPDATES innerUpd
       WHERE innerUpd.RecordId = outerUpd.RecordId
    GROUP BY RecordId
           , date_part('year', innerUpd.datecolumn)
           , date_part('month', innerUpd.datecolumn)
      HAVING max(innerUpd.datecolumn) = outerUpd.datecolumn
 )

适用于 PostgreSQL，date_part 在其他 RDBMS 中不同）

Last updates per record and month:

SELECT *
  FROM UPDATES outerUpd
 WHERE exists
 (
      -- Magic part
      SELECT 1
        FROM UPDATES innerUpd
       WHERE innerUpd.RecordId = outerUpd.RecordId
    GROUP BY RecordId
           , date_part('year', innerUpd.datecolumn)
           , date_part('month', innerUpd.datecolumn)
      HAVING max(innerUpd.datecolumn) = outerUpd.datecolumn
 )

(Works on PostgreSQL, date_part is different in other RDBMS)

回复收藏 0 原文

~没有更多了~