Problem:
The table in the database has three attributes, the user (number, name, height), the height of the second to find out the height.
Build a Table statement
Create Table users ( intIdentity(1,1primarykey, varchar (), float );
Database tables: User tables (users)
Database fields |
Field meaning |
Data type |
Constraints |
Note |
Id |
Number |
Int |
Primary key |
Self-growth |
Name |
Name |
VARCHAR (20) |
|
|
Height |
Height |
Float |
|
|
Test data
Insert intoUsers (Name,height)Values('Zhangsan',182);Insert intoUsers (Name,height)Values('John Doe',122);Insert intoUsers (Name,height)Values('Zhang Qiang', the);Insert intoUsers (Name,height)Values('Wang Ming',122);Insert intoUsers (Name,height)Values('Jiang Ming',182);Insert intoUsers (Name,height)Values('Bakeron',182);Insert intoUsers (Name,height)Values('Zhang',182);Insert intoUsers (Name,height)Values('Ann',181);Insert intoUsers (Name,height)Values('Jian Jian',181);Insert intoUsers (Name,height)Values('Ming',181);Insert intoUsers (Name,height)Values('Little',177);Insert intoUsers (Name,height)Values(' Wang',123);Insert intoUsers (Name,height)Values(' Karnataka',175);Insert intoUsers (Name,height)Values('Wen Xin',166);Insert intoUsers (Name,height)Values('Kin Ming',155);Insert intoUsers (Name,height)Values('Lesbian', the);Insert intoUsers (Name,height)Values('qingqing', the);Insert intoUsers (Name,height)Values('Lele',155);Insert intoUsers (Name,height)Values('KK',123);Insert intoUsers (Name,height)Values('FF',122);Insert intoUsers (Name,height)Values('Lily', the);
Query statements
1, the query out no duplicate value of the second, that is, if the tallest height is 182, there are several people at the same height is 182, the height is found to be less than 182 height of the highest value.
1>, Mode 1:
Select MAX from where < (SelectMAX from users);
Meaning: The highest height value is queried first, then the highest height is less than the height of the value.
2>, Mode 2:
Select Top 1 from where not inch (SelectMAXfromorderbydesc;
Meaning: First check out the tallest height, exclude the height, then sort the data (descending) and query out the current first piece of data.
2, the query may be the same as the highest height value of the second place, that is, if the height is 182 182 height, The second place is still
Select Top 1 * from (selecttop2fromorderbydescOrder byASC;
Meaning: The data is sorted by height (descending), the first two items are queried, and the items are queried in ascending order of height.
Thanks: Thank you for your patience! If there is a better answer, I would like to advise you.
SQL query the biggest see more, query the second???