有三个表
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
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
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)
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
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 ;
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);
不过如果要设置为not null,一定要考虑到正反两方面影响的。比如说补充Z,那么就会占用更多的存储空间,使得一个block包含更少的行,也会降低性能的。
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
哎。。 本人数据库知识还处于起步阶段,函数只听过 都没写过的。。 这题要大家来做会是怎样写呢?
第一个根据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);