公司SQL考核及小结(Oracle)

一.数据库初始化脚本:

Create TABLE HAND_CUSTOMERS
(
 CUSTOMERS_NO Varchar2(10),
 CUSTOMERS_NAME Varchar2(30),
 CUSTOMERS_GENDER Varchar2(3),
 CUSTOMERS_BIRTH_DATE Date
);
comment on TABLE HAND_CUSTOMERS is '顾客表';
comment on COLUMN HAND_CUSTOMERS.CUSTOMERS_NO is '客户编号';
comment on COLUMN HAND_CUSTOMERS.CUSTOMERS_NAME is '客户名称';
comment on COLUMN HAND_CUSTOMERS.CUSTOMERS_GENDER is '客户性别';
comment on COLUMN HAND_CUSTOMERS.CUSTOMERS_BIRTH_DATE is '客户出生日期';

 
Create TABLE HAND_SELLERS
(
 SELLER_NO Varchar2(10),
 SELLER_NAME Varchar2(30),
 MANAGER_NO  Varchar2(10)
);
comment on TABLE HAND_SELLERS is '销售员表';
comment on COLUMN HAND_SELLERS.SELLER_NO is '销售员编码';
comment on COLUMN HAND_SELLERS.SELLER_NAME is '销售员名称';
comment on COLUMN HAND_SELLERS.MANAGER_NO  is '销售员经理'; 


---
 
Create Table HAND_GOODS
(
 GOODS_NO Varchar2(10),
 GOODS_NAME Varchar2(30),
 GOODS_PRICE Number
);
comment on table HAND_GOODS is '商品表';
comment on column HAND_GOODS.GOODS_NO is '商品编码';
comment on column HAND_GOODS.GOODS_NAME is '商品名称';
comment on column HAND_GOODS.GOODS_PRICE is '商品单价';

----
Create Table HAND_SALES_RECORDS
(
 CUSTOMERS_NO Varchar2(10),
 SELLER_NO Varchar2(10),
 GOODS_NO Varchar2(10),
 SALES_QUANTY Number,
 SALES_DATE Date
);
comment on table HAND_SALES_RECORDS is '销售记录表';
comment on column HAND_SALES_RECORDS.CUSTOMERS_NO is '客户编号';
comment on column HAND_SALES_RECORDS.SELLER_NO is '销售员编码';
comment on column HAND_SALES_RECORDS.GOODS_NO is '商品编码';
comment on column HAND_SALES_RECORDS.SALES_QUANTY is '销售数量';
comment on column HAND_SALES_RECORDS.SALES_DATE is '销售记录';
  




