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

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


 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) --卖出数量
)

回复赞

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


 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) --卖出数量
)

回复赞

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

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

回复赞

落落叶叶无声 2017-01-12

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

回复赞

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

回复赞