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

结果：

欢迎指正

未完待续。。。。

ETL工程师笔试题

原文：https://www.cnblogs.com/XiaoyangBoke/p/11232137.html