Query only take the no. 1 and 3 course students student id-CodePudding

This is the table sc

Tried several ways but think impassability is one of the following code, is there a statistical cno why for 2 conditions limit to the number of the last student number, there will be 95001 95001 Ming chose 3 class
The use of School
Select Sno
The from SC
Where Cno in (' 1 ', '3')
Group by Sno
Having a COUNT (Cno)=2

This is the results

CodePudding user response:

 
Select Sno 
The from SC a 
Where Cno in (' 1 ', '3') 
And not the exists (select 1 from SC b where b.S no=a.S no and biggest no not in (' 1 ', '3')) 
Group by Sno 
Having a count (1)=2

CodePudding user response:

The Create table # SC
(
Sno varchar (255),
Cno varchar (255),
Grade a decimal (4, 1)
)
Insert into # SC
Select '95001', '1', 92,
Union all
Select '95001', '2', 85,
Union all
Select '95001', '3', 88,
Union all
Select '95002', '1', 90,
Union all
Select '95003', '1', 82,
Union all
Select '95003', '3', 85,
Select Sno from # SC where the exists (select 1 # from SC a where clause a.S no=# SC. Sno and a.C no='3')
And not the exists (select 1 # from SC b where b.S no=# SC. Sno and biggest no not in (' 1 ', '3'))
And Cno='1'

Drop table # SC

CodePudding user response:

You in where, the first step is to filter the 1, 3 course record, group by, again, of course, 95001 count (*) has a value of 2, and meet the conditions,

Have a try like this:

Select sno
The from # SC
Group by sno
Having the sum (case when cno in (1, 3) then one else 2 end)=2

CodePudding user response:

Finally there will be 95001 student id, 95001 chose 3 class, obviously because Cno in (' 1 ', '3') the records of 2 filtered out, not have 2 classes left

CodePudding user response:

 
 Create table # SC(
Sno varchar (255), 
Cno varchar (255), 
Grade a decimal (4, 1) 
) 
Insert into # SC 
Select '95001', '1', 92, 
Union all 
Select '95001', '2', 85, 
Union all 
Select '95001', '3', 88, 
Union all 
Select '95002', '1', 90, 
Union all 
Select '95003', '1', 82, 
Union all 
Select '95003', '3', 85, 

Select Sno 
The from # SC a 
Where the exists (select Sno, count (cno) from # SC b where a.S no=b.s no group by Sno having count (cno)=2) 
Group by Sno

CodePudding user response:

The meaning of having a COUNT (Cno)=2, is repeated the number of occurrences of Cno for 2; Not the same number of courses completed,
Read figure known 1 3 times, 2 in 1, 3 2 times,
The action object for
-- -- -- -- --
95001 3 88
95003 3 85
-- -- -- -- --
How to get the number for the 2 courses, the other floor provides a lot of thinking,