After reading these tests below, your SQL level will have new improvement.
Let's take a look at the topic: 2D tables T (F1, F2, F3, F4, F5, F6, F7) are shown below: ┌ --──────────────────────────────────────── ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ────────────────────────────────────────────────────────────────────────────yle >> Our Name │ │ │ ┼ - ┼─────────────────────────────────────────────────────────────────────────────tes 03 │ │Panzi │ 53 │ T4 │ Teacher Zhao │├ ├───────────────────────────────────────────────────────── ─ ─ ─ ─ ─ │ K1 │ Mathematics │ 61 │ T1 │ Zhang Teacher │├───────────────────────────────────────────────────────── John Doe ───┤│ S2 │ │ K3 │ English │ 88 │ T3 │ Li │├─────┼────┼─────┼─────┼───── ┼─────────── ─ │ S1 │ 张 │ K4 │ Politics │ 77 │ T4 │ Zhao Teacher │├──────────────────────────────── John Doe ───┼─────┼─────┼─────┤│ S2 │ │ K4 │ │ 67 │ T5 │ political Chou │├─────┼──── ┼──────────────────────────────────────────── ─te │ K2 │ │ 90 │ T2 │ 王 老 │├- ─ ─ ─ ─ ┼──────────────────────────────────────────────────────────────tes 55 │ T1 │ Zhang Teacher │ ├ - ─ ─ ─ ─ ─ ───────────────────────────────── S1 │ 张三 │ K2 │ language │ 81 │ T2 │ 王 老 │├──────────────────────────────────────────── │ S4 │ Zhao Six │ K2 │ Language │ 59 │ T1 │ 王 老 │├───────────────────────────────────────────────────────────────────────────────────── ─ ─ ─ ┼──── ─ ┤│S1 │ 张三 │ K3 │ English │ 37 │ T3 │ Li Teacher │├───────────────────────────────────────── ────────────────── ─ ┤│S2 │ 李 四│K1 │ Mathematics │ 81 │ T1 │ Zhang Teacher │├────────────────────── ─ ┼─────────────────────────── ─ │ │ │ │ │ ├───────── ┼──────────────────────────────────────────── │ │ │ │ │ └─── ┴ ─ ─ ──────────────────────────────────────────────────── ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ "Transcript"
1. If the T table also has a field F data type is an automatic incremental integer (unique, no repetition), and the T table contains except for the F field, delete other fields excessive dirty recovery data: this The problem is a problem with the "logical repetition" record. Of course, this situation can be used to eliminate the primary key constraint! However, the reality is often the original data in "washing", and the logic master key is too early. Constraint, will bring inconvenience to the original data, for example: read attendance records from the card machine. When the application data is applied, the dirty data will be swept away! The reason why you want to keep an automatic identity column, because it is indeed the premise necessary for the following answers:
DELETE L from "L Join" transcript "R on L." student ID "= r." student ID "and L." course ID "= r." Course ID "and L.f> R.f
This is one of the most exciting ways to think about and mostly effective. Use not to wait, you can leave the same set of repeated data in the F field value, and select other deletion. If there is only one, it will not be selected. Here is to emphasize that everyone must clearly clear the basic table of the operation is also the two-dimensional table data set by the Basic Table after the delete keyword and the filter condition is connected by the basic table, which is all of the FROM clause. . At least one individual name is taken to reference the Basic table. The use of alias is very convenient when writing a large number of SQLs, and facilitates the constructive SQL. If necessary, it is necessary to continue to use the WHERE clause. If the above example is not intuitive, the following mimics an inequality of self-contained, there is a set of numbers (1, 2, 3), make a non-equal contact, so that the left subset is greater than the right subset, is: 2 1 3 1 3 2 If the left subset is now selected, it is 2 and 3. 1 No smaller data in the right can match it, so it is filtered. If the data is repeated, the efficiency will be unconventional. Fortunately, it is not SELECT, but DELETE does not need to return the result set, which affects nature.
Delete t from transcripts T where f not in (SELECT MIN (f) from scores I group by I. Student ID, i. Course ID HAVING Count (*)> 1) And f not in (SELECT MIN (f) from Press Table I Group by I. Student ID, I. Course ID HAVING Count (*) = 1)
This method is simple, just like translation natural language, it is very accurately described with the characteristics of eligible records, and even the second condition is indeed. At least one> = number should be used to merge these two conditions or only retain any of the conditions, and improve efficiency.
Delete t from transcript table T where f> (SELECT MIN (f) from transcripts as I where I. Student ID = T. Student ID and I. Course ID = T. Course ID Group By I. Student ID, I. Course ID)
This method is basically a correlation subquery version of the method, and the readers of the Cartesi can be well understood, and the statistical function is used, so the efficiency is not too high. The careful readers will find that the Group By clause in the query is not necessary, and it should be improved.
About debugging of the Delete statement, experienced programmers will temporarily replace the dangerous delete with a harmless Select. For example: Select L. * - Delect L temporarily annotated from "L Join" transcript "R on L." student ID "= r." student ID "and L." courses ID "= r." Course ID "and LF> RF
In this way, it greatly reduces the possibility of online data being unintentional, and of course, the data is also important in advance. Similarly UPDATE and INSERT write should also act according to this. From the perspective of the relationship between database principles, these write operations are typical "Selection" operations, Update and INSERT and "Projection" operations, they are all these relationships. "Write" application's performance form. In fact, the purpose of the query is also nothing more than browsing, deleting, updating or inserting. Usually written is more than the read operation, if the index is too large, it will only reduce efficiency.
Choosing "Sub Query" or "Connection" is different in efficiency, but the most critical difference is to express the read and write of the result set of queries, the developer is writing a "read-only" query record set, The respective efficiency of "subquery" and "connect" should be first considered, but if you want to implement the query result set of "writable" applications, whether it is related or non-related sub-queries, it is difficult to avoid in complex applications. .
In the above solution, it should be said that the first method, simple and effective, and is very creative, it is a recommended method. Of course, the easiest way to write should be:
Delete T from T, T T1 WHERE T. Student ID = T1. Student ID and T. Course ID = T. Course ID and T.f In fact, this is the "standard" of the method (but it is not "ANSI / ISO SQL" standard) connection writing method, the following answers are generally not to be understood, this is also the "ANSI / ISO SQL" standard. The encouragement, Join is indeed more easily expressed the relationship between the tables, and interested readers can override themselves. If you use "* =" to implement two tables, it is important to note that the AND condition of the WHERE clause is in order, and the "ANSI / ISO SQL" standard does not allow the order of WHERE conditions affect the results of the query, but The order of each table connection of the FROM clause can affect the results of the query. 2. Related records of the highest score of each department: (is the highest, minimum score of each course) Course ID, course name, highest score, student ID, student name, teacher ID, teacher name, Minimum points, student ID, student name, teacher ID, teacher name If this question, if you just find the highest score or minimum score, it is a very simple question: SELECT L. Course ID, Max (L. Course Name), Max (L. Grade) AS The highest score, min (l. Grade) AS lowest FROM transcript LGROUP BY L. Course ID However, the topic of the drill is to Lists relevant records related to the highest and minimum achievements of various subjects, which is often true. Since you have elected the highest and minimum points of each department, then the rest is to incorporate the information of students and teachers into this result set. If you write it, it is very troublesome, because the fields to add too much, soon make the code difficult to manage. Still change your ideas: SELECT L. Course ID, L. Course Name, L. [Result] AS Maximum, l. [Student ID], L. [Student Name], L. [Teacher ID], L. [Teacher Name], R. [Result] The lowest score, R. [Student ID], R. [Student Name], R. [Teacher ID], R. [Teacher Name] From Transit Table L Join Transcripts AS R On L. [Course ID] = R. [Course ID] WHERE L. [grade] = (Select Max (IL. [Grade]) from transcript AS [IL] WHERE L. [Course ID] = IL. [Course ID] Group By IL. [ Course ID]) And R. [Result] = (Select Min (IR. [Results]) from transcript AS [IR] WHERE R. [Course ID] = IR. [Course ID] Group By IR. [Courses ID] At first glance, it seems that it is very complicated. In fact, if you have mastered the basic methods of constructing cross-facing table and the knowledge of related situations, the problem is solved. Since the lowest and highest points are for course information, the answer cleverly merges the course information to the highest score, of course, can also be merged into the minimum. The code is in the rules, the style is very good, readability is also good. 3. With average score from high to low order, print all students' four (mathematics, language, English, political) course scores: (就 每 成 单 成) Student ID, Student Name, Mathematics , Language, English, politics, effective courses, effective average (Note: Effective courses are recorded in the T table, if you don't understand how you can print "effective courses" and "effective average points") It is to be noted that the title clearly proposes "four-door (mathematics, language, English, political) courses" is reasonable, because of the realization, it is indeed possible to avoid the value of the data on the line in the original table. This is another typical "row variable" related sub-query: SELECT Student ID, Max (Student Name) AS Student Name, (SELECT Score from Redissibility WHERE Student ID = T. Student ID AND Course ID = 'K1') AS Mathematics, (SELECT Score from Pied WHERE Student ID = T. Student ID and course ID = 'k2') AS language, (SELECT score from transcripts WHERE students id = T. Student ID and course id = 'k3') AS English, (SELECT score from transcript WHERE students ID = T. Student ID AND Course ID = 'K4') AS Politics, Count (*) AS Effective Course Number, AVG (T. Results) AS Average Results From Transcripts AS TGROUP BY Students Idorder BY Average Results This can be said to be a very rule of solution, in this application, the child query is much more readable than the coupling code. If the database engine thinks it is better to parse it into a join, it will be went, in fact, the associated subquery must also contain a connection. Here, add another table Ranks (Rank, MinValue, MaxValue) in practical applications: ┌────────────────────────────────────────────────────────────────────────────────────────────────── ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ 100 │ ├────────────── ┤ │ B │ 80 │ 89 │ ├──────────────────── ─ ┤ │ C │ 70 │ 79 │ ├───────────────── │ D │ 60 │ 69 │ ├──────────────────── E │ 0 │ 59 │ └ --────────────────────── ─ ─ ┘ can achieve a very practical application: SELECT Student ID, Max (Student Name) AS Student Name, (SELECT Score from Redissive Table T WHERE Student ID = T0. Student ID AND Course ID = 'K1') AS Mathematics, (Select Max (Rank) from Ranks, transcript T WHERE T. Grade> = ranks.minvalue and t. grades <= ranks.maxvalue and t. Student ID = T0. Student ID and T. Course ID = 'K1') AS Mathematics Level, (SELECT Score FROM Pied T WHERE Student ID = T0. Student ID AND Course ID = 'K2') AS Language, (Select Min (Rank) from ranks, transcripts T where t. grade> = ranks.minvalue and t. grade <= ranks.maxvalue and T. Student ID = T0. Student ID and T. Course ID = 'K2') AS Chinese Level, (SELECT Score from Redict Table T WHERE Student ID = T0. Student ID AND Course ID = 'K3') AS English, Select max (rank) from ranks, transcript table t where t. Grade> = ranks.minvalue and t. Grade <= ranks.maxvalue and t. Student ID = T0. Student ID and T. Course ID = 'K3') AS English level, (SELECT score from transcripts t where students id = t0. Student ID and course id = 'k4') AS politics, (SELECT MIN ", score table T where t. Grade> = ran Ks.minvalue and t. grade <= ranks.maxvalue and t. Student ID = T0. Student ID and T. Course ID = 'K4') AS Political Level, COUNT (*), AVG (T0. grade), (SELECT Max (Rank) from ranks where avg (t0. grade)> = ranks.minvalue and avg (t0. grade) <= ranks.maxvalue) AS average level from transcript T0GROUP BY Student ID This surface is used on the surface, Think carefully, the interval of each record in the Ranks table is not intended, in fact, it can also be equivalent to the equivalent connection, such surface design undoubtedly there is a good extensibility, if the title is only required Print (student ID, student name, number of effective courses, effective average, average class): SELECT Student ID, Max (Student Name) AS Student Name, Count (*), AVG (T0. Grade), (SELECT MAX "(Rank) from ranks where avg (t0. grade)> = Ranks.minvalue and AVG (T0. Results) <= ranks.maxvalue) AS Average Level From Ti AS T0GROUP BY Student ID This solution is more comprehensive. Going back to the original question, introduce a relatively good way, only one simple grouping query can solve the problem, experienced readers may have already thought, that is, Case: SELECT Student ID, Min (Student Name), SUM (CASE Course ID WHEN 'K1' THEN SECTY ELSE 0 END) AS Mathematics, SUM (Case Course ID WHEN 'K2' THEN SEARE ELSE 0 End) AS Language, SUM (CASE Course ID WHEN 'K3' THEN Review ELSE 0 END) AS English, SUM (Case Course ID WHEN 'K4' THEN Review ELSE 0 End) AS Politics, Count (*) AS Effective Course, AVG (T. Score) AS Average Results From transcripts as Tgroup by student idorder by average grade DESC Although it may be a bit strange to see the answer, it is very good to understand, readability is not low, the efficiency is also high. But it can't be like the previous answer, and the student is out of the class in the middle of the score, and it is really zero. This solution fully utilizes the role of the CASE statement for data classification: CASE will score the results in four categories, and SUM is used to eliminate excess 0. Select [T]. [Student ID], Max ([T]. [Student Name]) AS Student Name, Max ([T1]. [Results]) AS Math, Max ([T2]. [Grade]) AS language , Max ([T3]. [Grade]) AS English, Max ([T4]. [Grade]) AS politics, count ([t]. [Lesson ID]) AS effective course, (Isnull ([T1 ]. [Grade]), 0) ISNULL (Max ([T2]), 0) ISNULL (Max ([T3]. [Grade]), 0) ISNULL (Max ([T4]. [Grade]), 0) / count ([t]. [Lesson ID]) AS effective average from FROM scores T Left Join transcripts AS [T1] ON [T]. [Student ID] = [T1]. [Student ID] and [t1]. [T2] = 'K1' Left Join Transcript, AS [T2] ON [T]. [Student ID] = [T2]. [Student ID] and [T2]. ID] = 'K2' Left Join Transcript AS [T3] ON [T]. [Student ID] = [T3]. [Student ID] and [T3]. [T3]. [Course ID] = 'K3' Left Join Transcript AS [T4] ON [T] = [T4]. [Student ID] and [T4]. [T4]. [Course ID] = 'K4' Group by [T]. [Student ID] ORDER BY effective average DESC This method is a fairly orthodox coupled solution, although it is troublesome, but it is not difficult to understand. Again from a practical point of view, true demand is often not like this topic to clearly propose a relatively static demand such as printing four-door (mathematics, language, English, political) courses. "It is time when dynamic SQL is big, it is obviously One way is undoubtedly the best choice for constructing dynamic SQL, of course, the other two SQL laws are still very obvious, the same is not difficult to construct. Taking the case version as an example: first use a cursor traversal, take out all the courses to join: SUM (Case 'course ID' courseway name 'TENGEE ELSE 0 END) AS course name form, replenish SELECT and FROM, WHERE If necessary, a SQL that generates dynamic transcripts is born, as long as it is executed by the relevant program call, it can be a more complete solution. In fact, the most similar typical application is the summary of the fine table in the primary, thin relationship of the main table projection, such as two tables: Master (f, f1, f2 ...) a pair of Details (f, F3, f4 ...) SELECT *, (Select Count (*) from Details Where Master.f = Details.f), (Select Sum (F3) from Details Where Master.f = Details.f) from master 4. According to the percentage of each subject, the percentage of low to high and post rate is from high to low order, and the average of the average grades and non-rate rates (indicated "N lines" is represented by "N row": (is the analysis of which Difficulties in the course ID, course name, average grade, comparison SELECT course ID, MAX (course name) AS course name, AVG (grade) AS average grade, STR (100 * Sum (Case WHEN score> = 60 THEN 1 ELSE 0 END) / count (*)) '%' AS and grid percentage from transcript TGROUP BY course iDorder by and grid percentage DESC This question should be said to be simple, just use "line" to provide performance form. As long as you want to understand what is packet you want to group, take the statistical aggregation function, you will be good. 5. The average score rate of the printed four courses (with "1 line 4 column"): (is the problem of analyzing which course is difficult) Mathematical average division, mathematical and grid population, Chinese average division, Chinese and Geometers, English Average score, English and genus score, political average, political and parallel This question is actually the "column" expression version of the previous question, which is static, because this question is similar to the third question, and the extension must be used to achieve additional Program construction dynamic SQL: Select SUM (Case WHEN Course ID = 'K1' THEN) ELSE 0 end) / sum (Case course ID when 'k1' Then 1 ELSE 0 END) AS mathematical average score, 100 * SUM (Case WHEN course ID = 'K1' AND score> = 60 THEN 1 ELSE 0 END) / SUM (Case When Course ID = 'K1' TEN 1 ELSE 0 END) AS Mathematics and Component Points, SUM (Case WHEN Course ID = 'K2' Then Reradiction ELSE 0 End) / SUM (CASE course ID when 'k2' Then 1 ELSE 0 END) AS language average, 100 * SUM (Case When course ID = 'K2' AND)> = 60 Then 1 else 0 End) / SUM (Case WHEN Course ID) = 'K2' THEN 1 ELSE 0 END) AS Language and Piece Points, SUM (Case WHEN Course ID = 'K3' THEN Review ELSE 0 End) / SUM (Case Course ID WHEN 'THEN 1 ELSE 0 End) AS English average Point, 100 * SUM (Case WHEN Course ID = 'K3' AND)> = 60 THEN 1 ELSE 0 End) / SUM (Case WHEN Course ID = 'K3' Then 1 ELSE 0 End) AS English and Grid Points, SUM (CASE WHEN Course ID = 'K4' TENGET ELSE 0 End) / SUM (Case Course ID WHEN 'K4' THEN 1 ELSE 0 End) AS Politics Average, 100 * Sum (Case WHEN Courses ID = 'K4' AND Score> = 60 THEN 1 ELSE 0 END / SUM (Case WHEN Course ID = 'K4' TEN 1 ELSE 0 End) AS Politics and Ge - Bumbe FROM Decoration Table THewei Looks, but actually is the most classic CASE application, very Practical data analysis technology. First, a series of results in the original table is 2 consecutive projections for four different courses, and use the value domain of Case and data ['K1', 'K2', 'K3', 'K4'] to divide the data, and then use SUM () [1 ... 1] implements the function of seems to be the counter that should be used with counters, don't talk about joint and subqueys, even the traces of Group By group can't be found! Read hard, you can only keep one field first, relatively understand, and make up one by one. This question can also be considered a "row" cross-perspective example! In addition, "line" is dynamic, "line" is relatively unlimited, "column" is relatively limited, "line" The increase is the application level, but "free" to delete, "column", "column", is managed, don't change it easily! 6. According to the different courses of different teachers, the average division is from high to low: (is what the teacher's level is high) Teacher ID, teacher name, course ID, course name, average SELECT teacher ID, MAX (teacher name) AS teacher name, course ID, MAX (course name) AS course name, AVG (grade) AS average grade FROM transcript TGROUP BY course ID, teacher idorder by avg (grade) DESC this If you don't say it, even if you turn your eyes, you don't move your hands, your answer should be out! If the average points take the highest points and one minimum, it is not difficult to write: SELECT teacher ID, MAX (teacher name), course ID, max (course name) AS course name -, AVG (grade) AS average results, (Sum (Sum) - (SELECT MAX) from transcripts WHERE course ID = T1. Course ID AND teacher ID = T1. Teacher ID) - (SELECT MIN) - (SELECT MIN) WHERE course ID = T1. Course ID AND teacher ID = T1. Teacher ID) / cast ((SELECT COUNT) ) -2 from transcript WHERE course ID = T1. Course ID AND teacher ID = T1. Teacher ID) AS FLOAT AS Average FROM Reviews AS T1WHERE (Select Count (*) -2 from transcript WHERE course ID = T1 Teacher ID = T1. Teacher ID)> 0Group BY Course ID, Teacher Idorder By Average Desc *********************************** *********************************************************** **************** 7. The 10th to 15th Mathematical Score or Print Average 5th to 15th Student Transcript [ Student ID], [Student Name], Mathematics, Language, English, Politics, Average If you only consider a course, such as: Mathematical results, very simple: SELECT TOP 5 * from T where course ID = 'K1' AND grade NOT IN (SELECT TOP 15 grade from t order by grade DESC) Order By grade Descunion Select * From t where course id = 'K1' AND score NOT IN (SELECT TOP 10 results from T Order BY grade DESC) and achieve in (SELECT TOP 15 results from T Order BY grade DESC) Order BY grade DESC Logically, the 10th to 15th is from the original top 15, "again" picked out the top 5 do not, retaining the remaining 5. The second way is to pick out the previous 10 records from 15 in front. If the two data sets are poor, there is a relatively low efficiency, which if necessary "ANSI / ISO SQL "Except keyword is ideal. This technique is often used in the application of data "paging", as long as follows the following principles: Select Top @Pagesize * from T where sortfield not in (select top @Pagesize * @Pagei sortfield from t order by sortfield) Order by Sortfield to this, the main purpose of the question is reached. As for the printed transcript: [Student ID], [Student Name], mathematics, language, English, politics, and average results have similar topics, doing it, so only provide reference answers, will not have explained: Select Distinct Top 5 [Selected], [Reviews]. [Student Name] AS Student Name, [T1]. [Result] AS Mathematics, [T2]. [Result] AS language, [T3] "AS English," [T4]. [Result] AS Politics, ISNULL ([T1]. [Grade], 0) ISNULL ([T2]. [Grade], 0) ISNULL ([T3]. Grade], 0) isnull ([T4]. [Grade], 0) AS total from FROM [transcript] LEFT JOIN [transcript] AS [T1] ON [transcript]. [Student ID] = [T1] [Student id] and [t1]. [Lesson ID] = 'K1' Left Join [transcript] AS [T2] ON [transcript]. [Student ID] = [T2]. [Student ID] and [T2] ]. [Lesson ID] = 'K2' Left Join [transcript] AS [T3] on [transcript]. [Student id] = [T3]. [Student id] and [t3]. [Lesson ID] = ' K3 'LEFT JOIN [transcript] AS [T4] ON [transcript]. [student id] = [T4]. [student id] and [t4]. [T4] =' K4 'WHERE ISNULL ([T1] [Grade], 0) isnull ([T2]. [Grade], 0) isnull ([T3]. [Grade], 0) isnull ([T4]. [Grade], 0) Not in (SELECT Distinct top 15 with Ties isnull ([T1]. [Grade], 0) ISNULL ([T2]. [Grade], 0) isnull ([T3]. [Grade], 0) ISNULL ([T4]. [Grade], 0) f ROM [transcript] Left join [transcript] AS [T1] ON [transcript]. [Student id] = [T1]. [Student id] and [t1]. [T1]. [T1]. [课] = 'K1' Left Join [ Serague] AS [T2] ON [transcript]. [Student ID] = [T2]. [Student id] and [t2]. [T2]. [Trial ID] = 'K2' Left Join [transcript] AS [T3] ON [Student ID] = [T3]. [Student ID] and [T3]. [Trial ID] = 'K3' Left Join [transcript] AS [T4] ON [transcript]. [Student ID]. ] = [T4]. [Student id] and [t4]. [Course id] = 'k4' Order by isnull ([T1]. [Grade], 0) isnull ([T2]. [Grade], 0) Isnull ([T3]. [Grade], 0) ISNULL ([T4]. [Grade], 0) DESC)
