一个SQL语句面试题

[onull]

有三个表
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

[oracle_dba_11]

[Quote=引用 7 楼 yanran_hill 的回复:]
引用 6 楼 humanity 的回复:
还有修改客户级别为 null 的，什么设计嘛，没有级别也给一个 Z 之类的让这个列 not null 有利于将来的性能优化。

哥哥,这是在面试呢,不是在真的做开发设计,这句话说出来的话,应该基本上就OVER了
[/Quote]
那不一定，你说出来说不定面试的对你影响更好呢

[Mark_MaoHUA]

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)

[clisav]

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

[dg1136]

sql都快忘光了，赶紧恶补

[Mark_MaoHUA]

/* 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 ;

[Mark_MaoHUA]

3楼的执行效率太低

[humanity]

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

[yanran_hill]

[Quote=引用 6 楼 humanity 的回复:]
还有修改客户级别为 null 的，什么设计嘛，没有级别也给一个 Z 之类的让这个列 not null 有利于将来的性能优化。
[/Quote]
哥哥,这是在面试呢,不是在真的做开发设计,这句话说出来的话,应该基本上就OVER了

[zengjc]

莫非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);

[zengjc]

[Quote=引用 7 楼 yanran_hill 的回复:]
引用 6 楼 humanity 的回复:
还有修改客户级别为 null 的，什么设计嘛，没有级别也给一个 Z 之类的让这个列 not null 有利于将来的性能优化。

哥哥,这是在面试呢,不是在真的做开发设计,这句话说出来的话,应该基本上就OVER了
[/Quote]
面试更重要的是要展示出你在开发设计时的能力，如果你能提出更好的建议，那么对面试当然是有正面影响的。
不过如果要设置为not null，一定要考虑到正反两方面影响的。比如说补充Z，那么就会占用更多的存储空间，使得一个block包含更少的行，也会降低性能的。

[ljf33_2000]

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

[onull]

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

[asggadfgadfg]

帮顶，等待更牛逼的方法出现

[li1229363]

4楼的同学已经把完整的建表、插入数据、更新语句都罗列出来了，楼上你花想干啥？

[archwuke1]

如果非要用自定义函数写

可以写两个自定义函数
第一个根据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);