SQL 問題求解~

显示全部楼层 · 2015-10-26 09:55:08

CREATE TABLE BREAK_DATA
(
BREAK_QTY NUMBER,
BREAK_PRICE NUMBER
)
Insert into BREAK_DATA
(BREAK_QTY, BREAK_PRICE)
Values
(1, 11);
Insert into BREAK_DATA
(BREAK_QTY, BREAK_PRICE)
Values
(5, 10);
Insert into BREAK_DATA
(BREAK_QTY, BREAK_PRICE)
Values
(10, 8);
Insert into BREAK_DATA
(BREAK_QTY, BREAK_PRICE)
Values
(20, 5);
COMMIT;
資料說明：我現在有份價格表說明某項物品購買數量(含)到達某量時的販賣單價。
舉例：若買7個則單價為10元；若買20個則單價為5元。
請問有無方法可以一段SQL就有辦法抓出購買的數量單價應該多少!?

千问 · 2015-10-26 09:55:08

with temp as (
select break_qty as min_cnt,lead(break_qty,1,999) over(order by break_qty) as max_cnt,break_price from BREAK_DATA
)
select min_cnt,max_cnt,break_price
from temp
where 20 >= min_cnt and 20
SQL> select n, b.break_price
2from (select level n from dual connect by level = b.break_qty
9 and a.n
SQL> select n, b.break_price price
2from (select level n from dual connect by level = b.break_qty
8 and a.n < b.lead_break_qty
9/
NPRICE
---------- ----------
1 11
2 11
3 11
4 11
5 10
6 10
7 10
8 10
9 10
10
8
11
8
12
8
13
8
14
8
15
8
16
8
17
8
18
8
19
8
20
5
NPRICE
---------- ----------
21
5
22
5
23
5
24
5
25
5
26
5
27
5
28
5
29
5
30
5
31
5
32
5
33
5
34
5
35
5
36
5
37
5
38
5
39
5
40
5
41
5
NPRICE
---------- ----------
42
5
43
5
44
5
45
5
46
5
47
5
48
5
49
5
50
5
50 rows selected

千问 · 2015-10-26 09:55:08

感謝兩位大師....

千问 · 2015-10-26 09:55:08

我觉得没有必要展开，如果范围很大那么就很浪费了。
select * from (
select t.*
,lead(break_qty-1,1,99999999) over(order by break_qty) qty2
from break_data t
)
where 7 between break_qty and qty2;

千问 · 2015-10-26 09:55:08

WoW....大師在加持過的。更是不一樣~~
感謝各位大力的協助~~