禁止在后续行中重复值 [英] Suppress repeating values on subsequent rows

问题描述

item_no parent   item_no_child   item_name   text
123                3              xxx       the item is resistant to water
123                5              yyy       The item is resistant to heat
123                6              zzz       The item is ....

我将提供父item_no作为输入并检索子项no.现在，我必须检查每个子项的文本，如果它们具有相同的文本，则不应显示item_name，否则应显示.

I will be giving the parent item_no as input and retrieve child item no's. Now I have to check each child item's text and if they have same text I should not display the item_name else I should.

推荐答案

The row_number() analytic function is a neat way of implementing such distinct queries:

SELECT item_name
FROM   (SELECT item_name,
               ROW_NUMBER() OVER (PARTITION BY text ORDER BY 1) AS rn
        FROM   items
        WHERE  item_no parent = 123)
WHERE  rn = 1

注释中要求的某些解释-row_number是一种分析功能(有时也称为窗口功能).它每行输入返回一个结果(如行函数)，但也考虑所有其他行(如聚合函数).在这种情况下，row_number仅返回当前行的编号(即简单的计数器).此计数是根据不同的text值(partition by子句)进行的. row_number需要一个order by子句，因此它知道对这些行进行计数的顺序.由于在这里我们不在乎哪个行(每个不同的text值)先出现，因此我只按常数1进行排序.

Some explanation, as requested in the comments - row_number is an analytic function (sometimes also referred to as a windowing function). It returns one result per row of input (like a row function), but takes into account all the other rows too (like an aggregate function). In this case, row_number simply returns the number of current row (i.e., a simple counter). This counting is done per different value of text (the partition by clause). row_number requires an order by clause so it knows in which order to count these rows. Since here we don't care about which row (per different value of text) comes first, I simply order by a constant 1.

这篇关于禁止在后续行中重复值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！