Hello everyone
I have a table named studentMark student orders.
Its structure is:
Auto-increment ID, student ID, subject ID, score, exam time, Exam ID
ID (int), StudentID (int), Kind (int), Mark (float), TestTime (DateTime), TestKindID (int)
1, 20, 1, 90,200, 1
2, 20, 2, 80,200, 1
3, 20, 3, 70,200, 1
4, 21, 1, 60,200, 1
5, 21, 2, 70,200, 1
6, 21, 3, 90,200, 1
7, 23, 1, 50,200, 1
8, 23, 2, 40,200, 1
9, 23, 3, 20,200, 1
10, 20, 1, 90,200, 2
11, 20, 2, 80,200, 2
12, 20, 3, 70,200, 2
13, 21, 1, 60,200, 2
14, 21, 2, 70,200, 2
14, 21, 3, 90,200, 2
15, 23, 1, 50,200, 2
16, 23, 2, 40,200, 2
17, 23, 3, 20,200, 2
I want to use an SQL statement to get this result (that is, to display the scores of all subjects in an exam in the same row as the subject name, but the number of subjects is not certain, there may be N.) How can this problem be solved?
Student ID, subject, score, Exam ID
20, 1, 90, 2, 80, 2, 70, 1
21, 1, 60, 2, 70, 3, 90, 1
21, 1, 50, 2, 40, 3, 20, 1
20, 1, 90, 2, 80, 3, 70, 2
21, 1, 60, 2, 70, 3, 90, 2
21, 1, 50, 2, 40, 3, 20, 2
If there are many subjects, they will be added as follows.
21, 1, 50, 2, 40, 3, 13, 4, 55, 5, 66, 6, 20 ....... 2. Question points: 100. replies: 16
On the first floor, huobr (two teeth) replied to 13:56:30, and scored 5 points. The dynamic table should be used,
Reference: http://blog.csdn.net/fuxc/archive/2002/03/04/14528.aspx
Top
Zjcxc (zhujian) on the second floor replied to the score 70 at 14:22:34, -- using dynamic SQL statements
Declare @ s nvarchar (4000)
Set @ s =''
Select @ s = @ s + ', subject =' + quotename (Kind ,'''')
+ ', Score = max (case Kind when' + quotename (Kind ,'''')
+ 'Then Mark end )'
From studentMark
Group by Kind
Exec ('select studentid' + @ s + ', TestKindID from studentMark group by StudentID, TestKindID') Top
Zjcxc (zhujian) on the third floor replied to 14:26:30, with a score of 0-test
-- Test Data
Create table studentMark (ID int, StudentID int, Kind int, Mark float, TestTime DateTime, TestKindID int)
Insert studentMark select 1, 2004, 90, '2017-', 1
Union all select 2, 2004, 80, '1970-', 1
Union all select 3, 2004, 70, '1970-', 1
Union all select 4, 22004, 60, '1970-', 1
Union all select 5, 22004, 70, '1970-', 1
Union all select 6, 2004, 90, '1970-', 1
Union all select 7, 2004, 50, '1970-', 1
Union all select 8, 2004, 40, '1970-', 1
Union all select 9, 2004, 20, '1970-', 1
Union all select 10, 2004, 90, '2017-02-26 ', 2
Union all select 11, 2004, 80, '2017-02-26 ', 2
Union all select 12, 20, 3, 70, '2017-02-26 ', 2
Union all select 13, 22004, 60, '2017-02-26 ', 2
Union all select 14, 22004, 70, '2017-02-26 ', 2
Union all select 14, 2004, 90, '2017-02-26 ', 2
Union all select 15, 2004, 50, '1970-02-26 ', 2
Union all select 16, 2004, 40, '2014-02-26 ', 2
Union all select 17, 2004, 20, '1970-02-26 ', 2
Go
-- Query
Declare @ s nvarchar (4000)
Set @ s =''
Select @ s = @ s + ', subject =' + quotename (Kind ,'''')
+ ', Score = max (case Kind when' + quotename (Kind ,'''')
+ 'Then Mark end )'
From studentMark
Group by Kind
Exec ('select studentid' + @ s + ', TestKindID
From studentMark
Group by StudentID, TestKindID
Order by StudentID, TestKindID ')
Go
-- Delete test
Drop table studentMark
/* -- Result
StudentID subject score TestKindID
-------------------------------------------------------------
20 1 90.0 2 80.0 3 70.0 1
20 1 90.0 2 80.0 3 70.0 2
21 1 60.0 2 70.0 3 90.0 1
21 1 60.0 2 70.0 3 90.0 2
23 1 50.0 2 40.0 3 20.0 1
23 1 50.0 2 40.0 3 20.0 2
-- */Top
Xluzhong (Ralph) on the 4th floor replied to 15:22:23 score 20 create table studentmark (ID int, StudentID int, Kind int, Mark float, TestTime DateTime, TestKindID int)
Insert into studentmark
Select 1, 20, 2004, '1970-', 1 union all
Select 2, 20, 2004, '1970-', 1 union all
Select 3, 20, 2004, '1970-', 1 union all
Select 4, 21, 2004, '1970-', 1 union all
Select 5, 21, 2004, '1970-', 1 union all
Select 6, 21, 2004, '1970-', 1 union all
Select 7, 23, 2004, '1970-', 1 union all
Select 8, 23, 2004, '1970-', 1 union all
Select 9, 23, 2004, '1970-', 1 union all
Select 10, 20, 2004, '1970-02-26 ', 2 union all
Select 11, 20, 2004, '2017-02-26 ', 2 union all
Select 12, 20, 2004, '2017-02-26 ', 2 union all
Select 13, 21, 2004, '1970-02-26 ', 2 union all
Select 14, 21, 2004, '2017-02-26 ', 2 union all
Select 15, 21, 2004, '1970-02-26 ', 2 union all
Select 16, 23, 2004, '2017-02-26 ', 2 union all
Select 17, 23, 2004, '2017-02-26 ', 2 union all
Select 18, 23, 2004, '2017-02-26 ', 2
DECLARE @ SQL VARCHAR (8000)
SET @ SQL = 'select distinct studentid'
SELECT @ SQL = @ SQL + ', ''' + cast (kind as nvarchar (10) + ''' as [' + cast (kind as nvarchar (10 )) + '], (select mark from studentmark where kind = ''' + cast (kind as nvarchar (10) + ''' and TestKindID =. testKindID and StudentID =. studentID) as mark' + cast (kind as nvarchar (10 ))
From
(Select distinct kind from studentmark) B
Set @ SQL = @ SQL + ', TestKindID from studentmark'
Print @ SQL
Exec (@ SQL)
Drop table studentmark
Top
Chenqianlong (443) on the fifth floor replied to the score of 0 ding Top at 16:22:28
On the 6th floor, softj (guest of Heaven and Earth <Confused recently>) replied with a score of 5 from 16:39:41-using dynamic SQL statements
Declare @ s nvarchar (4000)
Set @ s =''
Select @ s = @ s + ', subject =' + quotename (Kind ,'''')
+ ', Score = max (case Kind when' + quotename (Kind ,'''')
+ 'Then Mark end )'
From studentMark
Group by Kind
Exec ('select studentid' + @ s + ', TestKindID from studentMark group by StudentID, testkindid ')
Top
FlyNow on the 7th floor (for those in China, I am a public Clerk) back to 17:58:30 score 0 how can I put the selected result in a temporary table T? Top
On the eighth floor, FlyNow (the Chinese mainland, who dares to take care of me, I am a public Clerk) replied to 18:06:53, score 0, and put the results in the temporary table, what should we do? Top
Zjcxc (zhujian) on the 9th floor replied to 18:13:23, score 0 declare @ s nvarchar (4000)
Set @ s =''
Select @ s = @ s + ', subject =' + quotename (Kind ,'''')
+ ', Score = max (case Kind when' + quotename (Kind ,'''')
+ 'Then Mark end )'
From studentMark
Group by Kind
Exec ('select studentid' + @ s + ', TestKindID
Into # from studentMark
Group by StudentID, TestKindID
Order by StudentID, TestKindID ')
Select * from # Top
Zjcxc (zhujian) on the 10th floor replied to 19:11:06, score 0 declare @ s nvarchar (4000)
Set @ s =''
Select @ s = @ s + ',' + quotename (rtrim (Kind) + 'subject ') +' = '+ quotename (Kind ,'''')
+ ',' + Quotename (rtrim (Kind + 'fractional ') +' = max (case Kind when' + quotename (Kind ,'''')
+ 'Then Mark end )'
From studentMark
Group by Kind
Exec ('select studentid' + @ s + ', TestKindID
Into # from studentMark
Group by StudentID, TestKindID
Order by StudentID, TestKindID ')
Select * from ##
Top
On the 11 th floor, crystal_table () replied to the Top learning score at 20:03:51 ,.
Sindia () on the 12th floor replied to 21:12:40, with a score of 0 up.
Top
On the 13th floor, yaoyaomaomao (Xiong) replied that he had scored 0 at 10:40:21 and he was able to score the score. The answer is completely correct. :) Top
On the 14th floor, FlyNow (a Chinese mainland user, who dares to take care of me, I am a public Clerk) replied to 11:59:55, score 0 server: Message 156, Level 15, status 1, row 6
There is a syntax error near the keyword 'from.
Top
Zjcxc (zhujian) on the 15th floor replied with a score of 0 from 12:04:11
-- Test
-- Test Data
Create table studentMark (ID int, StudentID int, Kind int, Mark float, TestTime DateTime, TestKindID int)
Insert studentMark select 1, 2004, 90, '2017-', 1
Union all select 2, 2004, 80, '1970-', 1
Union all select 3, 2004, 70, '1970-', 1
Union all select 4, 22004, 60, '1970-', 1
Union all select 5, 22004, 70, '1970-', 1
Union all select 6, 2004, 90, '1970-', 1
Union all select 7, 2004, 50, '1970-', 1
Union all select 8, 2004, 40, '1970-', 1
Union all select 9, 2004, 20, '1970-', 1
Union all select 10, 2004, 90, '2017-02-26 ', 2
Union all select 11, 2004, 80, '2017-02-26 ', 2
Union all select 12, 20, 3, 70, '2017-02-26 ', 2
Union all select 13, 22004, 60, '2017-02-26 ', 2
Union all select 14, 22004, 70, '2017-02-26 ', 2
Union all select 14, 2004, 90, '2017-02-26 ', 2
Union all select 15, 2004, 50, '1970-02-26 ', 2
Union all select 16, 2004, 40, '2014-02-26 ', 2
Union all select 17, 2004, 20, '1970-02-26 ', 2
Go
-- Query
Declare @ s nvarchar (4000)
Set @ s =''
Select @ s = @ s
+ ',' + Quotename (rtrim (Kind) + 'account') + '=' + quotename (Kind ,'''')
+ ',' + Quotename (rtrim (Kind) + 'fractional ') +' = max (case Kind when' + quotename (Kind ,'''')
+ 'Then Mark end )'
From studentMark
Group by Kind
Exec ('select studentid' + @ s + ', TestKindID
Into # from studentMark
Group by StudentID, TestKindID
Order by StudentID, TestKindID ')
Select * from ##
Drop table ##
Go
-- Delete test
Drop table studentMark
