现在，我希望能够列出当天销售价格第二低的产品 [英] Now I want to be able to list the 2nd lowest priced product sold for the day

问题描述

我希望能够列出在Microsoft服务器sql studio 2008上进行查询后第二天售出的价格最低的产品。（我知道如何列出回答此问题的人的最低价格，但是在哪里如果我只想列出最低的第二，第三或第四，但是让我们只专注于如何列出最低的第二，

I want to be able to list the 2nd lowest priced product sold for the day after i do queries on microsoft server sql studio 2008. (I know how to list the lowest thx to the guys who answered that question Here, but what if i just want to list the 2nd lowest, 3rd, or 4th lowest, but let just focus on how to list the 2nd lowest

这就是表的名字是销售

DATE PRODUCT_SOLD PRICE
2013-11-15, crab pot , 21.15
2013-11-15, bait , 3.50
2013-11-15, hooks , 11.99
2013-11-15 , sinkers , 1.99
2013-11-15 , fishing rod , 49.99
2013-11-16 , baitcaster , 29.99
2013-11-16 , squid bait , 3.50
2013-11-16 , knife , 9.95
2013-11-17 , fishing rod , 99.95
2013-11-17 , net , 25.99

如何显示它列出一天内价格第二低的产品。

How do I display it so it just list the 2nd lowest priced product for a day.

如下所示。

2013-11-15, bait , 3.50
2013-11-16 , knife , 9.95
2013-11-17 , fishing rod , 99.95

推荐答案

尝试一下：

SELECT date, product_sold, price
FROM (
  SELECT date, product_sold, price,
    DENSE_RANK() OVER (PARTITION BY date ORDER BY price) rank
  FROM sales
) t
WHERE rank = 2

小提琴此处。

请注意，如果您具有值 1 ，则 1 和 2 ，这将返回 2 。如果您想返回 second 1 ，请使用 ROW_NUMBER 而不是 DENSE_RANK 。

Note that if you have values 1, 1 and 2 this will return 2. If you would like to return the second 1 then use ROW_NUMBER instead of DENSE_RANK.

如果您正在寻找适用于大多数DBMS的通用解决方案，则可以：

If you're looking for a generic solution that would in most DBMS then you can go for:

SELECT s2.date, min(s2.price) price FROM (
  SELECT date, min(price) price FROM sales
  GROUP BY date
) s1 JOIN sales s2 ON s1.date = s2.date AND s2.price > s1.price
GROUP BY s2.date

请注意，此选项将作为 DENSE_RANK 解决方案。

Note this one would work as the DENSE_RANK solution.

这篇关于现在，我希望能够列出当天销售价格第二低的产品的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！