mysql的交集與差集

mysql的交集與差集

在論壇看到的一個問題這裡總結下：
 

CREATE TABLE consume (
id VARCHAR(11) NOT NULL,
tid VARCHAR(11) NOT NULL
)
COLLATE=’utf8_general_ci’
ENGINE=MyISAM
;
INSERT INTO consume (id, tid) VALUES (‘1’, ‘11’);
INSERT INTO consume (id, tid) VALUES (‘2’, ‘14’);
INSERT INTO consume (id, tid) VALUES (‘3’, ‘12’);

CREATE TABLE teacher (
id VARCHAR(11) NOT NULL,
tname VARCHAR(11) NOT NULL,
tdate DATETIME NOT NULL
)
COLLATE=’utf8_general_ci’
ENGINE=MyISAM
;
INSERT INTO teacher (id, tname, tdate) VALUES (‘10’, ‘李老師’, ‘2008-01-22 21:54:27’);
INSERT INTO teacher (id, tname, tdate) VALUES (‘11’, ‘支老師’, ‘2008-01-21 21:54:27’);
INSERT INTO teacher (id, tname, tdate) VALUES (‘13’, ‘宋老師’, ‘2008-01-28 21:54:27’);
INSERT INTO teacher (id, tname, tdate) VALUES (‘14’, ‘魏老師’, ‘2008-01-29 21:54:27’);
INSERT INTO teacher (id, tname, tdate) VALUES (‘15’, ‘金老師 ‘, ‘2008-01-30 21:54:27’);
INSERT INTO teacher (id, tname, tdate) VALUES (‘16’, ‘趙老師’, ‘2008-01-19 21:54:27’);
INSERT INTO teacher (id, tname, tdate) VALUES (‘17’, ‘張老師’, ‘2008-01-18 21:54:27’);
INSERT INTO teacher (id, tname, tdate) VALUES (‘18’, ‘嚴老師’, ‘2008-01-17 21:54:27’);
INSERT INTO teacher (id, tname, tdate) VALUES (‘12’, ‘龔老師’, ‘2008-01-25 21:54:27’);
INSERT INTO teacher (id, tname, tdate) VALUES (‘19’, ‘劉老師 ‘, ‘2008-01-17 21:34:27’);

求
如果teacher中的id在consume中的tid中有，就排在前面，沒有就排在後面
對於排在前面的又按照consume中的id升序排
對於排在後面的，按teacher中的tdate的降序排序

對於這個其實分拆出來：
1、teacher中的id在consume中的tid中有按照consume中的id升序排
select t.* from teacher t join consume c on c.tid= t.id order by c.id asc
2、teacher中的id在consume中的tid中沒有按teacher中的tdate的降序排序
select t.* from teacher t left join consume c on c.tid= t.id where c.id is NULL order by tdate desc
然後將兩張表連起來
select * from (select t.* from teacher t join consume c on c.tid= t.id order by c.id asc)b
union
select * from (select t.* from teacher t left join consume c on c.tid= t.id where c.id is NULL order by tdate desc)a

這裡說明下2中這個求法

就像這兩幅圖，從數據看更像第一張圖
首先left join 可以找到所有的teacher表的數據（select * from teacher t left join consume c on c.tid= t.id order by tdate desc），如圖
vc7Sw8fWu9Do0qq1xMrHaWTDu9PQyv2+3bXEsr+31qOsy/nS1NTabGVmdCBqb2luILrzvNO49sz1vP5jLmlkIGlzIE5VTEy8tL/JtcO1vdXiuPay7ryvPGJyIC8+DQrA4MvGtdrSu9XFzbw8YnIgLz4NCsTHz9bU2tT10fm1w7W9tdq2/tXFzbyjrNXiwO/G5Mq1ysfSu9H5tcSjrNLyzqpsZWZ0IGpvaW7E3Lm7tcO1vbXEyv2+3crHdGVhY2hlcrHttcTIq7K/yv2+3cjnzbw8YnIgLz4NCjxpbWcgYWx0PQ=="第三張圖" src="http://www.bkjia.com/uploads/allimg/150830/0600405F7-4.png" title="" />
所以我在剔除掉交集的那部分即可(c.id is not NULL),所以還是在left join 後加個條件c.id is NULL即可得到這個差集類似第二張圖
以上是我的交集與差集的理解。