A question seen in the forum is summarized here below:
CREATE TABLEconsume
(
id
VARCHAR (one) is not NULL,
tid
VARCHAR (one) not NULL
)
Collate= ' Utf8_general_ci '
Engine=myisam
;
INSERT intoconsume
(id
,tid
VALUES (' 1 ', ' 11 ');
INSERT intoconsume
(id
,tid
VALUES (' 2 ', ' 14 ');
INSERT intoconsume
(id
,tid
VALUES (' 3 ', ' 12 ');
CREATE TABLEteacher
(
id
VARCHAR (one) is not NULL,
tname
VARCHAR (one) is not NULL,
tdate
DATETIME not NULL
)
Collate= ' Utf8_general_ci '
Engine=myisam
;
INSERT intoteacher
(id
,tname
,tdate
VALUES (' 10 ', ' Miss Li ', ' 2008-01-22 21:54:27 ');
INSERT intoteacher
(id
,tname
,tdate
VALUES (' 11 ', ' Teacher ', ' 2008-01-21 21:54:27 ');
INSERT intoteacher
(id
,tname
,tdate
VALUES (' 13 ', ' Teacher song ', ' 2008-01-28 21:54:27 ');
INSERT intoteacher
(id
,tname
,tdate
VALUES (' 14 ', ' Teacher Wei ', ' 2008-01-29 21:54:27 ');
INSERT intoteacher
(id
,tname
,tdate
VALUES (' 15 ', ' Teacher Kim ', ' 2008-01-30 21:54:27 ');
INSERT intoteacher
(id
,tname
,tdate
VALUES (' 16 ', ' Miss Zhao ', ' 2008-01-19 21:54:27 ');
INSERT intoteacher
(id
,tname
,tdate
VALUES (' 17 ', ' Teacher Zhang ', ' 2008-01-18 21:54:27 ');
INSERT intoteacher
(id
,tname
,tdate
VALUES (' 18 ', ' Teacher Yan ', ' 2008-01-17 21:54:27 ');
INSERT intoteacher
(id
,tname
,tdate
VALUES (' 12 ', ' Miss Gong ', ' 2008-01-25 21:54:27 ');
INSERT intoteacher
(id
,tname
,tdate
VALUES (' 19 ', ' Teacher Liu ', ' 2008-01-17 21:34:27 ');
Please
If the ID in teacher is in the TID in consume, it's in front, not in the back row.
For the row in front and follow the ID in consume ascending
For the rows that follow, sort by the descending order of tdate in teacher
For this actually split out:
1, the ID in the teacher in the consume in the TID in accordance with the ID in the consume ascending row
Select t.* from teacher T joins consume C on c.tid= t.id ORDER by c.id ASC
2. The ID in teacher is not sorted by tdate in teacher in the TID in consume
Select t.* from teacher T left joins consume C on c.tid= t.id where c.id are NULL order by tdate Desc
and connect two sheets.
SELECT * FROM (select t.* to teacher T join consume C on c.tid= t.id ORDER by c.id ASC) b
Union
SELECT * FROM (select t.* to teacher T left joins consume C on c.tid= t.id where c.id are NULL order by tdate Desc) A
650) this.width=650; "src=" http://img.blog.csdn.net/20150826231019004 "alt=" here write a picture describing "title=" "style=" border:none; "/ >
Here's a description of the method under 2.
650) this.width=650; "src=" http://img.blog.csdn.net/20150826230821063 "alt=" The first picture "title=" "style=" border:none; "/ >650) this.width=650; "src=" http://img.blog.csdn.net/20150826230935879 "alt=" the second picture "title=" "style=" Border:none; " />
Like these two pictures, from the data look more like the first picture
First the left join can find all the data for the teacher table (SELECT * FROM teacher T left join consume C on c.tid= t.id ORDER BY tdate Desc),
650) this.width=650; "src=" http://img.blog.csdn.net/20150826231536364 "alt=" here write a picture describing "title=" "style=" border:none; "/ >
Here you can see that all we need is the ID no data part, so add a condition c.id is null after the left join to get this difference set
Similar to the first picture
So now how to get the second picture, here is actually the same, because the left join can get the data is the teacher table of all the data
650) this.width=650; "src=" http://img.blog.csdn.net/20150826232225113 "alt=" The third picture "title=" "style=" border:none; "/ >
So I can get rid of that part of the intersection (C.id is not NULL), so add a condition c.id is null after the left join, like the second picture
The above is my understanding of the intersection and the difference set.
Reference: http://blog.itpub.net/29510932/viewspace-1777673/
http://blog.csdn.net/youngqj/article/details/7225400
The intersection of MySQL and the difference set