A seemingly simple SQL has baffled a lot of people

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