mysql中表之间能否进行除运算_数据库除运算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');