数据库除运算SQL实现

已知A，B两表，B表是A表aid = 1 构成的子表。现通过B表查询A表中哪些aid同时全部包含了B表中所有的bid。（参考数据库除运算）
表A：

表B：

查询结果：

查询SQL代码：
SELECT DISTINCT A2.AID FROM A AS A2 WHERE A2.AID NOT IN(

SELECT B2.BAID FROM (

SELECT A.AID AAID,A.BID ABID,B1.AID BAID,B1.BID BBID FROM (SELECT A1.AID,B.BID FROM ((SELECT DISTINCT A.AID FROM A) AS A1 CROSS JOIN B) ) AS B1

LEFT JOIN A USING(AID,BID)

) AS B2 WHERE B2.AAID IS NULL AND B2.BAID IS NOT NULL )

附录：
表A：

-- ----------------------------

-- Table structure for `a`

-- ----------------------------

DROP TABLE IF EXISTS `a`;

CREATE TABLE `a` (

  `aid` int(11) NOT NULL,

  `bid` int(11) NOT NULL,

  PRIMARY KEY  (`aid`,`bid`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

-- ----------------------------

-- Records of a

-- ----------------------------

INSERT INTO `a` VALUES ('1', '1');

INSERT INTO `a` VALUES ('1', '2');

INSERT INTO `a` VALUES ('1', '3');

INSERT INTO `a` VALUES ('1', '4');

INSERT INTO `a` VALUES ('2', '1');

INSERT INTO `a` VALUES ('2', '2');

INSERT INTO `a` VALUES ('3', '1');

INSERT INTO `a` VALUES ('3', '2');

INSERT INTO `a` VALUES ('3', '3');

INSERT INTO `a` VALUES ('3', '4');

INSERT INTO `a` VALUES ('3', '5');

INSERT INTO `a` VALUES ('4', '1');

INSERT INTO `a` VALUES ('4', '2');

INSERT INTO `a` VALUES ('4', '3');

INSERT INTO `a` VALUES ('4', '4');

表B：

-- ----------------------------

-- Table structure for `b`

-- ----------------------------

DROP TABLE IF EXISTS `b`;

CREATE TABLE `b` (

  `AID` int(11) NOT NULL,

  `BID` int(11) NOT NULL,

  PRIMARY KEY  (`AID`,`BID`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

-- ----------------------------

-- Records of b

-- ----------------------------

INSERT INTO `b` VALUES ('1', '1');

INSERT INTO `b` VALUES ('1', '2');

INSERT INTO `b` VALUES ('1', '3');

INSERT INTO `b` VALUES ('1', '4');

转载于:https://www.cnblogs.com/Zjmainstay/archive/2012/03/08/MySQL_divideQuery.html