How mysql article 1000 take a-CodePudding

How mysql article 1000 take a
Table structure is as follows:
Id int
The name varchar
Create_time datetime

CodePudding user response:

Id orderly on the uninterrupted, direct the WHERE id % 1000=1
Otherwise, sorted by ID, build an orderly non-stop on the list, and then use this column to do computing

CodePudding user response:

My logic is to increase a sort field first, with the serial number divided by 1000, the result is an integer, meet the conditions, the following case is once every 10 I pick up my

 DECLARE @ TEMP TABLE (ID VARCHAR (50), the NAME VARCHAR (50)) 
INSERT INTO @ TEMP 
SELECT the 'A', 'x' UNION ALL 
SELECT 'B', 'x' UNION ALL 
SELECT the 'C', 'x' UNION ALL 
SELECT the 'P', 'x' UNION ALL 
SELECT the 'D', 'x' UNION ALL 
SELECT the 'E', 'x' UNION ALL 
SELECT the 'F', 'x' UNION ALL 
SELECT the 'G' and 'x' UNION ALL 
SELECT the 'H', 'x' UNION ALL 
SELECT the 'I' and 'x' UNION ALL 
SELECT the 'J' and 'x' UNION ALL 
SELECT the 'K' and 'x' UNION ALL 
SELECT the 'L', 'x' UNION ALL 
SELECT the 'M' and 'x' UNION ALL 
SELECT 'N', 'x' UNION ALL 
SELECT the 'O' and 'x' 

DECLARE @ number numeric (10, 2) 
The SET @ number=10 

- serial number by the 
B. SELECT *, CASE WHEN CAST (B.R esult AS INT)=B.R esult 
THEN 'integer' 
The else 
'decimal' 
End 
The FROM (
SELECT A. *, Result=CAST (A.N O/@ number AS numeric (10, 2)) FROM (
SELECT NO=ROW_NUMBER () OVER (ORDER BY ID) - 1, * FROM @ TEMP) 
 A) B

Finally, the integer can be found out

CodePudding user response:

1) for a monotone increasing id value, where the mod (1000), I=1, is relatively simple, a full table scan,
2) simple and crude limit n, 1 program for training in rotation, but for larger amount of data table, with batch larger, slower,
Article 3) for each batch to take 1000, take the first, and get the most id value, as the next batch of id starting conditions, SQL performance with data volume growth does not change,