请教大神:table test:
id index testNumber testType
A 3 A COT
A 2 B COT
A 5 A COW
A 6 B COW求 Sql 达到如下效果:
id COT_index cot_testNumber cow_index cow_testnumber
A 3 A 6 B条件: 把Id是一样的排成一行,按testType 取index 大的一行值。
希望sql的性能要好点谢谢
id index testNumber testType
A 3 A COT
A 2 B COT
A 5 A COW
A 6 B COW求 Sql 达到如下效果:
id COT_index cot_testNumber cow_index cow_testnumber
A 3 A 6 B条件: 把Id是一样的排成一行,按testType 取index 大的一行值。
希望sql的性能要好点谢谢
按Id和testType把index和testnumber取名为testType_index,testType_testNumber ,对应这个列子有4个栏位:
cow_index,cow_testNumber,cot_index,cot_testNumber ,值取index大的一行这样看的懂吗?小弟不才,表述不给力啊
SELECT 'A' AS idd,'3' as indexx,'A' as testNumber,'COT' as testType from dual
union all
SELECT 'A' AS idd,'2' as indexx,'B' as testNumber,'COT' as testType from dual
union all
SELECT 'A' AS idd,'5' as indexx,'A' as testNumber,'COW' as testType from dual
union all
SELECT 'A' AS idd,'6' as indexx,'B' as testNumber,'COW' as testType from dual
)
SELECT idd,
MAX(DECODE(testType, 'COT', indexx, '')) AS COT_index,
MAX(DECODE(testType, 'COT', testNumber, '')) AS COT_testNumber,
MAX(DECODE(testType, 'COW', indexx, '')) AS COW_index,
MAX(DECODE(testType, 'COW', testNumber, '')) AS COW_testNumber
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY idd, testType ORDER BY indexx DESC) AS RN,
TEST.*
FROM TEST)
WHERE RN = 1
GROUP BY idd
===================================================
1 A 3 A 6 B
一 COT_index 和cow_index 有什么区别?
二 cot_testNumber 和 cow_testnumber 有什么区别?