如何在sql server表中找到第三个和第四个最小值 [英] How do I find third and fourth minimum values in an sql server table
问题描述
嗨亲爱的朋友们....
我有一张桌子如下所示。
id名称价格状态
345 AA 5.5 0
346 AA 3.2 0
347 BB 8.9 0
348 AA 6.3 0
349 BB 1.1 0
350 AA 2.1 0
351 BB 3.8 0
352 BB 9.7 0
353 BB 10.0 0
354 BB 12.7 0
355 CC 4.8 0
356 DD 23.7 0
357 DD 24.4 0
358 EE 7.8 0
359 CC 9.2 0
360 CC 4.1 0
此表为现货在插入过程之后。
我想找到每个项目的第三和第四最便宜（最小）值，并将相应的状态列值更新为1，如下所示。
id名称价格状态
345 AA 5.5 1
346 AA 3.2 0
347 BB 8.9 1
348 AA 6.3 1
349 BB 1.1 0
350 AA 2.1 0
351 BB 3.8 0
352 BB 9.7 1
353 BB 10.0 0
354 BB 12.7 0
355 CC 4.8 0
356 DD 23.7 0
357 DD 24.4 0
358 EE 7.8 0
359 CC 9.2 1
360 CC 4.1 0
如果有人对此有所了解，请帮助我......
谢谢...... :)
Hi Dear Friends....
I have a table as shown below.
id name price status
345 AA 5.5 0
346 AA 3.2 0
347 BB 8.9 0
348 AA 6.3 0
349 BB 1.1 0
350 AA 2.1 0
351 BB 3.8 0
352 BB 9.7 0
353 BB 10.0 0
354 BB 12.7 0
355 CC 4.8 0
356 DD 23.7 0
357 DD 24.4 0
358 EE 7.8 0
359 CC 9.2 0
360 CC 4.1 0
This table is the out put just after the insertion process.
I would like to find the third and fourth cheapest(minimum) values of each item and update the corresponding "status" column value to 1 as shown below.
id name price status
345 AA 5.5 1
346 AA 3.2 0
347 BB 8.9 1
348 AA 6.3 1
349 BB 1.1 0
350 AA 2.1 0
351 BB 3.8 0
352 BB 9.7 1
353 BB 10.0 0
354 BB 12.7 0
355 CC 4.8 0
356 DD 23.7 0
357 DD 24.4 0
358 EE 7.8 0
359 CC 9.2 1
360 CC 4.1 0
If anyone has idea about this kindly help me......
Thank You... :)
推荐答案
DECLARE @Var1 MONEY, @Var2 MONEY, @Var3....
SELECT @Var1 = MIN(Price)
FROM Items
SELECT @Var2 = MIN(Price)
FROM Items
WHERE Price > @Var1
SELECT @Var3 = MIN(Price)
FROM Items
WHERE Price > @Var2
...
这篇关于如何在sql server表中找到第三个和第四个最小值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
