1> select * from TEST_TOP1;
2> go
型号 品牌 批号 数量
---------- ---------- ---------- -----------
ABC TOKO 2003 1111
ABC YAMAHA 2000 1000
ABC MOTOROLA 2004 3000
ABC INFINEON 2004 3000
OTHER Test 2002 1000
(5 行受影响)
1> SELECT
2> [型号],
3> [品牌],
4> [批号],
5> [数量]
6> FROM
7> (
8> SELECT
9> ROW_NUMBER () OVER (PARTITION BY [型号] ORDER BY [品牌]) AS id,
10> TEST_TOP1.*
11> FROM
12> TEST_TOP1
13> WHERE
14> ( SELECT
15> COUNT(1)
16> FROM
17> TEST_TOP1 subTopnTest
18> WHERE
19> TEST_TOP1.[型号] = subTopnTest.[型号]
20> AND TEST_TOP1.[数量] < subTopnTest.[数量]
21> ) < 1
22> ) tmpView
23> WHERE
24> id = 1
25> go
型号 品牌 批号 数量
---------- ---------- ---------- -----------
ABC INFINEON 2004 3000
OTHER Test 2002 1000
(2 行受影响)
温馨提示:答案为网友推荐,仅供参考