如何在分区中使用多个列并确保没有返回重复行 [英] How to Use Multiple Columns in Partition By And Ensure No Duplicate Row is Returned

问题描述

大家好，



我在SQL中的Partition By语句中使用了多个列，但返回了重复的行。我只想要返回不同的行。



这是我在分区中编码的：

hi Members,

I have used multiple columns in Partition By statement in SQL but duplicate rows are returned back. I only want distinct rows being returned back.

This is what I have coded in Partition By:

row_nums AS
    (
SELECT DATE, STATUS, TITLE, ROW_NUMBER() OVER (PARTITION BY DATE, STATUS, TITLE ORDER BY QUANTITY ASC) AS Row_Num
		FROM TABLE
    )
SELECT .................

这是我目前得到的输出:(哪里返回重复的行 - 请参阅第6行到第8行）

http://i.stack.imgur.com/foe7g.png [ ^ ]



这是我想要的输出实现：（没有重复行返回 - 参见第6行到第8行）

http://i.stack.imgur.com/GkrHP.png [ ^ ]



问题：如何在 1分区中放置多列并确保没有重复行返回？



感谢有人能为我提供帮助，非常感谢!!

This is the output I get currently: (Where there are duplicate rows being returned - Refer to Row 6 to 8)
http://i.stack.imgur.com/foe7g.png[^]

This is the output I want to achieve: (no duplicate row being returned - Refer to Row 6 to 8)
http://i.stack.imgur.com/GkrHP.png[^]

Question: How can I place multiple columns in 1 Partition By and Ensure No Duplicate Row is Returned?

Appreciate if someone can provide me help on this, thanks a lot!!

推荐答案

似乎数据应按预期显示。尝试通过选择以下查询来识别它：



从表格中选择不同的日期，状态，标题



在上述情况下，查询应返回该行的2条记录。如果不是这种情况，请发布行和表结构的表值。

Seems the data should display as expected. Try to identify it by selecting the following query:

Select distinct DATE, STATUS, TITLE From TABLE

In the above case, the query should return 2 records for that row. If this isn't the case, please post the table values for both the rows and the table structure.

;WTIH row_nums AS 
    (
        SELECT DATE, STATUS, TITLE, ROW_NUMBER() OVER (PARTITION BY DATE, STATUS, TITLE ORDER BY NUM ASC) AS Row_Num
        FROM (
             SELECT your required columns, COUNT(duplicated_rows_columnsname)
             FROM ***
             GROUP BY columnnames
             HAVING COUNT(duplicated_rows_columnsname) = 1
        )
    )
SELECT .................

这篇关于如何在分区中使用多个列并确保没有返回重复行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！