A seemingly simple SQL has baffled a lot of people

Source: Internet
Author: User

A choice of curriculum, with student ID, course ID, teacher ID, asked to choose both Chinese and math students

Use [tempschool]go/****** object:  Table [dbo].[ SELECTC]    Script Date: 07/02/2015 11:04:55 ******/set ansi_nulls ongoset quoted_identifier ongocreate TABLE [dbo].[ SELECTC] ([ID] [int] IDENTITY (NULL,[SID) "[int] null,[cid] [int] null,[tid] [int] NULL, CONSTRAINT [PK_SELECTC] PRI MARY KEY CLUSTERED ([ID] ASC) with (Ignore_dup_key = OFF) on [PRIMARY]) on [PRIMARY]

Insert some simulation data, assuming that cid=1 and cid=2 are both Chinese and maths, then according to the language and mathematics, both the number 1th and the 5th students should be selected.

Don't look at the answer, write a SQL in your head, and then look down.

Got a lot of wrong answers at first, and the easiest answer.

--The wrong answer, none of the records. SELECT * from SELECTC where cid=1 and cid=2--the wrong answer, a student of only one language will appear in the record select * from SELECTC where cid=1 or cid=2

  

So the right answer should be how to do it, at present I found 3 ways,

Method One:

Select A.sid from SELECTC as a left join SELECTC as B on b.sid = A.sidwhere a.cid =1 and B.cid =2

  

By the same time, the Cartesian product, the Infinite Association can elect the language mathematics English and so on.

Method Two:

                SELECT sid from                SELECTC                WHERE  (cid = 1) OR                        (cid = 2) The                GROUP by Sid has                (COUNT (*) >= 2)    

This is the second, first through the conditional or select all the language or mathematics, and then according to the SID summary, as long as the count of more than 2 is the same time selected language and mathematics, if you want to expand more is to modify the number of count and or conditions

Method Three:

SELECT distinct  sid from SELECTC where SID in (select SID from SELECTC WHERE cid = 1) and SID in (select SID from Selectc WHERE cid = 2)

The third method seems to be less efficient and needs to be queried in.

I don't know if we have any other written wording.

A seemingly simple SQL has baffled a lot of people

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.