如何在sql server表中找出前两个最小值 [英] How do I find out first two minimum values in sql server table

问题描述

嗨亲爱的朋友们....





我有一张桌子如下所示。

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

此表是插入过程后的输出。

我想找到每个项目的前两个最便宜（最小）值，并将相应的状态列值更新为1，如下所示。

This table is the out put just after the insertion process.
I would like to find the first two 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               0  

346           AA                    3.2               1

347           BB                    8.9               0

348           AA                    6.3               0

349           BB                    1.1               1
 
350           AA                    2.1               1

351           BB                    3.8               1

352           BB                    9.7               0

353           BB                    10.0              0

354           BB                    12.7              0  

355           CC                    4.8               1

356           DD                    23.7              1

357           DD                    24.4              1

358           EE                    7.8               1

359           CC                    9.2               0
          
360           CC                    4.1               1

如果有人对此有所了解，请帮助我......

谢谢...... :)



[edit]已添加代码块 - OriginalGriff [/ edit]

If anyone has idea about this kindly help me......
Thank You... :)

[edit]Code block added - OriginalGriff[/edit]

推荐答案

这应该是这样的解决你的问题。

This should solve your problem.

DECLARE @TBL TABLE
(
  ID INT,
  NAME VARCHAR(10),
  PRICE Float,
  Status Int
 )
 
INSERT INTO @TBL (ID, NAME, PRICE, Status)
SELECT 345,'AA',5.5,0  
UNION ALL
SELECT 346,'AA',3.2,0
UNION ALL
SELECT 347,'BB',8.9,0
UNION ALL
SELECT 348,'AA',6.3,0
UNION ALL
SELECT 349,'BB',1.1,0
UNION ALL
SELECT 350,'AA',2.1,0
UNION ALL
SELECT 351,'BB',3.8,0
UNION ALL
SELECT 352,'BB',9.7,0
UNION ALL
SELECT 353,'BB',10.0,0
UNION ALL
SELECT 354,'BB',12.7,0  
UNION ALL
SELECT 355,'CC',4.8,0
UNION ALL
SELECT 356,'DD',23.7,0
UNION ALL
SELECT 357,'DD',24.4,0
UNION ALL
SELECT 358,'EE',7.8,0
UNION ALL
SELECT 359,'CC',9.2,0
UNION ALL
SELECT 360,'CC',4.1,0
	
UPDATE T
SET	STATUS = 1
FROM  @TBL T INNER JOIN 
(
	SELECT 
		T.NAME,
		T.PRICE,
		rowid = ROW_NUMBER() OVER (PARTITION BY T.NAME ORDER BY PRICE)
	FROM @TBL T
) TMP ON T.NAME = TMp.NAME AND T.PRICE = TMP.PRICE 
WHERE TMP.rowid <= 2

SELECT * FROM @TBL

我无法从表中找到前两个最小值..

这很简单：

"I am not able to findout the first two minimum values from the table.."
That's pretty easy:

SELECT * FROM MyTable m1
WHERE ( SELECT  COUNT(*) FROM MyTable m2
            WHERE m1.name = m2.name AND m1.price <= m2.price
      ) <= 2

因此，检查它是否完全符合您的要求，然后如果您想在SQL中完成所有操作，请用UPDATE替换外部SELECT。

So, check that works exactly as you want and then replace the outer SELECT with an UPDATE if you want to do it all in SQL.

这篇关于如何在sql server表中找出前两个最小值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！