请教一个关于一个苹果排序的问题

[dgyig]

A 有 品质为9的苹果20个。
B有品质为8的苹果50个
C有品质为7的苹果40个。
表如下：
name grade amount
A 9 20
B 8 50
C 7 40
现在想按品质从高到低收购100个苹果。该如何查询？

[dgyig]

要求将每人被收购的数量查出来，如 A 20 B 50 C 30

[liuzhe_521]

with t as( select 'a' nm,9 gd,20 cnt from dual union all select 'b',8,50 from dual union all select 'c',7,40 from dual union all select 'd',6,30 from dual ) select nm, gd, case when scnt > 100 then cnt - (scnt - 100) else cnt end cnt from (select sum(cnt) over(order by gd desc) scnt, nm, gd, cnt from t) a where cnt - (scnt - 100) > 0

[dgyig]

引用 2 楼 liuzhe_521 的回复:

with t as( select 'a' nm,9 gd,20 cnt from dual union all select 'b',8,50 from dual union all select 'c',7,40 from dual union all select 'd',6,30 from dual ) select nm, gd, case when scnt > 100 then cnt - (scnt - 100) else cnt end cnt from (select sum(cnt) over(order by gd desc) scnt, nm, gd, cnt from t) a where cnt - (scnt - 100) > 0

我要学多久才能到你这个水平....

[落落叶叶无声]

当楼主的头像不再那么搞笑的时候

[dgyig]

引用 2 楼 liuzhe_521 的回复:

with t as( select 'a' nm,9 gd,20 cnt from dual union all select 'b',8,50 from dual union all select 'c',7,40 from dual union all select 'd',6,30 from dual ) select nm, gd, case when scnt > 100 then cnt - (scnt - 100) else cnt end cnt from (select sum(cnt) over(order by gd desc) scnt, nm, gd, cnt from t) a where cnt - (scnt - 100) > 0

再次请问又该怎样才能把结果更新回表里面呢？

[dgyig]

引用 4 楼 u012557814 的回复:

当楼主的头像不再那么搞笑的时候

一天不熟练。就一天不换头像

[liuzhe_521]

引用 5 楼 dgyig 的回复:

[quote=引用 2 楼 liuzhe_521 的回复:] with t as( select 'a' nm,9 gd,20 cnt from dual union all select 'b',8,50 from dual union all select 'c',7,40 from dual union all select 'd',6,30 from dual ) select nm, gd, case when scnt > 100 then cnt - (scnt - 100) else cnt end cnt from (select sum(cnt) over(order by gd desc) scnt, nm, gd, cnt from t) a where cnt - (scnt - 100) > 0

再次请问又该怎样才能把结果更新回表里面呢？[/quote] 行数可能和原数据不一样了，怎么能更新到原表 ？ 或者原表里新加一字段，把这段代码加上rowid，根据rowid把cnt更新到新字段上。

[dgyig]

引用 7 楼 liuzhe_521 的回复:

[quote=引用 5 楼 dgyig 的回复:] [quote=引用 2 楼 liuzhe_521 的回复:] with t as( select 'a' nm,9 gd,20 cnt from dual union all select 'b',8,50 from dual union all select 'c',7,40 from dual union all select 'd',6,30 from dual ) select nm, gd, case when scnt > 100 then cnt - (scnt - 100) else cnt end cnt from (select sum(cnt) over(order by gd desc) scnt, nm, gd, cnt from t) a where cnt - (scnt - 100) > 0

再次请问又该怎样才能把结果更新回表里面呢？[/quote] 行数可能和原数据不一样了，怎么能更新到原表 ？ 或者原表里新加一字段，把这段代码加上rowid，根据rowid把cnt更新到新字段上。[/quote] 我新建了一个表

 select * from t for update 
CREATE TABLE  t
(
nm char(1),
gd number(4,0),
cnt number(4,0),
mark number(4,0),--唯一标识
sale number(4,0) --卖出数量
)

