需求:求库存的可销天数（按30天平均销量取值）(完了)
当日销售表
CC_JXC_GYS_DAY RQ DATE N 日期
MC VARCHAR2(4) N 卖场
GYS VARCHAR2(8) N 供应商号
GYSM VARCHAR2(60) Y 供应商名
SPTM VARCHAR2(13) N 商品编码
SPM VARCHAR2(60) Y 商品名
PP VARCHAR2(6) Y 品牌号
PPM VARCHAR2(20) Y 品牌名
CD VARCHAR2(6) Y 产地号
CDM VARCHAR2(32) Y 产地名
DZXL VARCHAR2(15) N 类别号
DZXLM VARCHAR2(60) Y 类别名
DW VARCHAR2(4) Y 单位
JYFS CHAR(1) N 经营方式
LSJ FLOAT N 实际零售价
XSSL FLOAT Y 销售数量
XSJE FLOAT Y 实际销售额
HSJJ FLOAT N 含税进价
HSJJJE FLOAT Y 含税成本
XSSLZB FLOAT Y 销售额
XSSLPM NUMBER Y 含税毛利
XSJEZB FLOAT Y 进销差价
XSJEPM FLOAT Y
GYS_SLZB FLOAT Y
GYS_SLPM FLOAT Y
GYS_JEZB FLOAT Y
GYS_JEPM FLOAT Y
KCSL FLOAT Y 总折扣
KCJE FLOAT Y 供应商折扣
BAK1 VARCHAR2(13) Y 商品条码
BAK2 VARCHAR2(10) Y 备用
库存表CC_STR_COM_HZ RQ DATE N 日期
SPTM VARCHAR2(13) N 商品编码
SPM VARCHAR2(60) Y 商品名称
MC VARCHAR2(15) N 卖场编码
PP VARCHAR2(6) Y 品牌编码
DZXL VARCHAR2(15) N 类别编码
GZ VARCHAR2(15) N 柜组
GYS VARCHAR2(15) N 供应商编码
JYFS FLOAT N 经营方式
ZHHSJJ FLOAT Y 最后含税进价
ZHBHSJJ FLOAT Y 最后不含税进价
KL FLOAT Y 扣率
LSJ FLOAT Y 零售价
KCSL FLOAT Y 库存数量
KCBHSJE FLOAT Y 库存不含税金额
KCHSJE FLOAT Y 库存含税金额
KCLSJE FLOAT Y 库存零售金额
历史日销售表
CC_JXC_GYS_DAY_HIS RQ DATE N 日期
MC VARCHAR2(4) N 卖场
GYS VARCHAR2(8) N 供应商号
GYSM VARCHAR2(60) Y 供应商名
SPTM VARCHAR2(13) N 商品编码
SPM VARCHAR2(60) Y 商品名
PP VARCHAR2(6) Y 品牌号
PPM VARCHAR2(20) Y 品牌名
CD VARCHAR2(6) Y 产地号
CDM VARCHAR2(32) Y 产地名
DZXL VARCHAR2(15) N 类别号
DZXLM VARCHAR2(60) Y 类别名
DW VARCHAR2(4) Y 单位
JYFS CHAR(1) N 经营方式
LSJ FLOAT N 实际零售价
XSSL FLOAT Y 销售数量
XSJE FLOAT Y 实际销售额
HSJJ FLOAT N 含税进价
HSJJJE FLOAT Y 含税成本
XSSLZB FLOAT Y 销售额 同类别销售数量占比
XSSLPM NUMBER Y 含税毛利
XSJEZB FLOAT Y 进销差价
XSJEPM FLOAT Y 同类别销售金额排名
GYS_SLZB FLOAT Y 本供应商销售数量占比
GYS_SLPM FLOAT Y 本供应商销售数量排名
GYS_JEZB FLOAT Y 本供应商销售金额占比
GYS_JEPM FLOAT Y 本供应商销售金额排名
KCSL FLOAT Y 总折扣
KCJE FLOAT Y 供应商折扣
BAK1 VARCHAR2(13) Y 商品条码
BAK2 VARCHAR2(10) Y 备用
SQL语句select *
from (select t.*, rownum rn
from (select a.kcsl,
a.spm,
c.name,
p.dsinventory,
d.dsinventory1,
e.dsinventory2,
g.dsinventory3,
f.dsinventory4
from (select gys, kCSL, sPM from CC_STR_COM_HZ) a,
(select name from INF_SHOP where fgs = 'C') c,
(select avg(dsinventory) as dsinventory
from (select (max(d.xssl) * 30) as dsinventory
from CC_JXC_GYS_DAY d
inner join CC_JXC_GYS_DAY_HIS his
on d.gys = his.gys
where his.rq in
(select distinct d.rq - 30
from CC_JXC_GYS_DAY d
group by d.rq
having count(*) >= 1)
and his.rq between his.rq and d.rq)) p,
(select avg(dsinventory1) as dsinventory1
from (select (max(d.xssl) * 7) as dsinventory1
from CC_JXC_GYS_DAY d
inner join CC_JXC_GYS_DAY_HIS his
on d.gys = his.gys
where his.rq in
(select distinct d.rq - 7
from CC_JXC_GYS_DAY d
group by d.rq
having count(*) >= 1)
and his.rq between his.rq and d.rq)) d,
(select avg(dsinventory2) as dsinventory2
from (select (max(d.xssl) * 14) as dsinventory2
from CC_JXC_GYS_DAY d
inner join CC_JXC_GYS_DAY_HIS his
on d.gys = his.gys
where his.rq in
(select distinct d.rq - 14
from CC_JXC_GYS_DAY d
group by d.rq
having count(*) >= 1)
and his.rq between his.rq and d.rq)) e,
(select avg(dsinventory4) as dsinventory4
from (select (max(d.xssl) * 45) as dsinventory4
from CC_JXC_GYS_DAY d
inner join CC_JXC_GYS_DAY_HIS his
on d.gys = his.gys
where his.rq in
(select distinct d.rq - 45
from CC_JXC_GYS_DAY d
group by d.rq
having count(*) >= 1)
and his.rq between his.rq and d.rq)) f,
(select avg(dsinventory3) as dsinventory3
from (select (max(d.xssl) * 30) as dsinventory3
from CC_JXC_GYS_DAY d
inner join CC_JXC_GYS_DAY_HIS his
on d.gys = his.gys
where his.rq in
(select distinct d.rq - 30
from CC_JXC_GYS_DAY d
group by d.rq
having count(*) >= 1)
and his.rq between his.rq and d.rq)) g) t
where rownum < 1000)
where rn > 1
解决方案 »
- 系统windows server2008 64位无法安装oracle10g 64位
- ora-06512,ora-04088错误
- oracle照片批量倒入问题？
- 请问如何更改oracle的系统时间?
- 【求助】大家帮我看看这个代码的问题在哪里
- 请各位帮帮忙~~~关于sql语句的问题
- 有人在win2003上装oracle成功吗？
- 存储过程返回记录集，我怎么在java里面获得记录的遍历呢？求助，100分谢谢
- ORACLE 游标丢数的问题
- 急，急 oracle写过程时查询视图，报表或视图不存在
- 大家帮忙分析一下，这条SQL语句的执行计划，谢了！
- 费用匹配-结束。累计匹配1536条记录。满足条件的1536条;实际匹配的1499条。
Predicate Information (identified by operation id):
--------------------------------------------------- 1 - filter("RN">1)
2 - filter(ROWNUM<1000)
13 - access("D"."GYS"="HIS"."GYS")
filter("HIS"."RQ"<="D"."RQ")
14 - access("HIS"."RQ"=INTERNAL_FUNCTION("$nso_col_1"))
16 - filter(COUNT(*)>=1)
19 - filter("HIS"."RQ">="HIS"."RQ")
25 - access("D"."GYS"="HIS"."GYS")
filter("HIS"."RQ"<="D"."RQ")
26 - access("HIS"."RQ"=INTERNAL_FUNCTION("$nso_col_1"))
28 - filter(COUNT(*)>=1)
31 - filter("HIS"."RQ">="HIS"."RQ")
33 - filter("FGS"='C')
38 - access("D"."GYS"="HIS"."GYS")
filter("HIS"."RQ"<="D"."RQ")
39 - access("HIS"."RQ"=INTERNAL_FUNCTION("$nso_col_1"))
41 - filter(COUNT(*)>=1)
44 - filter("HIS"."RQ">="HIS"."RQ")
50 - access("D"."GYS"="HIS"."GYS")
filter("HIS"."RQ"<="D"."RQ")
51 - access("HIS"."RQ"=INTERNAL_FUNCTION("$nso_col_1"))
53 - filter(COUNT(*)>=1)
56 - filter("HIS"."RQ">="HIS"."RQ")
62 - access("D"."GYS"="HIS"."GYS")
filter("HIS"."RQ"<="D"."RQ")
63 - access("HIS"."RQ"=INTERNAL_FUNCTION("$nso_col_1"))
65 - filter(COUNT(*)>=1)
68 - filter("HIS"."RQ">="HIS"."RQ")Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
79925 consistent gets
0 physical reads
0 redo size
45384 bytes sent via SQL*Net to client
1515 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
998 rows processed
----------------------------------------------------------
Plan hash value: 535012280--------------------------------------------------------------------------------
------------------------------------| Id | Operation | Name | Rows | Byt
es |TempSpc| Cost (%CPU)| Time |--------------------------------------------------------------------------------
------------------------------------| 0 | SELECT STATEMENT | | 999 | 1
41K| | 48827 (23)| 00:09:46 ||* 1 | VIEW | | 999 | 1
41K| | 48827 (23)| 00:09:46 ||* 2 | COUNT STOPKEY | | |
| | | || 3 | NESTED LOOPS | | 999 | 1
05K| | 48827 (23)| 00:09:46 || 4 | NESTED LOOPS | | 1 |
80 | | 48822 (23)| 00:09:46 || 5 | NESTED LOOPS | | 1 |
67 | | 39058 (23)| 00:07:49 || 6 | NESTED LOOPS | | 1 |
54 | | 29294 (23)| 00:05:52 || 7 | NESTED LOOPS | | 1 |
41 | | 19530 (23)| 00:03:55 || 8 | NESTED LOOPS | | 1 |
26 | | 19528 (23)| 00:03:55 || 9 | VIEW | | 1 |
13 | | 9764 (23)| 00:01:58 || 10 | SORT AGGREGATE | | 1 |
13 | | | || 11 | VIEW | | 1 |
13 | | 9764 (23)| 00:01:58 || 12 | SORT AGGREGATE | | 1 |
39 | | | ||* 13 | HASH JOIN | | 5723K| 2
12M| 5400K| 9764 (23)| 00:01:58 ||* 14 | HASH JOIN | | 167K| 34
32K| | 3549 (5)| 00:00:43 || 15 | VIEW | VW_NSO_5 | 1 |
6 | | 556 (18)| 00:00:07 ||* 16 | FILTER | | |
| | | || 17 | HASH GROUP BY | | 1 |
8 | | 556 (18)| 00:00:07 || 18 | INDEX FAST FULL SCAN| INDEX_RQ_MC_GYS | 541K| 42
31K| | 474 (3)| 00:00:06 ||* 19 | TABLE ACCESS FULL | CC_JXC_GYS_DAY_HIS | 502K| 73
54K| | 2983 (2)| 00:00:36 || 20 | TABLE ACCESS FULL | CC_JXC_GYS_DAY | 541K| 95
21K| | 3163 (2)| 00:00:38 || 21 | VIEW | | 1 |
13 | | 9764 (23)| 00:01:58 || 22 | SORT AGGREGATE | | 1 |
13 | | | || 23 | VIEW | | 1 |
13 | | 9764 (23)| 00:01:58 || 24 | SORT AGGREGATE | | 1 |
39 | | | ||* 25 | HASH JOIN | | 5723K| 2
12M| 5400K| 9764 (23)| 00:01:58 ||* 26 | HASH JOIN | | 167K| 34
32K| | 3549 (5)| 00:00:43 || 27 | VIEW | VW_NSO_4 | 1 |
6 | | 556 (18)| 00:00:07 ||* 28 | FILTER | | |
| | | || 29 | SORT GROUP BY | | 1 |
8 | | 556 (18)| 00:00:07 || 30 | INDEX FAST FULL SCAN| INDEX_RQ_MC_GYS | 541K| 42
31K| | 474 (3)| 00:00:06 ||* 31 | TABLE ACCESS FULL | CC_JXC_GYS_DAY_HIS | 502K| 73
54K| | 2983 (2)| 00:00:36 || 32 | TABLE ACCESS FULL | CC_JXC_GYS_DAY | 541K| 95
21K| | 3163 (2)| 00:00:38 ||* 33 | TABLE ACCESS FULL | INF_SHOP | 2 |
30 | | 2 (0)| 00:00:01 || 34 | VIEW | | 1 |
13 | | 9764 (23)| 00:01:58 || 35 | SORT AGGREGATE | | 1 |
13 | | | || 36 | VIEW | | 1 |
13 | | 9764 (23)| 00:01:58 || 37 | SORT AGGREGATE | | 1 |
39 | | | ||* 38 | HASH JOIN | | 5723K| 2
12M| 5400K| 9764 (23)| 00:01:58 ||* 39 | HASH JOIN | | 167K| 34
32K| | 3549 (5)| 00:00:43 || 40 | VIEW | VW_NSO_2 | 1 |
6 | | 556 (18)| 00:00:07 ||* 41 | FILTER | | |
| | | || 42 | SORT GROUP BY | | 1 |
8 | | 556 (18)| 00:00:07 || 43 | INDEX FAST FULL SCAN | INDEX_RQ_MC_GYS | 541K| 42
31K| | 474 (3)| 00:00:06 ||* 44 | TABLE ACCESS FULL | CC_JXC_GYS_DAY_HIS | 502K| 73
54K| | 2983 (2)| 00:00:36 || 45 | TABLE ACCESS FULL | CC_JXC_GYS_DAY | 541K| 95
21K| | 3163 (2)| 00:00:38 || 46 | VIEW | | 1 |
13 | | 9764 (23)| 00:01:58 || 47 | SORT AGGREGATE | | 1 |
13 | | | || 48 | VIEW | | 1 |
13 | | 9764 (23)| 00:01:58 || 49 | SORT AGGREGATE | | 1 |
39 | | | ||* 50 | HASH JOIN | | 5723K| 2
12M| 5400K| 9764 (23)| 00:01:58 ||* 51 | HASH JOIN | | 167K| 34
32K| | 3549 (5)| 00:00:43 || 52 | VIEW | VW_NSO_3 | 1 |
6 | | 556 (18)| 00:00:07 ||* 53 | FILTER | | |
| | | || 54 | SORT GROUP BY | | 1 |
8 | | 556 (18)| 00:00:07 || 55 | INDEX FAST FULL SCAN | INDEX_RQ_MC_GYS | 541K| 42
31K| | 474 (3)| 00:00:06 ||* 56 | TABLE ACCESS FULL | CC_JXC_GYS_DAY_HIS | 502K| 73
54K| | 2983 (2)| 00:00:36 || 57 | TABLE ACCESS FULL | CC_JXC_GYS_DAY | 541K| 95
21K| | 3163 (2)| 00:00:38 || 58 | VIEW | | 1 |
13 | | 9764 (23)| 00:01:58 || 59 | SORT AGGREGATE | | 1 |
13 | | | || 60 | VIEW | | 1 |
13 | | 9764 (23)| 00:01:58 || 61 | SORT AGGREGATE | | 1 |
39 | | | ||* 62 | HASH JOIN | | 5723K| 2
12M| 5400K| 9764 (23)| 00:01:58 ||* 63 | HASH JOIN | | 167K| 34
32K| | 3549 (5)| 00:00:43 || 64 | VIEW | VW_NSO_1 | 1 |
6 | | 556 (18)| 00:00:07 ||* 65 | FILTER | | |
| | | || 66 | SORT GROUP BY | | 1 |
8 | | 556 (18)| 00:00:07 || 67 | INDEX FAST FULL SCAN | INDEX_RQ_MC_GYS | 541K| 42
31K| | 474 (3)| 00:00:06 ||* 68 | TABLE ACCESS FULL | CC_JXC_GYS_DAY_HIS | 502K| 73
54K| | 2983 (2)| 00:00:36 || 69 | TABLE ACCESS FULL | CC_JXC_GYS_DAY | 541K| 95
21K| | 3163 (2)| 00:00:38 || 70 | TABLE ACCESS FULL | CC_STR_COM_HZ | 999 | 279
72 | | 5 (0)| 00:00:01 |--------------------------------------------------------------------------------
------------------------------------