一个SQL语句面试题

Question

有三个表 
inf_subscriber 有三个字段  sub_id、sub_name、level   分别表示用户ID、用户姓名、用户级别
inf_product 有三个字段 prod_id、prod_name、fee 分别表示产品ID、产品名称、产品费用
rel_order 有两个字段 sub_id、prod_id  表示用户和产品的订购关系
要求用一个SQL语句实现这几个要求：
用户订购产品总费用大于100的，用户级别修改为A，用户订购产品总费用在100和50之间的，用户级别修改为B，用户订购产品总费用小于50的，用户级别修改为NULL

Answer 1

UPDATE inf_subscriber t1
   SET LEVEL =
          (SELECT CASE
                     WHEN SUM (fee) > 100
                        THEN 'A'
                     WHEN SUM (fee) < 50
                        THEN NULL
                     ELSE 'B'
                  END
             FROM inf_product t2, rel_order t3
            WHERE t2.sub_id = t1.sub_id AND t2.prod_id = t3.prod_id)

Answer 2

update inf_subscriber set  level = 
case 
when
(select sum(t2.fee) from inf_product t2,rel_order t3
where inf_subscriber.sub_id=t3.sub_id and t2.prod_id =t3.prod_id ) >100 then 'A'when
(select sum(t2.fee) from inf_product t2,rel_order t3
where inf_subscriber.sub_id=t3.sub_id and t2.prod_id =t3.prod_id ) between (50,100) then 'B'when
(select sum(t2.fee) from inf_product t2,rel_order t3
where inf_subscriber.sub_id=t3.sub_id and t2.prod_id =t3.prod_id ) < 50 then 'C'
end 

Answer 3

/* Formatted on 2011/03/11 08:57 (Formatter Plus v4.8.8) */
CREATE TABLE inf_product
(
  prod_id   VARCHAR2(8),
  prod_name  VARCHAR2(16),
  fee     VARCHAR2(8)
)LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING;CREATE TABLE rel_order
(
  prod_id   VARCHAR2(8),
  sub_id  VARCHAR2(8)
)LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING;CREATE TABLE inf_subscriber
(
  sub_id    VARCHAR2(8),
  sub_name  VARCHAR2(16),
  sub_level     VARCHAR2(8)
)LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING;INSERT INTO inf_subscriber
            (sub_id, sub_name, sub_level
            )
     VALUES ('1', 'xx', NULL
            );
INSERT INTO inf_subscriber
            (sub_id, sub_name, sub_level
            )
     VALUES ('2', 'yy', NULL
            );
INSERT INTO inf_subscriber
            (sub_id, sub_name, sub_level
            )
     VALUES ('3', 'zz', NULL
            );
COMMIT ;
INSERT INTO inf_product
            (prod_id, prod_name, fee
            )
     VALUES ('22', 'bb', '50'
            );
INSERT INTO inf_product
            (prod_id, prod_name, fee
            )
     VALUES ('11', 'aa', '10'
            );
INSERT INTO inf_product
            (prod_id, prod_name, fee
            )
     VALUES ('33', 'cc', '100'
            );
INSERT INTO inf_product
            (prod_id, prod_name, fee
            )
     VALUES ('44', 'dd', '80'
            );
COMMIT ;
INSERT INTO rel_order
            (prod_id, sub_id
            )
     VALUES ('33', '3'
            );
INSERT INTO rel_order
            (prod_id, sub_id
            )
     VALUES ('11', '1'
            );
INSERT INTO rel_order
            (prod_id, sub_id
            )
     VALUES ('22', '1'
            );
INSERT INTO rel_order
            (prod_id, sub_id
            )
     VALUES ('11', '2'
            );
INSERT INTO rel_order
            (prod_id, sub_id
            )
     VALUES ('11', '3'
            );
COMMIT ;UPDATE inf_subscriber t1
   SET sub_level =
          (SELECT CASE
                     WHEN SUM (fee) > 100
                        THEN 'A'
                     WHEN SUM (fee) < 50
                        THEN NULL
                     ELSE 'B'
                  END
             FROM inf_product t2, rel_order t3
            WHERE t3.sub_id = t1.sub_id AND t2.prod_id = t3.prod_id);
COMMIT ;

Answer 4

还有修改客户级别为 null 的，什么设计嘛，没有级别也给一个 Z 之类的让这个列 not null 有利于将来的性能优化。

Answer 5

哥哥,这是在面试呢,不是在真的做开发设计,这句话说出来的话,应该基本上就OVER了

Answer 6

莫非update时，都要全表更新的？ 
UPDATE INF_SUBSCRIBER T1
   SET SUB_LEVEL = (SELECT CASE
                             WHEN SUM(FEE) > 100 THEN
                              'A'
                             WHEN SUM(FEE) < 50 THEN
                              NULL
                             ELSE
                              'B'
                           END
                      FROM INF_PRODUCT T2, REL_ORDER T3
                     WHERE T3.SUB_ID = T1.SUB_ID
                       AND T2.PROD_ID = T3.PROD_ID)
 WHERE EXISTS (SELECT 1 FROM REL_ORDER T4 WHERE T4.SUB_ID = T1.SUB_ID);

Answer 7

面试更重要的是要展示出你在开发设计时的能力，如果你能提出更好的建议，那么对面试当然是有正面影响的。
不过如果要设置为not null，一定要考虑到正反两方面影响的。比如说补充Z，那么就会占用更多的存储空间，使得一个block包含更少的行，也会降低性能的。

Answer 8

UPDATE inf_subscriber 
SET LEVEL=(CASE WHEN B.feeSum>100 THEN 'A' WHEN B.feeSum<50 THEN Null ELSE 'B' END)
FROM inf_subscriber A
LEFT JOIN (SELECT DA.Sub_id, Sum(fee) AS feeSum FROM rel_order DA LEFT JOIN inf_product DB ON DA.Prod_id=DB.prod_id GROUP BY DA.Sub_id ) B ON A.Sub_Id=B.Sub_id

Answer 9

面试官说可以调用自定义函数来实现，函数根据值的大小返回A、B或者NULL
哎。。 本人数据库知识还处于起步阶段，函数只听过 都没写过的。。  这题要大家来做会是怎样写呢？

Answer 10

如果非要用自定义函数写可以写两个自定义函数
第一个根据sub_id取得总费用
CREATE OR REPLACE FUNCTION fun_getFee(p_sub_id IN VARCHAR2) RETURN VARCHAR2 IS
 RESULT VARCHAR2(8);
 BEGIN
   SELECT SUM(fee) INTO RESULT
          FROM inf_product a ,rel_order b
          WHERE a.prod_id=b.prod_id AND b.sub_id=p_sub_id;
   RETURN RESULT;
 END;第二个根据sub_id取得等级
CREATE OR REPLACE FUNCTION fun_getCurrentLevel(p_sub_id IN VARCHAR2) RETURN VARCHAR2 IS
        RESULT VARCHAR2(8);
        afee INT;
 BEGIN
   afee:=fun_getfee(p_sub_id);
   IF (afee >100) THEN 
      RESULT:='A' ;
   ELSIF (afee >50 AND afee <=100) THEN 
      RESULT:='B' ;
   ELSE
      RESULT:=NULL;
   END IF;
   RETURN RESULT;
END
;最后,可以
UPDATE inf_subscriber SET sub_level=fun_getcurrentlevel(sub_id);