bepsun面试题

#mysql表
ses
/*
Navicat MySQL Data Transfer

Source Server : localhost
Source Server Type : MySQL
Source Server Version : 80013
Source Host : localhost:3306
Source Schema : sys

Target Server Type : MySQL
Target Server Version : 80013
File Encoding : 65001

Date: 22/11/2018 14:14:01
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

---

– Table structure for ses

---

DROP TABLE IF EXISTS ses;
CREATE TABLE ses (
idses int(11) NOT NULL,
pid varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
aid varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
PRIMARY KEY (idses) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

---

– Records of ses

---

INSERT INTO ses VALUES (1, ‘11’, ‘1’);
INSERT INTO ses VALUES (2, ‘22’, ‘2’);

SET FOREIGN_KEY_CHECKS = 1;

a

/*
Navicat MySQL Data Transfer

Source Server : localhost
Source Server Type : MySQL
Source Server Version : 80013
Source Host : localhost:3306
Source Schema : sys

Target Server Type : MySQL
Target Server Version : 80013
File Encoding : 65001

Date: 22/11/2018 14:15:07
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

---

– Table structure for a

---

DROP TABLE IF EXISTS a;
CREATE TABLE a (
aid int(11) NULL DEFAULT NULL,
aname varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
alan varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

---

– Records of a

---

INSERT INTO a VALUES (1, ‘1ANAME’, ‘a’);
INSERT INTO a VALUES (2, ‘2ANAME’, ‘b’);
INSERT INTO a VALUES (1, ‘1ANAME’, ‘d’);
INSERT INTO a VALUES (3, ‘3ANAME’, ‘F’);
INSERT INTO a VALUES (1, ‘asf’, ‘c’);

SET FOREIGN_KEY_CHECKS = 1;

p

/*
Navicat MySQL Data Transfer

Source Server : localhost
Source Server Type : MySQL
Source Server Version : 80013
Source Host : localhost:3306
Source Schema : sys

Target Server Type : MySQL
Target Server Version : 80013
File Encoding : 65001

Date: 22/11/2018 14:16:35
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

---

– Table structure for p

---

DROP TABLE IF EXISTS p;
CREATE TABLE p (
pid int(11) NULL DEFAULT NULL,
pname varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
plan varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

---

– Records of p

---

INSERT INTO p VALUES (11, ‘11PNAME’, ‘a’);
INSERT INTO p VALUES (22, ‘22PNAME’, ‘b’);
INSERT INTO p VALUES (11, ‘11CPNAME’, ‘c’);
INSERT INTO p VALUES (44, ‘44PNAME’, ‘e’);

SET FOREIGN_KEY_CHECKS = 1;

sql

SELECT DISTINCT
IFNULL( G.AID, ‘-1’ ),
IFNULL( G.PID, ‘-1’ ),
IFNULL( G.LAN, ‘-1’ ),
IFNULL( P.PNAME, ‘-1’ ),
IFNULL( A.ANAME, ‘-1’ )
FROM
(
SELECT
AID,
PID,
LAN
FROM
(
( SELECT AID AID, P.PID PID, PLAN LAN FROM SES RIGHT JOIN P ON SES.PID = P.PID ) E
LEFT JOIN (
SELECT
A.AID AID,
SES.PID PID,
A.ALAN LAN
FROM
SES
RIGHT JOIN A ON A.AID = SES.AID
) F USING ( PID, AID, LAN )
) UNION
SELECT
AID,
PID,
LAN
FROM
(
( SELECT AID AID, P.PID PID, PLAN LAN FROM SES RIGHT JOIN P ON SES.PID = P.PID ) E
RIGHT JOIN (
SELECT
A.AID AID,
SES.PID PID,
A.ALAN LAN
FROM
SES
RIGHT JOIN A ON A.AID = SES.AID
) F USING ( PID, AID, LAN )
)
) G
LEFT JOIN P ON G.PID = P.PID
AND G.LAN = P.PLAN
LEFT JOIN A ON G.AID = A.AID
AND G.LAN = A.ALAN