SQL sever grouping and sqlsever grouping

SQL sever grouping and sqlsever grouping

When maintaining personnel, the Personnel Bureau requires that a new function be added. The specific requirements are as follows: the added personnel are numbered in sequence in the same unit, and the unit must be sorted in time. That is to say, there are two orders, first, the names of the first unit are sorted by creation time, and then by creation time. Second, the people of each unit are sorted by a certain encoding. I have listened to the advice of Shi Ge and used an independent number for each newly added unit. For example, the first unit created is 1, then there are 2, 3, and 4 in sequence, so that the sorting can be very good. Here I create a trigger for the table to be operated to implement this function. The code and description are as follows.

-- ===================================================== ====== -- Author: xu fengzhu -- Create date: 16:10:04, January 1, July 7, 2015 -- Description: when inserting data, check whether the unit name exists. If not, add the unit name, view groups -- ============================================== ========= alter trigger [dbo]. [trg_addoutstanding] ON [dbo]. [T_OutstandingDispatch] after insert, UPDATEASdeclare @ myID varchar (50), @ mycompany varchar (50), @ mygroupid bigint, @ sum bigint, @ maxnum bigint -- Obtain the file number from the newly inserted content. unit name: select @ myID = number, @ mycompany = companyName frominsertedif update (companyName) BEGIN -- view the total number of records with the same unit name in the table. select @ sum = COUNT (*) from T_OutstandingDispatch where companyName = @ mycompany -- query the largest group number in the table, so as to select @ maxnum = MAX (groupID) from T_OutstandingDispatch for the new unit group number -- query the Group Number of the same unit (exists ), in order to assign the same group number to the new unit group number selecttop 1 @ mygroupid = groupID fromT_OutstandingDispatch where companyName = @ mycompany -- if there is a record with the same unit name greater than 1 (exclude itself, otherwise, write the new group number in order if (@ sum> 1) updateT_OutstandingDispatch setgroupID = @ mygroupid where number = @ myIDelseupdateT_OutstandingDispatch setgroupID = @ maxnum + 1 where number = @ myIDEND

 

 

 

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.