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

dgyig 开发工程师  2017-01-12 02:35:31
A 有 品质为9的苹果20个。
B有品质为8的苹果50个
C有品质为7的苹果40个。
表如下:
name grade amount
A 9 20
B 8 50
C 7 40
现在想按品质从高到低收购100个苹果。该如何查询?
...全文
208 点赞 收藏 11
写回复
11 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
dgyig 2017-03-15
修正下: where cnt - (scnt - 100) > 0) b 这里应该改为 where cnt - (scnt - 100) > =0) b
回复
dgyig 2017-01-16
引用 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) 会报表不存在吗?错在哪里
回复
liuzhe_521 2017-01-13
引用 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 2017-01-13
引用 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 2017-01-12
引用 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 2017-01-12
引用 4 楼 u012557814 的回复:
当楼主的头像不再那么搞笑的时候
一天不熟练。就一天不换头像
回复
dgyig 2017-01-12
引用 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 2017-01-12
引用 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
我要学多久才能到你这个水平....
回复
liuzhe_521 2017-01-12
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 2017-01-12
要求将每人被收购的数量查出来,如 A 20 B 50 C 30
回复
相关推荐
发帖
Oracle
创建于2007-09-28

1.6w+

社区成员

Oracle开发相关技术讨论
申请成为版主
帖子事件
创建了帖子
2017-01-12 02:35
社区公告
暂无公告