SQL Server---Data type conversions during query
The first two days in the maintenance of a city Talent service Center personnel file Management system, found this problem the new file number rule is the date + has the file maximum number +1 (six bits, less than six bits in the middle of 0) ((CONVERT ([varchar] (9), [Createtime], ( ) (+right) (100000000000.) +[num], (6))))) For example: 20150511007841. Honestly, I don't know why I used the maximum number, not the total number of records, +1 (no delete data), and then we'll talk about the problem and how to solve it.
Problem reproduction
Total number of records in query table +1
Select count (number) +1 from t_universitystudent
Execution Results
Querying the table for maximum data +1
Select MAX (number) +1fromt_universitystudent
Execution Results
Cause of the problem
Why is it that there are 7,840 data in the database, and the data record is 1 each time, why does it cause this problem?
I would like to have experienced developers, already know, is not the data type has a problem. 9>1000 is established in the comparison of the size of the string, which means that 999>7840 is also correct.
We sort it out to verify if the above statement is correct.
Select number from T_universitystudentorderbynumber ASC
Execution results
Problem solving
Now that we know that there is a problem with the field design process, the simplest part is that the data type of the field is changed, but this method is not the best because we do not understand the consequences of modifying the data type.
Then we can use the cast function and the CONVERT function.
The specific usage is as follows
Sort by converting a varchar type to an int type (using the CONVERT function)
Select number from T_universitystudentorderbyconvert (int,number) ASC
Execution results
Convert varchar in a query field to int
Select MAX (CAST (numberasint)) +1fromt_universitystudent
Execution results
It is important to note that the CONVERT function can only be used in SQL Server and that the cast function is available in Oracle and SQL Servers, and that the reader has to try it out for support in other databases.
SQL Server---Data type conversions during query