------
INSERT INTO HAND_CUSTOMERS(CUSTOMERS_NO, CUSTOMERS_NAME, CUSTOMERS_GENDER, CUSTOMERS_BIRTH_DATE) values 
('C001', '张三', '', TO_DATE('1990/1/1','YYYY/MM/DD'));
INSERT INTO HAND_CUSTOMERS(CUSTOMERS_NO, CUSTOMERS_NAME, CUSTOMERS_GENDER, CUSTOMERS_BIRTH_DATE) values 
('C002', '李四', '', TO_DATE('1994/3/2','YYYY-MM-DD'));
INSERT INTO HAND_CUSTOMERS(CUSTOMERS_NO, CUSTOMERS_NAME, CUSTOMERS_GENDER, CUSTOMERS_BIRTH_DATE) values 
('C003', '吴鹏', '', TO_DATE('1996/2/19','YYYY-MM-DD'));
INSERT INTO HAND_CUSTOMERS(CUSTOMERS_NO, CUSTOMERS_NAME, CUSTOMERS_GENDER, CUSTOMERS_BIRTH_DATE) values 
('C004', '琴沁', '', TO_DATE('1997/1/4','YYYY-MM-DD'));
INSERT INTO HAND_CUSTOMERS(CUSTOMERS_NO, CUSTOMERS_NAME, CUSTOMERS_GENDER, CUSTOMERS_BIRTH_DATE) values 
('C005', '王丽', '', TO_DATE('1998/1/5','YYYY-MM-DD'));
INSERT INTO HAND_CUSTOMERS(CUSTOMERS_NO, CUSTOMERS_NAME, CUSTOMERS_GENDER, CUSTOMERS_BIRTH_DATE) values 
('C006', '李波', '', TO_DATE('1998/4/6','YYYY-MM-DD'));
INSERT INTO HAND_CUSTOMERS(CUSTOMERS_NO, CUSTOMERS_NAME, CUSTOMERS_GENDER, CUSTOMERS_BIRTH_DATE) values 
('C007', '刘玉', '', TO_DATE('1998/7/7','YYYY-MM-DD'));
INSERT INTO HAND_CUSTOMERS(CUSTOMERS_NO, CUSTOMERS_NAME, CUSTOMERS_GENDER, CUSTOMERS_BIRTH_DATE) values 
('C008', '萧蓉', '', TO_DATE('1998/8/21','YYYY-MM-DD'));
INSERT INTO HAND_CUSTOMERS(CUSTOMERS_NO, CUSTOMERS_NAME, CUSTOMERS_GENDER, CUSTOMERS_BIRTH_DATE) values 
('C009', '陈萧晓', '', TO_DATE('1994/12/1','YYYY-MM-DD'));
INSERT INTO HAND_CUSTOMERS(CUSTOMERS_NO, CUSTOMERS_NAME, CUSTOMERS_GENDER, CUSTOMERS_BIRTH_DATE) values 
('C010', '陈美', '', TO_DATE('1999/10/10','YYYY-MM-DD'));

