2 sinat 30214689 sinat_30214689 于 2016.03.09 20:15 提问

求大神帮忙 sql 查询问题 5C
sql

create table lw_sales(dept_id varchar2(6), sale_date date, goods_type varchar2(4), sale_cnt number(10));
COMMENT ON TABLE LW_SALES IS '销售数据测试表。'
/
SET DEFINE OFF;
Insert into LW_SALES
(DEPT_ID, SALE_DATE, GOODS_TYPE, SALE_CNT)
Values
('S00', TO_DATE('03/16/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'G00', 400);
Insert into LW_SALES
(DEPT_ID, SALE_DATE, GOODS_TYPE, SALE_CNT)
Values
('S02', TO_DATE('04/05/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'G00', 400);
Insert into LW_SALES
(DEPT_ID, SALE_DATE, GOODS_TYPE, SALE_CNT)
Values
('S01', TO_DATE('03/17/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'G00', 700);
Insert into LW_SALES
(DEPT_ID, SALE_DATE, GOODS_TYPE, SALE_CNT)
Values
('S01', TO_DATE('04/13/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'G00', 900);
Insert into LW_SALES
(DEPT_ID, SALE_DATE, GOODS_TYPE, SALE_CNT)
Values
('S00', TO_DATE('04/18/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'G01', 300);
Insert into LW_SALES
(DEPT_ID, SALE_DATE, GOODS_TYPE, SALE_CNT)
Values
('S00', TO_DATE('03/15/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'G01', 30);
Insert into LW_SALES
(DEPT_ID, SALE_DATE, GOODS_TYPE, SALE_CNT)
Values
('S01', TO_DATE('04/15/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'G01', 400);
Insert into LW_SALES
(DEPT_ID, SALE_DATE, GOODS_TYPE, SALE_CNT)
Values
('S02', TO_DATE('03/10/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'G01', 200);
Insert into LW_SALES
(DEPT_ID, SALE_DATE, GOODS_TYPE, SALE_CNT)
Values
('S02', TO_DATE('03/06/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'G01', 70);
Insert into LW_SALES
(DEPT_ID, SALE_DATE, GOODS_TYPE, SALE_CNT)
Values
('S01', TO_DATE('03/17/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'G01', 400);
Insert into LW_SALES
(DEPT_ID, SALE_DATE, GOODS_TYPE, SALE_CNT)
Values
('S01', TO_DATE('04/07/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'G02', 300);
Insert into LW_SALES
(DEPT_ID, SALE_DATE, GOODS_TYPE, SALE_CNT)
Values
('S01', TO_DATE('03/14/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'G02', 300);
Insert into LW_SALES
(DEPT_ID, SALE_DATE, GOODS_TYPE, SALE_CNT)
Values
('S02', TO_DATE('03/09/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'G02', 900);
Insert into LW_SALES
(DEPT_ID, SALE_DATE, GOODS_TYPE, SALE_CNT)
Values
('S01', TO_DATE('03/16/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'G02', 800);
Insert into LW_SALES
(DEPT_ID, SALE_DATE, GOODS_TYPE, SALE_CNT)
Values
('S02', TO_DATE('04/22/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'G03', 300);
Insert into LW_SALES
(DEPT_ID, SALE_DATE, GOODS_TYPE, SALE_CNT)
Values
('S00', TO_DATE('03/21/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'G03', 200);
Insert into LW_SALES
(DEPT_ID, SALE_DATE, GOODS_TYPE, SALE_CNT)
Values
('S01', TO_DATE('03/17/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'G03', 600);
Insert into LW_SALES
(DEPT_ID, SALE_DATE, GOODS_TYPE, SALE_CNT)
Values
('S02', TO_DATE('04/05/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'G03', 800);
Insert into LW_SALES
(DEPT_ID, SALE_DATE, GOODS_TYPE, SALE_CNT)
Values
('S01', TO_DATE('04/03/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'G03', 200);
Insert into LW_SALES
(DEPT_ID, SALE_DATE, GOODS_TYPE, SALE_CNT)
Values
('S00', TO_DATE('03/06/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'G03', 800);
Insert into LW_SALES
(DEPT_ID, SALE_DATE, GOODS_TYPE, SALE_CNT)
Values
('S01', TO_DATE('03/09/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'G04', 800);
Insert into LW_SALES
(DEPT_ID, SALE_DATE, GOODS_TYPE, SALE_CNT)
Values
('S01', TO_DATE('03/07/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'G04', 30);
Insert into LW_SALES
(DEPT_ID, SALE_DATE, GOODS_TYPE, SALE_CNT)
Values
('S01', TO_DATE('04/20/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'G04', 900);
Insert into LW_SALES
(DEPT_ID, SALE_DATE, GOODS_TYPE, SALE_CNT)
Values
('S01', TO_DATE('03/21/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'G04', 800);
Insert into LW_SALES
(DEPT_ID, SALE_DATE, GOODS_TYPE, SALE_CNT)
Values
('S00', TO_DATE('03/02/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'G04', 500);
Insert into LW_SALES
(DEPT_ID, SALE_DATE, GOODS_TYPE, SALE_CNT)
Values
('S00', TO_DATE('03/10/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'G04', 500);
Insert into LW_SALES
(DEPT_ID, SALE_DATE, GOODS_TYPE, SALE_CNT)
Values
('S02', TO_DATE('04/06/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'G04', 300);
Insert into LW_SALES
(DEPT_ID, SALE_DATE, GOODS_TYPE, SALE_CNT)
Values
('S00', TO_DATE('03/07/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'G05', 400);
Insert into LW_SALES
(DEPT_ID, SALE_DATE, GOODS_TYPE, SALE_CNT)
Values
('S02', TO_DATE('03/06/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'G05', 200);
Insert into LW_SALES
(DEPT_ID, SALE_DATE, GOODS_TYPE, SALE_CNT)
Values
('S00', TO_DATE('03/06/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'G05', 500);
COMMIT;

1、求出LW_SALES表中每天的销售总额占当月总销售额的百分比和占所有月份总销售额的百分比,
要求四舍五入到小数点后四位,如0.1234,并按类似于12.34%这样的格式输出百分比值,
结果按销售日期正序排列。

本题输出结果表头(总共应该19条记录)
SALE_DATE RATIO_MONTH RATIO_ALLMONTHS

2、已知LW_SALES表中同一部门同一天对同一货物只有一笔销售记录,
现在先按销售日期(SALE_DATE)正序再按销售额(SALE_CNT)倒序最后按部门编号(DEPT_ID)正序和货物类型(GOODS_TYPE)正序排列,
求出全公司的累计销售额及按部门的累计销售额,按销售日期(SALE_DATE)正序排列求出全公司最近30天的累计销售额。
输出结果先按销售日期正序再按销售额倒序最后按部门编号正序和货物类型正序排列。

本题输出结果表头
DEPT_ID SALE_DATE GOODS_TYPE SALE_CNT CUM_SALE_CNT CUM_30DAYS_SALE_CNT CUM_DEPT_SALE_CNT

前几行输出结果如下
S00 2013-03-02 G04 500 500 500 500
S00 2013-03-06 G03 800 1300 2070 1300
S00 2013-03-06 G05 500 1800 2070 1800
S02 2013-03-06 G05 200 2000 2070 200
S02 2013-03-06 G01 70 2070 2070 270
S00 2013-03-07 G05 400 2470 2500 2200
S01 2013-03-07 G04 30 2500 2500 30


1个回答

huamanlou02
huamanlou02   2016.03.09 20:21

可以看看这个链接sql命令

sinat_30214689
sinat_30214689 页面不存在呀
接近 2 年之前 回复
Csdn user default icon
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!