当列具有重复值时获取第二条记录 [英] get second record when having duplicate values for a column

问题描述

嗨专家，

我有一个折扣表如下：

Hi experts,
I have a discount table looks like this:

table discount ("productId","lineNum","price","discount","toDate","fromDate")

值可能是这样的：

Values could be something like this:

("product1","0","100","10","2015-03-01","2015-04-30")
("product1","1","100","10","2015-05-01","2015-06-30")

所以我可以拥有不同 lineNum的相同产品。

我打算做的是：从我的表中选择所有在2个日期之间有折扣的产品（我可以很容易地做到）。如果产品重复，只带 lineNum = 1

的产品我无法理解这一部分！

编辑：如果没有日期范围怎么办？ distinct在这种情况下不起作用，因为每一行都是截然不同的。



任何帮助都会非常感激。

提前致谢

Samira

So i could have the same product with different lineNum.
What i intend to do is: select from my table all the products that have discount between 2 dates (which i could easily do). AND if the product is repeating take only the product with lineNum=1
I can't figure out this part!
EDIT: What if there is no date range? distinct won't work in this case because every line is distinct.

Any help will be so much appreciated.
Thanks in advance
Samira

推荐答案

如果我理解正确，你需要使用 ROW_NUMBER（） [ ^ ]排名功能。



如果 lineNum 从 0开始试试这个：

If i understand you correctly, you need to use ROW_NUMBER()[^] ranking function.

If lineNum starts from 0 try this:

SELECT productId , lineNum, price, discount, toDate, fromDate
FROM (
    SELECT productId , lineNum, price, discount, toDate, fromDate, ROW_NUMBER() OVER(PARTITION BY productId ORDER BY lineNum)
    FROM TableName
    WHERE fromDate >= @fromDate AND toData <= @toDate AND lineNum > 0
    ) AS T 
WHERE RowNo=1

尝试不同

Try distinct

SELECT distinct productid, discount,price  FROM discount WHERE @InDate BETWEEN toDate AND fromDate ORDER BY lineNum

这篇关于当列具有重复值时获取第二条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！