------
INSERT INTO HAND_SELLERS(SELLER_NO, SELLER_NAME, MANAGER_NO ) values 
('X001', '销售A', '');
INSERT INTO HAND_SELLERS(SELLER_NO, SELLER_NAME, MANAGER_NO ) values 
('X002', '销售B', 'X001');
INSERT INTO HAND_SELLERS(SELLER_NO, SELLER_NAME, MANAGER_NO ) values 
('X003', '销售C', 'X001');
INSERT INTO HAND_SELLERS(SELLER_NO, SELLER_NAME, MANAGER_NO ) values 
('X004', '销售D', 'X003');
INSERT INTO HAND_SELLERS(SELLER_NO, SELLER_NAME, MANAGER_NO ) values 
('X005', '销售E', 'X003');
-------
INSERT INTO HAND_GOODS(GOODS_NO, GOODS_NAME, GOODS_PRICE) values 
('GOODS001', '商品A', 120);
INSERT INTO HAND_GOODS(GOODS_NO, GOODS_NAME, GOODS_PRICE) values 
('GOODS002', '商品B', 159);
INSERT INTO HAND_GOODS(GOODS_NO, GOODS_NAME, GOODS_PRICE) values 
('GOODS003', '商品C', 349);
INSERT INTO HAND_GOODS(GOODS_NO, GOODS_NAME, GOODS_PRICE) values 
('GOODS004', '商品D', 256);
INSERT INTO HAND_GOODS(GOODS_NO, GOODS_NAME, GOODS_PRICE) values 
('GOODS005', '商品E', 412);
INSERT INTO HAND_GOODS(GOODS_NO, GOODS_NAME, GOODS_PRICE) values 
('GOODS006', '商品F', 342);
INSERT INTO HAND_GOODS(GOODS_NO, GOODS_NAME, GOODS_PRICE) values 
('GOODS007', '商品G', 234);
INSERT INTO HAND_GOODS(GOODS_NO, GOODS_NAME, GOODS_PRICE) values 
('GOODS008', '商品H', 776);
INSERT INTO HAND_GOODS(GOODS_NO, GOODS_NAME, GOODS_PRICE) values 
('GOODS009', '商品I', 123);
INSERT INTO HAND_GOODS(GOODS_NO, GOODS_NAME, GOODS_PRICE) values 
('GOODS010', '商品J', null);
---
INSERT INTO HAND_SALES_RECORDS(CUSTOMERS_NO, SELLER_NO, GOODS_NO, SALES_QUANTY, SALES_DATE) values 
('C001', 'X001', 'GOODS001', 32, TO_DATE('2019/6/8','YYYY-MM-DD'));
INSERT INTO HAND_SALES_RECORDS(CUSTOMERS_NO, SELLER_NO, GOODS_NO, SALES_QUANTY, SALES_DATE) values 
('C002', 'X002', 'GOODS001', 39, TO_DATE('2019/6/18','YYYY-MM-DD'));
INSERT INTO HAND_SALES_RECORDS(CUSTOMERS_NO, SELLER_NO, GOODS_NO, SALES_QUANTY, SALES_DATE) values 
('C002', 'X003', 'GOODS003', 20, TO_DATE('2019/6/19','YYYY-MM-DD'));
INSERT INTO HAND_SALES_RECORDS(CUSTOMERS_NO, SELLER_NO, GOODS_NO, SALES_QUANTY, SALES_DATE) values 
('C004', 'X004', 'GOODS004', 4, TO_DATE('2019/6/11','YYYY-MM-DD'));
INSERT INTO HAND_SALES_RECORDS(CUSTOMERS_NO, SELLER_NO, GOODS_NO, SALES_QUANTY, SALES_DATE) values 
('C005', 'X005', 'GOODS005', 60, TO_DATE('2019/6/12','YYYY-MM-DD'));
INSERT INTO HAND_SALES_RECORDS(CUSTOMERS_NO, SELLER_NO, GOODS_NO, SALES_QUANTY, SALES_DATE) values 
('C006', 'X003', 'GOODS006', 30, TO_DATE('2019/6/13','YYYY-MM-DD'));
INSERT INTO HAND_SALES_RECORDS(CUSTOMERS_NO, SELLER_NO, GOODS_NO, SALES_QUANTY, SALES_DATE) values 
('C006', 'X002', 'GOODS007', 36, TO_DATE('2019/6/14','YYYY-MM-DD'));
INSERT INTO HAND_SALES_RECORDS(CUSTOMERS_NO, SELLER_NO, GOODS_NO, SALES_QUANTY, SALES_DATE) values 
('C004', 'X001', 'GOODS006', 40, TO_DATE('2019/6/15','YYYY-MM-DD'));
INSERT INTO HAND_SALES_RECORDS(CUSTOMERS_NO, SELLER_NO, GOODS_NO, SALES_QUANTY, SALES_DATE) values 
('C001', 'X003', 'GOODS001', 10, TO_DATE('2019/6/19','YYYY-MM-DD'));
INSERT INTO HAND_SALES_RECORDS(CUSTOMERS_NO, SELLER_NO, GOODS_NO, SALES_QUANTY, SALES_DATE) values 
('C002', 'X002', 'GOODS002', 41, TO_DATE('2019/4/11','YYYY-MM-DD'));
INSERT INTO HAND_SALES_RECORDS(CUSTOMERS_NO, SELLER_NO, GOODS_NO, SALES_QUANTY, SALES_DATE) values 
('C003', 'X003', 'GOODS003', 30, TO_DATE('2019/3/12','YYYY-MM-DD'));
INSERT INTO HAND_SALES_RECORDS(CUSTOMERS_NO, SELLER_NO, GOODS_NO, SALES_QUANTY, SALES_DATE) values 
('C003', 'X003', 'GOODS004', 60, TO_DATE('2019/6/11','YYYY-MM-DD'));
INSERT INTO HAND_SALES_RECORDS(CUSTOMERS_NO, SELLER_NO, GOODS_NO, SALES_QUANTY, SALES_DATE) values 
('C003', 'X002', 'GOODS005', 76, TO_DATE('2019/6/19','YYYY-MM-DD'));
INSERT INTO HAND_SALES_RECORDS(CUSTOMERS_NO, SELLER_NO, GOODS_NO, SALES_QUANTY, SALES_DATE) values 
('C003', 'X001', 'GOODS006', 68, TO_DATE('2019/6/25','YYYY-MM-DD'));

