用最高效最简单的sql列出各班成绩最高的列表（List the highest ranked lists in each class with the most efficient and simple SQL）.doc

用最高效最简单的sql列出各班成绩最高的列表（List the highest ranked lists in each class with the most efficient and simple SQL） 1, table: table1 (FId, Fclass, Fscore), with the most efficient and simple SQL list of the highest list of classes, display classes, grades two fields. Select, fClass, max (fscore), from, table1, group, by, fClass, FID 2, there is a table table1, there are two fields FID, Fno, the word is not empty, write a SQL statement, listed in the table of a FID corresponding to a number of different Fno records. Class such as: 101a1001 101a1001 102a1002 102a1003 103a1004 104a1005 104a1006 105a1007 105a1007 105a1007 Result： 102a1002 102a1003 104a1005 104a1006 Select t2.* from table1 T1, table1 T2 where t1.fid and t1.fno t2.fno = t2.fid; 3, staff table empinfo ( Fempno VARCHAR2 (10) not null pk, Fempname VARCHAR2 (20) not null, Fage number not null, Fsalary number not null ); If the amount of data is about 10 million; write a SQL that you think is the most efficient; use a SQL to calculate the following four types of people: Fsalary9999, and, Fage 35 Fsalary9999 and Fage 35 Fsalary, 9999, and, Fage 35 Fsalary 9999 and Fage 35 Number of employees per employee; Select sum (case, when, fsalary 9999, and, Fage 35 Then 1 Else 0end) as fsalary9999_fage35, Sum (case, when, fsalary 9999, and, Fage, 35 Then 1 Else 0 End) as fsalary9999_fage35, Sum (case, when, fsalary, 9999, and, Fage 35) Then 1 Else 0 End) as fsalary9999_fage35, Sum (case, when, fsalary, 9999, and, Fage, 35) Then 1 Else 0 End) as fsalary9999_fage35 From empinfo; 4, table A fields are as follows Month person income Monthly personnel income Requires a SQL statement (attention is one) where all (non personnel) monthly and last month and next months total income Request list output as In July, the months earnings last month earned income next month MONTHS PERSON INCOME ------------------------------200807 mantisXF 5000200806 mantisXF2 3500200806 mantisXF3 3000200805 mantisXF1 2000200805 mantisXF6 22002008