求助:非常经典的问题续
现有如下表:
id(varchar2) item(number) name(varchar2) amount(number)
1 1 A 100
1 2 B 30
1 3 A 50
1 4 B 90
2 1 A 90
2 2 B 40
2 3 C 140希望的结果:
id(varchar2) item(number) name(varchar2) amount(number)
1 1 A 150 原因:A的和150大于B的和120
2 3 C 140 原因:C的和140大于B的和40,也大于A的和90结果中ID的值为主键,item的值随name对应的amount的和变化,取最小值(取任意一个也可,但结果中必须包含此列---------关键问题)先谢过!
现有如下表:
id(varchar2) item(number) name(varchar2) amount(number)
1 1 A 100
1 2 B 30
1 3 A 50
1 4 B 90
2 1 A 90
2 2 B 40
2 3 C 140希望的结果:
id(varchar2) item(number) name(varchar2) amount(number)
1 1 A 150 原因:A的和150大于B的和120
2 3 C 140 原因:C的和140大于B的和40,也大于A的和90结果中ID的值为主键,item的值随name对应的amount的和变化,取最小值(取任意一个也可,但结果中必须包含此列---------关键问题)先谢过!
select '1' id, '1' item, 'A' name, '100' amount from dual union all
select '1' id, '2' item, 'B' name, '30' amount from dual union all
select '1' id, '3' item, 'A' name, '50' amount from dual union all
select '1' id, '4' item, 'B' name, '90' amount from dual union all
select '2' id, '1' item, 'A' name, '90' amount from dual union all
select '2' id, '2' item, 'B' name, '40' amount from dual union all
select '2' id, '3' item, 'C' name, '140' amount from dual)
SELECT id,item,name,amount from(
SELECT id,item,name,sum(amount) over(partition by id,name) amount,
row_number() over(partition by id order by id,amount) num
from t
ORDER BY id,amount desc
)WHERE num=1
不知道对不对,不过以现在的测试数据是没有问题的
select '1' id, '1' item, 'A' name, '100' amount from dual union all
select '1' id, '2' item, 'B' name, '30' amount from dual union all
select '1' id, '3' item, 'A' name, '50' amount from dual union all
select '1' id, '4' item, 'B' name, '90' amount from dual union all
select '2' id, '1' item, 'A' name, '90' amount from dual union all
select '2' id, '2' item, 'B' name, '40' amount from dual union all
select '2' id, '3' item, 'C' name, '140' amount from dual)
SELECT id,item,name,amount
from
(
SELECT id,item,name,amount,
row_number() over(partition by id order by amount desc ) r1
from(
SELECT id,item,name,sum(amount) over(partition by id,name) amount
from t
) tt
)
WHERE r1=1
select '1' id, '1' item, 'A' name, '100' amount from dual
union all
select '1' id, '2' item, 'B' name, '30' amount from dual
union all
select '1' id, '3' item, 'A' name, '50' amount from dual
union all
select '1' id, '4' item, 'B' name, '90' amount from dual
union all
select '2' id, '1' item, 'A' name, '90' amount from dual
union all
select '2' id, '2' item, 'B' name, '40' amount from dual
union all
select '2' id, '3' item, 'C' name, '140' amount from dual
)
select id,item,name,amount from(
select id,item,name,amount,row_number() over(partition by id order by amount desc ) rn from(
select id,item,name,sum(amount) over(partition by id,name) amount from temp)
)
WHERE rn=1
select '1' id, '1' item, 'A' name, '100' amount from dual
union all
select '1' id, '2' item, 'B' name, '30' amount from dual
union all
select '1' id, '3' item, 'A' name, '50' amount from dual
union all
select '1' id, '4' item, 'B' name, '90' amount from dual
union all
select '2' id, '1' item, 'A' name, '90' amount from dual
union all
select '2' id, '2' item, 'B' name, '40' amount from dual
union all
select '2' id, '3' item, 'C' name, '140' amount from dual
)
select id,item,name,amount from(
select id,item,name,amount,row_number() over(partition by id order by amount desc ) rn from(
select id,item,name,sum(amount) over(partition by id,name) amount from temp)
)
WHERE rn=1
SELECT ID, ITEM, NAME, C
FROM (SELECT ID,
NAME,
ITEM,
C,
ROW_NUMBER() OVER(PARTITION BY ID, NAME, C ORDER BY ITEM) AS SEQ
FROM (SELECT A.ID, A.NAME, A.C, B.ITEM
FROM (SELECT ID,
NAME,
C,
ROW_NUMBER() OVER(PARTITION BY ID ORDER BY C DESC) AS SEQ
FROM (SELECT ID, NAME, SUM(AMOUNT) AS C
FROM TABLEC
GROUP BY ID, NAME)) A,
TABLEC B
WHERE A.SEQ = 1
AND A.ID = B.ID
AND A.NAME = B.NAME))
WHERE SEQ = 1
ORDER BY ID