INSERT INTO HAND_SALES_RECORDS(CUSTOMERS_NO, SELLER_NO, GOODS_NO, SALES_QUANTY, SALES_DATE) values 
('C004', 'X005', 'GOODS005', 32, TO_DATE('2018/6/18','YYYY-MM-DD'));

COMMIT;
View Code

 

二.Sql语句题:

  1.条件:查询没有卖出过“商品B”的销售人员信息 显示:销售员编码,销售员名称,管理员编码,管理员名称 :

--1
SELECT hs.seller_no
,hs.seller_name
,hsm.seller_no
,hsm.seller_name
FROM hand_sellers hs
,hand_sellers hsm
WHERE hs.manager_no = hsm.seller_no(+)
AND NOT EXISTS (SELECT 1
FROM hand_sales_records hsr
,hand_goods hg
WHERE hsr.goods_no = hg.goods_no
AND hg.goods_name = '商品B'
AND hsr.seller_no = hs.seller_no);
--2
SELECT hs.seller_no
,hs.seller_name
,hsm.seller_no
,hsm.seller_name
FROM hand_sellers hs
,hand_sellers hsm
WHERE hs.manager_no = hsm.seller_no(+)
AND hs.seller_no NOT IN (SELECT hsr.seller_no
FROM hand_sales_records hsr
,hand_goods hg
WHERE hsr.goods_no = hg.goods_no
AND hg.goods_name = '商品B');
--考点:外连接、NOT EXISTS 和 NOT IN 的用法及区别     

    小结:

      1.in和exists:

        in是把外表和内表作hash连接(会用到外表上的索引),而exists是对外表作loop循环(用到内表上的索引),每次loop循环再对内表进行查询,如果查询的两个表大小相当,那么用in和exists差别不大;如果两个表中一个较小一个较大,则子查询表大的用exists,子查询表小的用in;

      2.not in和not exists:

        not in 逻辑上不完全等同于not exists。使用not in时,如果子查询中返回的任意一条记录含有空值,则查询将不返回任何记录。如果子查询字段有非空限制,这时可以使用not in,并且可以通过提示让它用hasg_aj或merge_aj连接。如果查询语句使用了not in,那么对内外表都进行全表扫描,没有用到索引;而not exists的子查询依然能用到表上的索引。所以无论哪个表大,用not exists都比not in 要快

      参照:https://blog.csdn.net/baidu_37107022/article/details/77278381

  2.条件:查询6 月中旬,所有顾客的消费情况。显示:顾客编号、顾客名称、顾客性别、销售商品、销售时间、销售数量排序:消费数量从高到低。说明:如果没有消费记录,销售商品、销售时间、销售数量留空

  

--1
SELECT hc.customers_no
,hc.customers_name
,hc.customers_gender
,hg.goods_name
,hsr.sales_date
,hsr.sales_quanty
FROM hand_sales_records hsr
,hand_goods hg
,hand_customers hc
WHERE hsr.goods_no = hg.goods_no(+)
AND hsr.customers_no(+) = hc.customers_no
--AND hsr.sales_date(+) BETWEEN to_date('2019-06-10', 'yyyy-mm-dd') AND
to_date('2019-06-20', 'yyyy-mm-dd')
AND to_char(hsr.sales_date(+), 'MM') = '06'
AND to_char(hsr.sales_date(+), 'DD') BETWEEN 10 AND 20
ORDER BY hsr.sales_quanty DESC;
--2
SELECT hc.customers_no
,hc.customers_name
,hc.customers_gender
,hg.goods_name
,hsr.sales_date
,hsr.sales_quanty
FROM hand_sales_records hsr
,hand_goods hg
,hand_customers hc
WHERE hsr.goods_no = hg.goods_no
AND hsr.customers_no = hc.customers_no
AND hsr.sales_date BETWEEN to_date('2019-06-10', 'yyyy-mm-dd') AND
to_date('2019-06-20', 'yyyy-mm-dd')
ORDER BY hsr.sales_quanty DESC;
--考点:外连接,日期函数

    小结:

        1.查两表关联列相等的数据用内连接。
      2.Col_L是Col_R的子集时用右外连接。
      3.Col_R是Col_L的子集时用左外连接。
      4.Col_R和Col_L彼此有交集但彼此互不为子集时候用全外。
      5.求差操作的时候用联合查询。

  3.条件:查询购买记录3条以上的顾客信息。 显示:顾客编号、顾客名称、顾客出生日期 :

