The intersection of MySQL and the difference set

Source: Internet
Author: User

A question seen in the forum is summarized here below:
CREATE TABLEconsume(
idVARCHAR (one) is not NULL,
tidVARCHAR (one) not NULL
)
Collate= ' Utf8_general_ci '
Engine=myisam
;
INSERT intoconsume(id,tidVALUES (' 1 ', ' 11 ');
INSERT intoconsume(id,tidVALUES (' 2 ', ' 14 ');
INSERT intoconsume(id,tidVALUES (' 3 ', ' 12 ');

CREATE TABLEteacher(
idVARCHAR (one) is not NULL,
tnameVARCHAR (one) is not NULL,
tdateDATETIME not NULL
)
Collate= ' Utf8_general_ci '
Engine=myisam
;
INSERT intoteacher(id,tname,tdateVALUES (' 10 ', ' Miss Li ', ' 2008-01-22 21:54:27 ');
INSERT intoteacher(id,tname,tdateVALUES (' 11 ', ' Teacher ', ' 2008-01-21 21:54:27 ');
INSERT intoteacher(id,tname,tdateVALUES (' 13 ', ' Teacher song ', ' 2008-01-28 21:54:27 ');
INSERT intoteacher(id,tname,tdateVALUES (' 14 ', ' Teacher Wei ', ' 2008-01-29 21:54:27 ');
INSERT intoteacher(id,tname,tdateVALUES (' 15 ', ' Teacher Kim ', ' 2008-01-30 21:54:27 ');
INSERT intoteacher(id,tname,tdateVALUES (' 16 ', ' Miss Zhao ', ' 2008-01-19 21:54:27 ');
INSERT intoteacher(id,tname,tdateVALUES (' 17 ', ' Teacher Zhang ', ' 2008-01-18 21:54:27 ');
INSERT intoteacher(id,tname,tdateVALUES (' 18 ', ' Teacher Yan ', ' 2008-01-17 21:54:27 ');
INSERT intoteacher(id,tname,tdateVALUES (' 12 ', ' Miss Gong ', ' 2008-01-25 21:54:27 ');
INSERT intoteacher(id,tname,tdateVALUES (' 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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.