ETL工程师笔试题

1、参考答案

1）建表

CREATE TABLE `ta`
  `id` int11) NOT NULL AUTO_INCREMENT,
  `cx` varchar20) DEFAULT NULL,
  `qy` varchar20) DEFAULT NULL,
  PRIMARY KEY `id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

CREATE TABLE `tb`
  `id` int11) NOT NULL AUTO_INCREMENT,
  `cx` varchar20) DEFAULT NULL,
  `qy` varchar20) DEFAULT NULL,
  `jg` int11) DEFAULT NULL,
  PRIMARY KEY `id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

CREATE TABLE `tc`
  `id` int11) NOT NULL AUTO_INCREMENT,
  `cx` varchar20) DEFAULT NULL,
  `qy` varchar20) DEFAULT NULL,
  `jg` int11) 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 varchar20);
    DECLARE qyc varchar20);
    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
            ifqyc=’全国’)THEN
INSERT INTO tccx,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;
 ELSEifqyc=’其他’)THEN
INSERT INTO tccx,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 tccx,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 maxCOHEV) COHEV,maxREIZ) 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;

欢迎指正

未完待续。。。。