--1
SELECT hc.customers_no
,hc.customers_name
,hc.customers_birth_date
FROM hand_customers hc
WHERE hc.customers_no IN (SELECT hsr.customers_no
FROM hand_sales_records hsr
GROUP BY hsr.customers_no HAVING COUNT(1) > 3);
--2
SELECT hc.customers_no
,hc.customers_name
,hc.customers_birth_date
FROM hand_customers hc
WHERE EXISTS (SELECT 1
FROM hand_sales_records hsr
WHERE hc.customers_no = hsr.customers_no
GROUP BY hsr.customers_no
HAVING COUNT(1) > 3);

 

    小结:  

      where是筛选行,having是筛选已经查询出来的字段。

  4.条件:查询顾客中姓氏为"张""李""刘"的顾客信息显示:顾客编号、顾客名称、顾客,出生日期。排序:顾客出生日期从近到远。说明:正则表达式。除正则表达式以外的其他方式:

  

--正则
SELECT hc.customers_no
,hc.customers_name
,hc.customers_birth_date
FROM hand_customers hc
WHERE regexp_like(hc.customers_name, '^张.*|^李.*|^刘.*');
--Like
SELECT hc.customers_no
,hc.customers_name
,hc.customers_birth_date
FROM hand_customers hc
WHERE (hc.customers_name LIKE '张%' OR hc.customers_name LIKE '李%' OR
hc.customers_name LIKE '刘%');

     小结:

      还可以使用substring()等函数提取第一个字符看是否在要查找的条件里面

  5.条件:查询商品信息,当商品价格在100-199 时 打9 折,在200-299 时打8 折,在300 以上打7 折。显示:商品编码、商品名称、商品打折前单价、商品打折后单价:

SELECT hg.goods_no
,hg.goods_name
,hg.goods_price
,CASE
WHEN hg.goods_price BETWEEN 100 AND 199 THEN
hg.goods_price * 0.9
WHEN hg.goods_price BETWEEN 200 AND 299 THEN
hg.goods_price * 0.8
WHEN hg.goods_price >= 300 THEN
hg.goods_price * 0.7
ELSE
hg.goods_price
END changed_goods_price
FROM hand_goods hg

   6.查询生日在当前月份的顾客信息。显示:顾客名称,顾客生日,顾客购买总数量。排序:顾客生日:

SELECT hc.customers_no
,hc.customers_name
,hc.customers_birth_date
,(select sum(hsr.sales_quanty) from hand_sales_records hsr where
hsr.customers_no = hc.customers_no )
FROM hand_customers hc
WHERE to_char(hc.customers_birth_date,'MM') =to_char(sysdate,'MM') ;
--考点: 日期函数,子查询

     小结:

        就考察了子查询,以及to_char函数转换为字符串的函数,注意对应的to_date将字符串转换为日期

  7.条件:查询商品单价在100 到199 之间的商品信息。显示:商品名称、单价。说明:单价为空的商品也进行显示:

--1
SELECT hg.goods_no
,hg.goods_name
,hg.goods_price
FROM hand_goods hg
where nvl( hg.goods_price,100) BETWEEN 100 AND 199;
--2
SELECT hg.goods_no
,hg.goods_name
,hg.goods_price
FROM hand_goods hg
WHERE hg.goods_price BETWEEN 100 AND 199
OR hg.goods_price IS NULL

     小结:

      就考察了between..and的用法(包括前面,不包括后面),以及nvl()函数,注意nvl(expr1,expr2)和nvl2(expr1,expr2,expr3)的区别

  8.条件:查询所有销售员的销售总数量。显示:销售员编码、销售员名称、销售数量、销售名次。排序:按照销售数量进行倒序。说明:如果有销售数量相同的,销售名次如下输出。销售汇总数量60,60,57 名次 1,1,3:

