For repeated row deletion problem, the Internet is difficult to find the right answer, ask a lot of questions, but in the search engine cursory look at the previous record there is no solution.
In fact, this problem can be very gorgeous solution.
1, if the table does not have a primary key (or the same row does not have a different content column), you need to create a new self column, to distinguish between different columns. For example
Copy Code code as follows:
ALTER TABLE [tablename] add [TID] int IDENTITY (1,1)
is to increment the temporary column tid.
Why do you use SQL statements? If more than hundreds of thousands of lines are used, the design interface modification of SQL Server Enterprise Manager usually times out.
2, then is the key. An example is the list of IP addresses that I have in hand to handle. Nearly 400,000 data, there are SIP and EIP (Start IP and end IP) record repeat, the number of duplicates is about 1/5. This situation is done with a simple SQL command:
Copy Code code as follows:
Delete from query_ip where TID isn't in (select Max (TID) from QUERY_IP Group by SIP,EIP)
The SIP and EIP are grouped together to obtain the largest TID value of the same grouping. Then delete the contents of the original table that are not in it (that is, the smaller ID content in the same group of duplicates).
This way of thinking can extend a lot of SQL solution. For example, a user landing table, to see each user's recent log log.
A very elegant query statement:
Copy Code code as follows:
SELECT * from Loginlog where ID in (select Max (ID) from Loginlog Group by UserID)
SQL is powerful, and many complex requirements can often be merged into a single SQL statement query. Therefore, in my program, in addition to Update/insert and other operations require transaction support, or the record is too large to need a paging or temporary table. is usually implemented with a single SQL statement. For example, select *, (SELECT COUNT (*) from XXX where xxx=t.id) from t where .... This allows the associated statistics to be obtained in the SELECT statement, especially for small to medium systems.