但是执行 update t set sale= (select case when scnt > 100 then cnt - (scnt - 100) else cnt end cnt from (select sum(cnt) over(order by gd desc) scnt, nm, gd, cnt,mark from t) a where cnt - (scnt - 100) > 0 and a.mark=t.mark) where exists (select 1 from a where mark=t.mark) 报错。 如果去掉最后的where exists (select 1 from a where mark=t.mark则也把用户D也更新

[liuzhe_521]

引用 8 楼 dgyig 的回复:

[quote=引用 7 楼 liuzhe_521 的回复:] [quote=引用 5 楼 dgyig 的回复:] [quote=引用 2 楼 liuzhe_521 的回复:] with t as( select 'a' nm,9 gd,20 cnt from dual union all select 'b',8,50 from dual union all select 'c',7,40 from dual union all select 'd',6,30 from dual ) select nm, gd, case when scnt > 100 then cnt - (scnt - 100) else cnt end cnt from (select sum(cnt) over(order by gd desc) scnt, nm, gd, cnt from t) a where cnt - (scnt - 100) > 0

再次请问又该怎样才能把结果更新回表里面呢？[/quote] 行数可能和原数据不一样了，怎么能更新到原表 ？ 或者原表里新加一字段，把这段代码加上rowid，根据rowid把cnt更新到新字段上。[/quote] 我新建了一个表

 select * from t for update 
CREATE TABLE  t
(
nm char(1),
gd number(4,0),
cnt number(4,0),
mark number(4,0),--唯一标识
sale number(4,0) --卖出数量
)

但是执行 update t set sale= (select case when scnt > 100 then cnt - (scnt - 100) else cnt end cnt from (select sum(cnt) over(order by gd desc) scnt, nm, gd, cnt,mark from t) a where cnt - (scnt - 100) > 0 and a.mark=t.mark) where exists (select 1 from a where mark=t.mark) 报错。 如果去掉最后的where exists (select 1 from a where mark=t.mark则也把用户D也更新 [/quote] merge into t a using (select case when scnt > 100 then cnt - (scnt - 100) else cnt end cnt, rid from (select sum(cnt) over(order by gd desc) scnt, nm, gd, cnt, rowid rid from t) a where cnt - (scnt - 100) > 0) b on (a.rowid = b.rid) when matched then update set a.sale = b.cnt

[dgyig]

引用 9 楼 liuzhe_521 的回复:

[quote=引用 8 楼 dgyig 的回复:] [quote=引用 7 楼 liuzhe_521 的回复:] [quote=引用 5 楼 dgyig 的回复:] [quote=引用 2 楼 liuzhe_521 的回复:] with t as( select 'a' nm,9 gd,20 cnt from dual union all select 'b',8,50 from dual union all select 'c',7,40 from dual union all select 'd',6,30 from dual ) select nm, gd, case when scnt > 100 then cnt - (scnt - 100) else cnt end cnt from (select sum(cnt) over(order by gd desc) scnt, nm, gd, cnt from t) a where cnt - (scnt - 100) > 0

再次请问又该怎样才能把结果更新回表里面呢？[/quote] 行数可能和原数据不一样了，怎么能更新到原表 ？ 或者原表里新加一字段，把这段代码加上rowid，根据rowid把cnt更新到新字段上。[/quote] 我新建了一个表

 select * from t for update 
CREATE TABLE  t
(
nm char(1),
gd number(4,0),
cnt number(4,0),
mark number(4,0),--唯一标识
sale number(4,0) --卖出数量
)

但是执行 update t set sale= (select case when scnt > 100 then cnt - (scnt - 100) else cnt end cnt from (select sum(cnt) over(order by gd desc) scnt, nm, gd, cnt,mark from t) a where cnt - (scnt - 100) > 0 and a.mark=t.mark) where exists (select 1 from a where mark=t.mark) 报错。 如果去掉最后的where exists (select 1 from a where mark=t.mark则也把用户D也更新 [/quote] merge into t a using (select case when scnt > 100 then cnt - (scnt - 100) else cnt end cnt, rid from (select sum(cnt) over(order by gd desc) scnt, nm, gd, cnt, rowid rid from t) a where cnt - (scnt - 100) > 0) b on (a.rowid = b.rid) when matched then update set a.sale = b.cnt [/quote] 请问知道为啥我的这句 where exists (select 1 from a where mark=t.mark) 会报表不存在吗？错在哪里

[dgyig]

修正下: where cnt - (scnt - 100) > 0) b 这里应该改为 where cnt - (scnt - 100) > =0) b