SELECT hs.seller_no
,hs.seller_name
,SUM(sr.sales_quanty) sales_quanty
,RANK() OVER(ORDER BY SUM(sr.sales_quanty) DESC) rank_level
FROM hand_sales_records sr
,hand_sellers hs
WHERE sr.seller_no(+) = hs.seller_no
GROUP BY hs.seller_no
,hs.seller_name

 

    小结:

      注意开窗函数【分析函数提供一系列比较高级的SQL功能。分析函数时建立在数据窗口(over在一定的数据库范 围进行数据分析),在一定的数据范围进行排序、汇总】over(partition by...order by...)其中partition by表示以什么分组,如果没有则使用group by的分组,rank()【用于返回结果集的分区内每行的排名,行的排名是相关行之前的排名数加一,如果排序的标准相同,则排名也相同】和dense_rank()【与rank函数类似,dense_rank函数在生成序号时是连续的,而rank函数生成的序号有可能不连续】,row_number()【为查询出来的每一行记录生成一个序号,依次排序且不会重复】。还值得注意的是MySQL应该是在8.0之后才有开窗函数(查询资料),可以使用自连接比较数量排序。

  9.条件:查询销售数量高于商品“商品A”的最大销售数量的所有销售记录。显示: 商品名称、销售员名称、顾客名称、销售数量、销售日期。 with 不计入(with 和子查询算一种方法):

--sn1
SELECT g.goods_name
,hs.seller_name
,c.customers_name
,sr.sales_quanty
,sr.sales_date
FROM hand_goods g
,hand_sales_records sr
,hand_sellers hs
,hand_customers c
WHERE 1 = 1
AND sr.seller_no = hs.seller_no(+)
AND sr.goods_no = g.goods_no(+)
AND sr.customers_no = c.customers_no
AND sr.sales_quanty > (SELECT MAX(sr1.sales_quanty)
FROM hand_sales_records sr1
,hand_goods g1
WHERE sr1.goods_no = g1.goods_no
AND g1.goods_name = '商品A');
--sn2
SELECT g.goods_name
,hs.seller_name
,c.customers_name
,sr.sales_quanty
,sr.sales_date
FROM hand_goods g
,hand_sales_records sr
,hand_sellers hs
,hand_customers c
WHERE 1 = 1
AND sr.seller_no = hs.seller_no(+)
AND sr.goods_no = g.goods_no(+)
AND sr.customers_no = c.customers_no
AND sr.sales_quanty > ALL (SELECT sr1.sales_quanty
FROM hand_sales_records sr1
,hand_goods g1
WHERE sr1.goods_no = g1.goods_no
AND g1.goods_name = '商品A');
--sn3
SELECT g.goods_name
,hs.seller_name
,c.customers_name
,sr.sales_quanty
,sr.sales_date
FROM hand_goods g
,hand_sales_records sr
,hand_sellers hs
,hand_customers c
WHERE 1 = 1
AND sr.seller_no = hs.seller_no(+)
AND sr.goods_no = g.goods_no(+)
AND sr.customers_no = c.customers_no
AND EXISTS (SELECT 1
FROM hand_sales_records sr1
,hand_goods g1
WHERE sr1.goods_no = g1.goods_no
AND g1.goods_name = '商品A'
AND sr.sales_quanty > sr1.sales_quanty);

    小结:

      考核外连接语法,Oracle使用+可以简化外连接SQL(left/right join),主要是找准关系,需要返回哪边的所有行,然后就是ALL(注意all和any的区别,如题all是大于查询出的所有,即比结果中的最大的还大,而any只要是其中任意一个就行),EXISTS(exists和in的区别,exists是遍历外表在查询,而in1是遍历查询出的内表结果)的用法。

  10.条件:分别根据 商品+销售员+顾客、商品+销售员 、销售员、总销售数量的维度俩统计销售数量。显示:商品、销售员、顾客、汇总销售数量:

