Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to use a sentence of SQL to solve the problem of SQL broken numbers". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to use a sentence of SQL to solve the problem of SQL broken numbers".

Noun interpretation of a break: for example, a continuously generated number that results in a discontinuous number due to an operation (usually deletion). We call this discontinuous number a break. For example, if there is a field in the database called contract number, and the normal format is 201106 contract 011 (representing the 11th contract in June 2011), then the first contract number should be 201106 contract 10, and the last one should be 201106 contract 12. When we delete the contract 201106 011, there will be 201106 October 010 followed directly by 201106 February 012, in which case it is called a break. In traditional systems, this kind of broken number is very common, such as the incremental type in the database, when a row is deleted, there will be a broken number, and customers often put forward requirements and do not want to have a broken number. The solution is usually that if a row of data is deleted, the next time you add it, you should fill in the broken sign. The problem is simple, and the solution is simple: write a C # method to get the number of the next record: copy the code as follows: public static int GetNextNumber (int [] iNumList) {int iTempStr = iNumList [0]; / / save the number of the previous record for (var I = 0; I) with a temporary variable

1) {return iTempStr + 1;} else {iTempStr = iNumList [I];} continue;} return iNumList [iNumList.Length-1] + 1;} of course, this code can also be abbreviated as follows: copy the code as follows: public static int GetNextNumber3 (int [] iNumList) {for (int I = 0, j = 1; j)

1) {return iNumList [I] + 1;}} return iNumList [iNumList.Length-1] + 1;} the test code is as follows: the copied code is as follows: static void Main (string [] args) {int [] iNums = {1,2,4,5,6,9,10}; / / deleted from the array 3prime7rai8, that is, 3jing7rai8 is a dash, and the next time it is added, the desired dash is 3 System.Console.WriteLine (BreakNumber.GetNextNumber3 (iNums)). System.Console.WriteLine (BreakNumber.GetNextNumber (iNums)) } the running result is as follows: I came into contact with this problem again a few days ago, because of the special scenario, using C# will increase the difficulty of development. If you want to solve the problem through SQL: create tables and make data SQL: copy the code as follows: CREATE TABLE testTable (Code int primary key) INSERT INTO testTable (Code) VALUES (1) INSERT INTO testTable (Code) VALUES (2) INSERT INTO testTable (Code) VALUES (3) INSERT INTO testTable (Code) VALUES (4) INSERT INTO testTable (Code) VALUES (5) INSERT INTO testTable (Code) VALUES (6) INSERT INTO testTable (Code) VALUES (7) INSERT INTO testTable (Code) VALUES (8) ) INSERT INTO testTable (Code) VALUES (9) INSERT INTO testTable (Code) VALUES (10) and then delete the third, 7, 8 rows of data Make these three lines have a break: DELETE FROM testTable WHERE Code in analysis: to generate a hyphen, that is, to make the column Code continuous, that is to say, the Code difference between every two lines is 1 because Code starts from 1 (the same calculation starts with other numbers), that is, according to the Code sorting number from small to large, the line with Code of 1 should be the first line, and the line with Code of 10 should be on line 10, that is, the line number of Code=. Preview the data as follows: delete the data before the row number: delete the data after the row number: it is obvious that before deleting the data, Code= line number, delete after Code is not equal to the equal sign, and delete the data after the first row of Code is not equal to the line number of the data, that is, the first to appear a broken number of data, that is, we want to query the results. If so, if there is a break in the database, you can find the break directly with the following statement: the result stands up. Another flaw in this code is that this method is dedicated to dealing with cases with a break, and should return Max (RowNumber) + 1 if there is no break. The correct code should be as follows: so far, the basic end of what I'm going to talk about today, I borrowed SQL2005's method row_number here, and there are similar methods in other databases, which you can explore for yourself. Is the problem completely solved? You can find that the above cases of broken numbers are all filled in from childhood, for example, 3, 7, 7, 8 is also a broken number, then fill 3. If a customer asks to fill the account from the main account (that is, when the number is broken by 3pd7 and 8), how to deal with it? The first two methods are easy to operate through the C# method, and here we mainly talk about the method of processing through SQL: so if you extend it again, how do you find out all the broken numbers? To achieve this, the general idea is to compare the current Code with the Code on the previous line, but due to the possibility of consecutive hyphens (for example, the deletion of lines Code=7, 8, and 9). What should be done at this time? My solution is that if max (code) equals 100, then I construct 100 lines first (how do I construct it? Find any table with more than 100 rows in the database and select top 100. if there is no table with more than 100 rows, the joint query will construct 100 rows), and then compare the row numbers of these 100 rows with code. If there is a Code row number, it is the break number. The example is as follows: suppose another table An already exists in the system, and its total number of rows is > max (Code). [note: of course, if such a table does not exist It can also be constructed by select], the SQL for querying all the broken numbers is as follows: at this point, the problem is over. The advantage of the above code is that you can solve all kinds of broken number problems with only one SQL statement, instead of using stored procedures, user-defined functions or loops in C#. Of course, you can also optimize it for performance, which is not listed here. Thank you for your reading, the above is the content of "how to use a sentence SQL to solve the problem of SQL interruptions". After the study of this article, I believe you have a deeper understanding of how to use a sentence of SQL to solve the problem of SQL interruptions, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.