删除SQL Server查询中的重复行 [英] remove duplicate rows in SQL Server Query

问题描述

我们如何在sqlserver查询中删除或不显示带有某些条件子句的重复行，这种情况看起来像这样，

how could we remove or not displaying duplicate row with some conditional clause in sqlserver query, the case look like this one,

code    decs
-------------------------
G-006   New
G-006   Re-Registration

我们如何只显示G-006和Re-Registration Desc，我已经尝试过使用该查询，但是也没有运气

how can we display just G-006 with Re-Registration Desc, i have tried with this query but no luck either

with x as (
            select  new_registration_no,category,rn = row_number()
      over(PARTITION BY new_registration_no order by new_registration_no)
      from  equipment_registrations 
)
select * from x

推荐答案

通过在PARTITION BY和ORDER BY子句中使用相同的字段，rn字段将始终等于1.

By using the same field in the PARTITION BY and ORDER BY clause, the rn field will always equal 1.

假设new_registration_no =代码且category = decs，则可以将ORDER BY字段更改为ORDER BY category DESC以获得该结果.但是，这是一个非常随意的ORDER BY-您只是将其基于随机文本值.我也不能100％地确定ROW_NUMBER()函数在CTE中的工作情况.

Assuming that new_registration_no = code and category = decs, you could change the ORDER BY field to be ORDER BY category DESC to get that result. However, that's a pretty arbitrary ORDER BY - you're just basing it on a random text value. I'm also not 100% sure how well the ROW_NUMBER() function works in a CTE.

更好的解决方案可能是:

A better solution might be something like:

SELECT *
FROM 
  (
    SELECT 
        New_Registration_No, 
        Category, 
        ROW_NUMBER() OVER 
          (
            PARTITION BY New_Registration_No 
            ORDER BY 
                CASE 
                    WHEN Category = 'Re-Registration' THEN 1
                    WHEN Category = 'New' THEN 2
                    ELSE 3
                END ASC ) rn
    FROM Equipment_Registrations
  ) s
WHERE rn = 1

您可以在CASE语句中设置所需的顺序-恐怕没有更多信息，这是我能为您提供的最佳解决方案.如果您知道该字段中可能出现的值列表，则应该很简单；否则，配置起来会有些困难，但这将基于您未包含在原始帖子中的业务规则.

You can set the order in the CASE statement to be whatever you want - I'm afraid that without more information, that's the best solution I can offer you. If you have a known list of values that might appear in that field, it should be easy; if not, it will be a little harder to configure, but that will be based on business rules that you did not include in your original post.

这篇关于删除SQL Server查询中的重复行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！