ETL工程师笔试题

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();

结果：

2、参考答案

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

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;

欢迎指正

未完待续。。。。