我有一个表，表示一组按组中名为 sequence 的列排序的集合：
| GroupId | Collection | Sequence |
|---------|------------|----------|
| 002 | A.2018 | 1 |
| 002 | A.2017 | 2 |
| 003 | P.2018 | 1 |
| 003 | L.2018 | 2 |
| 003 | R.2018 | 3 |
| 003 | M.2018 | 4 |
在另一个表中，我有按集合和细分的客户销售额：
| Collection | Segment | Customer | Sales |
|------------|---------|----------|--------|
| A.2018 | 002 | C001030 | 304.30 |
| A.2017 | 002 | C001030 | 493.10 |
| L.2018 | 002 | C001030 | 232.33 |
| L.2018 | 010 | C001030 | 343.12 |
| R.2018 | 002 | C001030 | 434.23 |
| M.2018 | 002 | C001030 | 121.12 |
我想通过 GroupID 获取客户在该细分市场中有销售额的第一个集合（按集合顺序排序）。
| GroupID | Collection | Segment | Customer | Sales |
|---------|------------|---------|----------|--------|
| 002 | A.2018 | 002 | C001030 | 304.30 |
| 003 | L.2018 | 002 | C001030 | 232.33 |
| 003 | L.2018 | 010 | C001030 | 343.12 |
SELECT * FROM (
with t1 as (
SELECT '002' as groupid, 'A.2018' as collection, 1 as sequence FROM DUAL UNION ALL
SELECT '002','A.2017',2 FROM DUAL UNION ALL
SELECT '003','P.2018',1 FROM DUAL UNION ALL
SELECT '003','L.2018',2 FROM DUAL UNION ALL
SELECT '003','R.2018',3 FROM DUAL UNION ALL
SELECT '003','M.2018',4 FROM DUAL
),
t2 as (
SELECT 'A.2018' as collection,'002' as segment ,'C001030' as customer,304.30 as sales FROM DUAL UNION ALL
SELECT 'A.2017','002','C001030',493.10 FROM DUAL UNION ALL
SELECT 'L.2018','002','C001030',232.33 FROM DUAL UNION ALL
SELECT 'L.2018','010','C001030',343.12 FROM DUAL UNION ALL
SELECT 'R.2018','002','C001030',434.23 FROM DUAL UNION ALL
SELECT 'M.2018','002','C001030',121.12 FROM DUAL
)
SELECT groupid,
t1.collection,
segment,
customer,
sales,
row_number() over (partition by groupid,segment,customer order by t1.collection ASC) rn
FROM t2,
t1
WHERE t1.collection = t2.collection
)
WHERE rn = 1
输出：
GROUPID COLLECTION SEGMENT CUSTOMER SALES RN
002 A.2017 002 C001030 493.1 1
003 L.2018 002 C001030 232.33 1
003 L.2018 010 C001030 343.12 1
但：
您预期的 002 组输出是
002 | A.2018 | 002 | C001030 | 304.30 |
你写道：
“第一个集合（按集合顺序排序）”
所以这里的第一个系列是A.2017，我想。
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句