根据HistoryID删除SQL重复字段

发布于 2024-12-13 04:14:32 字数 966 浏览 1 评论 0原文

我有一个包含重复值的数据库。具体来说，SiteCode、LastName、FirstName、DateofService、Payer、BilledAmount、NetReceivable 和 ContractualDiscount 连接形成在此表中重复出现的记录。

我想删除这些字段中除一个实例以外的所有字段，并尝试通过仅选择一个 NetBilledHistoryID（这是表中每条记录的唯一字段）来实现此目的。

不幸的是，当我运行此查询时，我仍然得到重复的值。

我该如何纠正这个问题，以便我的选择查询消除这些重复项？或者，更好的是，我应该一起使用不同的查询技术吗？

SELECT * 
FROM [Reports].[dbo].[NetBilledHistory] t1
WHERE EXISTS ( 
                SELECT 1 FROM [Reports].[dbo].[NetBilledHistory] AS t2
                WHERE t2.SiteCode = t1.SiteCode
                AND t2.LastName = t1.LastName
                AND t2.FirstName = t1.FirstName
                AND t2.DateofService = t1.DateofService
                AND t2.Payer = t1.Payer
                AND t2.BilledAmount = t1.BilledAmount
                AND t2.NetReceivable = t1.NetReceivable
                AND t2.ContractualDiscount = t1.ContractualDiscount
                AND t2.NetBilledHistoryID < t1.NetBilledHistoryID)

原文

I have a database with duplicate values. Specifically SiteCode, LastName, FirstName, DateofService, Payer, BilledAmount, NetReceivable, and ContractualDiscount concatenate to form records that recur repeatedly throughout this table.

I'd like to remove all but one instance of these fields and am trying to do so by picking just one NetBilledHistoryID (which is a unique field for each record in the table).

Unfortunately, when I run this query, I still get the duplicated values.

How can I correct this so that my select query eliminates these duplicates? Or, even better, should I be using a different query technique all together?

SELECT * 
FROM [Reports].[dbo].[NetBilledHistory] t1
WHERE EXISTS ( 
                SELECT 1 FROM [Reports].[dbo].[NetBilledHistory] AS t2
                WHERE t2.SiteCode = t1.SiteCode
                AND t2.LastName = t1.LastName
                AND t2.FirstName = t1.FirstName
                AND t2.DateofService = t1.DateofService
                AND t2.Payer = t1.Payer
                AND t2.BilledAmount = t1.BilledAmount
                AND t2.NetReceivable = t1.NetReceivable
                AND t2.ContractualDiscount = t1.ContractualDiscount
                AND t2.NetBilledHistoryID < t1.NetBilledHistoryID)

分享到QQ

分享到微博

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

发布评论

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

晌融 2024-12-20 04:14:32

您可以通过使用CTE来实现这一点：

WITH cte_query AS (
    SELECT ROW_NUMBER() OVER(
        partition by SiteCode, LastName, FirstName,
            DateofService, Payer, BilledAmount, NetReceivable,
            ContractualDiscount
        order by NetBilledHistoryID) AS RowNum, *
  FROM [Reports].[dbo].[NetBilledHistory]
)

SELECT * FROM cte_query where RowNum = 1;

根据需要进行调整以适合您的列等用途。

you can achieve that by using CTE:

WITH cte_query AS (
    SELECT ROW_NUMBER() OVER(
        partition by SiteCode, LastName, FirstName,
            DateofService, Payer, BilledAmount, NetReceivable,
            ContractualDiscount
        order by NetBilledHistoryID) AS RowNum, *
  FROM [Reports].[dbo].[NetBilledHistory]
)

SELECT * FROM cte_query where RowNum = 1;

adjust as needed to fit your purpose for columns, etc.

回复收藏 0 原文

~没有更多了~