ETL工程师笔试题

ETL工程师笔试题-LMLPHP

1、参考答案

1）建表

CREATE TABLE `ta` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`cx` varchar(20) DEFAULT NULL,
`qy` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

CREATE TABLE `tb` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`cx` varchar(20) DEFAULT NULL,
`qy` varchar(20) DEFAULT NULL,
`jg` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

CREATE TABLE `tc` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`cx` varchar(20) DEFAULT NULL,
`qy` varchar(20) DEFAULT NULL,
`jg` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8;

存储过程：

DROP PROCEDURE IF EXISTS `query_a_and_b`;
DELIMITER ;;
CREATE PROCEDURE query_a_and_b() READS SQL DATA
BEGIN
   DECLARE cxc varchar(20);
   DECLARE qyc varchar(20);
   DECLARE jgc INT;
   DECLARE s INT DEFAULT 0 ;
   DECLARE consume CURSOR FOR SELECT cx,qy,jg FROM tb;
-- DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET num = 1;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=1;
   OPEN consume;
       FETCH consume into cxc,qyc,jgc;
       while s <> 1 DO
           if(qyc='全国')THEN
INSERT INTO tc(cx,qy,jg)
SELECT a.cx,b.qy,a.jg from tb a left JOIN ta b on b.cx=a.cx WHERE b.cx=cxc;
ELSEif(qyc='其他')THEN
INSERT INTO tc(cx,qy,jg)
SELECT a.cx,b.qy,a.jg from tb a left JOIN ta b on b.cx=a.cx WHERE a.qy=qyc and b.qy not in (
select t.qy from tb t WHERE t.cx=b.cx
);
ELSE
INSERT INTO tc(cx,qy,jg)
SELECT a.cx,a.qy,a.jg from tb a WHERE a.cx=cxc and a.qy=qyc;
END IF;
           FETCH consume into cxc,qyc,jgc;
       END WHILE;
   CLOSE consume;
END;;
DELIMITER;

CALL query_a_and_b();

结果：

ETL工程师笔试题-LMLPHP

2、参考答案

借用1题表tb，数据如下：

ETL工程师笔试题-LMLPHP

sql如下：

第一种：

CREATE VIEW view_name AS
SELECT a.COHEV,b.REIZ,a.jg from 
(select
(case when cx = 'COHEV' then qy end) as COHEV,
jg
from tb) a JOIN  
(select
(case when cx = 'REIZ' then qy end) as REIZ,
jg
from tb) b  on a.jg = b.jg
WHERE a.COHEV is not null AND b.REIZ is not null
第二种：
select max(COHEV) COHEV,max(REIZ) REIZ,jg from
(select
(case when cx = 'COHEV' then qy end) as COHEV,
(case when cx = 'REIZ' then qy end) as REIZ,
jg
from tb)b GROUP BY jg;

欢迎指正

未完待续。。。。