SELECT g.goods_name
,hs.seller_name
,c.customers_name
,SUM(sr.sales_quanty)
FROM hand_goods g
,hand_sales_records sr
,hand_sellers hs
,hand_customers c
WHERE 1 = 1
AND sr.seller_no = hs.seller_no(+)
AND sr.goods_no = g.goods_no(+)
AND sr.customers_no = c.customers_no
GROUP BY ROLLUP(g.goods_name, hs.seller_name, c.customers_name)

    小结:

      注ROLLUP和CUBE的区别。使用ROLLUP产生常规分组汇总行 以及分组小计,Rollup 后面跟了n个字段,就将进行n+1次分组,从右到左每次减少一个字段进行分组;然后进行 union 【n+1次分组】。CUBE是后面跟了n个字段,就将进行2的N次方的分组运算,然后进行。MySQL中使用with rollup/cube。

  11.条件:查询商品销售数量总数量为前三的商品。显示:商品编号,商品名称,销售数量。排序:按照销售数量进行排序。说明:如果商品销售数量的前4 名为 100,80,67,67 则4 条记录都显示:

SELECT g.goods_no
,g.goods_name
,t.total_quanty
FROM hand_goods g
,(SELECT sr.goods_no
,SUM(sr.sales_quanty) total_quanty
,rank() over(ORDER BY SUM(sr.sales_quanty) DESC)
rank_level
FROM hand_sales_records sr
WHERE 1 = 1
GROUP BY sr.goods_no) t
WHERE t.goods_no = g.goods_no
AND t.rank_level <= 3
ORDER BY t.total_quanty DESC

 

    小结:

      考察开窗函数以及rank()的使用(如题如果使用的分组的条件total_quanty,如果这个值相同,那么排名也相同)。

  12.a.创建一个表HAND_GOODS_XXXXX 包含HAND_GOODS 表里金额大于200 的商品记录。b.将HAND_GOODS_XXXXX 的单价更新为原价的80%。c.将 HAND_GOODS 表里的记录合并至HAND_GOODS_XXXXX,通过商品编码进行匹配,如果表HAND_GOODS_XXXXX 中不存在记录,进行新建,如果存在记录则更新HAND_GOODS_XXXXX 的商品单价为HAND_GOODS 中的商品单价:

--a.
CREATE TABLE HAND_GOODS_25305 AS SELECT * FROM hand_goods g WHERE
g.goods_price > 200
--b.
UPDATE HAND_GOODS_25305 SET goods_price = goods_price * 0.8
--c.
MERGE INTO hand_goods_25305 g_new
USING hand_goods g
ON (g.goods_no = g_new.goods_no)
WHEN MATCHED THEN
UPDATE
SET g_new.goods_price = g.goods_price
WHEN NOT MATCHED THEN
INSERT
VALUES
(g.goods_no
,g.goods_name
,g.goods_price);

 

    小结:

      考察创建,更新表,以及批量插入。

  13.条件:查询购买商品数量总计最多的顾客信息。显示:顾客编号,顾客名称,购买商品数量: 

WITH tab_sum AS
(SELECT sr.customers_no
,SUM(sr.sales_quanty) total_quanty
FROM hand_sales_records sr
WHERE 1 = 1
GROUP BY sr.customers_no)
SELECT t.customers_no, c.customers_name, t.total_quanty
FROM tab_sum t, hand_customers c
WHERE c.customers_no = t.customers_no AND t.total_quanty = (
SELECT MAX(total_quanty)
FROM tab_sum)

 

    小结:

      使用子查询问,及with方便在后面多次使用则可以简化SQL并适当提高性能 。

posted @ 2019-08-11 17:18  biu嘟  阅读(633)  评论(0编辑  收藏  举报