Help leaders to help me solve the SQL-CodePudding

This is to write SQL statements, regardless of the value to 10, 30 the s.s id, s.u name is 1 and the data from ha ha this how to write

For bosses to help me to solve!

CodePudding user response:

Where a and after s.s id=1 ~ ~ ~

CodePudding user response:

 
The create table # course 
(
Cid int, 
Cname char (10) 
) 
Insert into # course 
Select 10, 'Chinese' union all 
Select 20, 'mathematics' union all 
The select 30, 'English' 


The create table # student 
(
Sid int, 
Uname char (10) 
) 


Insert into # student 
Select 1, 'ha ha' union all 
Select 2, 'hey' union all 
Select 3, 'ha ha' 


The create table # sc 
(
Sid int, 
Cid int, 
Score int 
) 

Insert into # sc 
Select 1,10,88 union all 
Select 1,20,89 union all 
Select 1,30,90 union all 
Select 2,20,100 union all 
Select 2,30,89 union all 
Select 2,10,99 union all 
Select 3,20,100 union all 
Select 3,30,100 union all 
Select 3,10,67 


Select s. *,, dc name, a.s core, da vg_score, d.m ax_score # from student s left join a on s.s id=a.s id # sc 
Left the join 
(
The select a.c id, Arthur c. name, avg (a.s core) as avg_score, Max (a.s core) as max_score from a left join # course c # sc on a.c id=Arthur c. id 
Group by a.c id, Arthur c. name 
) d on a.c id=, dc id 
Where s.s id=1 

/* 
Sid uname cname score avg_score max_score 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
1 ha ha Chinese, 88, 84, 99 
1 ha ha mathematics, 89, 96, 100 
1, 90, 93, 100 ha ha English 

(3 rows affected) 

*/

CodePudding user response:

 
Select the sid=1, uname='ha ha' the from student

CodePudding user response:

 
If object_id (' tempdb for.. # # course ') is not null drop table course 
The create table # course 
(cid int, cname nvarchar (50)) 
Insert into # course 
Select 10, 'Chinese' union all 
Select 20, 'mathematics' union all 
The select 30, 'English' 

If object_id (' tempdb for.. # # student ') is not null drop table student 
The create table # student 
(sid int, uname nvarchar (50)) 

Insert into # student 
Select 1, 'ha ha' union all 
Select 2, 'hey' union all 
Select 3, 'ha ha' 

If object_id (' tempdb for.. # # ') sc is not null drop table sc 
The create table # sc 
(sid int, cid int, score int) 

Insert into # sc 
Select 1,10,88 union all 
Select 1,20,89 union all 
Select 1,30,90 union all 
Select 2,10,99 union all 
Select 2,20,100 union all 
Select 2,30,89 union all 
Select 3,10,67 union all 
Select 3,20,100 union all 
Select 3,30,100 


, for each of the query subjects, the highest, average score, the result of the best students 
Select t. *, 
Stuff (select distinct ', '+ b.u name from # sc a inner join # student b on a.s id=b.s id where t. high score=a.s core and tc id=a.c id for XML path ('')), 1, 1, ' ') as the student name 
The from (
The select Arthur c. id, Arthur c. name, Max (score) as the highest, avg (score) as average 
The from # sc s 
Left the join # course c on s.c id=Arthur c. id 
Group by Arthur c. id, Arthur c. name 
) t