A SQL sorting method, come in please, thank you!-CodePudding

According to the following order priority order
1, all the data is the same as 1.11 2.22 3.33 ~ 9.99 decimal point on the left side of the big priorities
2, two to zero after the decimal 1.00 2.00 3.00 ~ 9.00 decimal point on the left side of the big priorities
3, after the decimal two same 0.11 0.22 0.33 ~ 0.99 decimal point on the right priorities
4, three even number 1.23 2.34 3.45 ~ 7.89 numerical priorities
5, three even number 9.87 8.76 7.65 ~ 3.21 numerical optimization sorting first
6 or above, no accord with the sort of three addition 0.35 1.69==8 6 is equal to 10 to remove 10 from 10 more than 10 modulo amount such as 13 take 3 big priority

The above order from 1 ~ 6 order

The following is a data
3.68
2.69
4.36
0.11
6.00
3.33
4.56
3.78
6.54
7.89
2.22
8.56
4.37
3.21
9.96
8.88
4.65
7.89

Thank you guys help!

CodePudding user response:

You make your own weight calculation function to sort,
Assume that the input parameters for d
The return value (weights) r for the int type
The key is your own decision out of 6 kinds of circumstances, the following calculation for r
1, r=1000 + d * 100
2, r=2000 + d
3, r=3000 + ROUND 100 + (d, 0, 1) * (1 - (d - ROUND (d, 0, 1))) * 100
4, r=4000 + d * 100
5, r=5000 + d * 100
6, r=6000 + three additive (d)

CodePudding user response:

Thank you

reference 1st floor Tiger_Zhao response:

make your own weight calculation function to sort,
Assume that the input parameters for d
The return value (weights) r for the int type
The key is your own decision out of 6 kinds of circumstances, the following calculation for r
1, r=1000 + d * 100
2, r=2000 + d
3, r=3000 + ROUND 100 + (d, 0, 1) * (1 - (d - ROUND (d, 0, 1))) * 100
4, r=4000 + d * 100
5, r=5000 + d * 100
6, r=6000 + three additive (d)

Thank you, good idea!

CodePudding user response:

You see below do you want?

 
; WITH t (num) AS (
UNION ALL SELECT 3.68 
UNION ALL SELECT 2.69 
UNION ALL SELECT 4.36 
UNION ALL SELECT 0.11 
UNION ALL SELECT 6.00 
UNION ALL SELECT 3.33 
UNION ALL SELECT 4.56 
UNION ALL SELECT 3.78 
UNION ALL SELECT 6.54 
UNION ALL SELECT 7.89 
UNION ALL SELECT 2.22 
UNION ALL SELECT 8.56 
UNION ALL SELECT 4.37 
UNION ALL SELECT 3.21 
UNION ALL SELECT 9.96 
UNION ALL SELECT 8.88 
UNION ALL SELECT 4.65 
The SELECT 7.89 
) 
SELECT * # INTO td FROM t 

; WITH a AS (
SELECT t.n um, STUFF (p., 1, 1, ' '), AS s CASE WHEN STUFF (p., 1, 1, ' ')='0 0, THEN 1 
The WHEN STUFF (p., 1, 1, ' ') LIKE '0 - [1-9], 0' THEN 2 
The WHEN STUFF (p., 1, 1, ' ') LIKE '0, 1-9 [], 0' THEN 3 
The WHEN STUFF (p., 1, 1, ' ')='0,1,1 THEN 4 
The WHEN STUFF (p., 1, 1, ' ')='0, 1, 1' THEN 5 
The ELSE 6 END AS NumType 
The FROM # td AS t 
CROSS the APPLY (
SELECT the ', '+ CONVERT (VARCHAR, 
CONVERT (INT, the SUBSTRING (CONVERT (VARCHAR, t.n um), sv. Number, 1)) - 
CONVERT (INT, CASE WHEN the SUBSTRING (CONVERT (VARCHAR, t.n um), sv. The number 1, 1)='. 'THEN the SUBSTRING (CONVERT (VARCHAR, t.n um), sv. Number 2, 1) 
WHEN the sv. Number=1 THEN the SUBSTRING (convert (VARCHAR, t.n um), sv. Number, 1) 
ELSE the SUBSTRING (convert (VARCHAR, t.n um), sv. The number 1, 1) END)) 
FROM the master. The dbo. Spt_values AS sv 
WHERE the sv. Type='P AND sv. Number BETWEEN 1 AND LEN (t.n um) 
And the SUBSTRING (convert (VARCHAR, t.n um), sv. Number, 1)! 
='. 'FOR XML PATH (' ') 
), p (s) 

) 
SELECT * FROM a ORDER BY NumType, CASE WHEN a.N umType LIKE '[1245]' THEN a.N um ELSE - num END desc 

/* 
Num s NumType 
8.88 0, 0, 1 
3.33 0, 0, 1 
2.22 0, 0, 1 
6.00 0, 6, 0 2 
0.11 0, 0 3 
7.89 0,1,1 4 
7.89 0,1,1 4 
4.56 0,1,1 4 
6.54 0-1-1, 5 
3.21 0-1-1, 5 
2.69 0, 3 6 
3.68 0 31 6 
3.78 0,4,1 6 
4.36 0, 1, 3-6 
4.37 0, 1, 4, 6 
4.65 0, 2, 1 6 
8.56 0, 3, 1 6 
9.96 0, 0, 6-3 

*/
/* 
Num 
8.88 
3.33 
2.22 
6.00 
0.11 
7.89 
7.89 
4.56 
6.54 
3.21 
2.69 
3.68 
3.78 
4.36 
4.37 
4.65 
8.56 
9.96 
*/

CodePudding user response:

To achieve this functionality, you can only use weighted
Add a column in the table records weights, change data are the most time to refresh with weighted formula

CodePudding user response:

Missed a condition, I write this statement and other places not rigorous, considering there may be repeat num, best can create a unique serial number,
In addition to the last and after more than 10 take, 24 and 14 of the same value, value may not only three Numbers, etc.

 
; WITH a AS (
The SELECT t.n um, STUFF (p., 1, 1, ' ') AS s, s.t n 
That CASE WHEN STUFF (p., 1, 1, ' ')='0, 0,' THEN 1 
The WHEN STUFF (p., 1, 1, ' ') LIKE '0 - [1-9], 0' THEN 2 
The WHEN STUFF (p., 1, 1, ' ') LIKE '0, 1-9 [], 0' THEN 3 
The WHEN STUFF (p., 1, 1, ' ')='0,1,1 THEN 4 
The WHEN STUFF (p., 1, 1, ' ')='0, 1, 1' THEN 5 
The ELSE 6 END AS NumType 
The FROM # td AS t 
CROSS the APPLY (
SELECT the ', '+ CONVERT (VARCHAR, 
CONVERT (INT, the SUBSTRING (CONVERT (VARCHAR, t.n um), sv. Number, 1)) - 
CONVERT (INT, CASE WHEN the SUBSTRING (CONVERT (VARCHAR, t.n um), sv. The number 1, 1)='. 'THEN the SUBSTRING (CONVERT (VARCHAR, t.n um), sv. Number 2, 1) 
WHEN the sv. Number=1 THEN the SUBSTRING (convert (VARCHAR, t.n um), sv. Number, 1) 
ELSE the SUBSTRING (convert (VARCHAR, t.n um), sv. The number 1, 1) END)) 
FROM the master. The dbo. Spt_values AS sv 
WHERE the sv. Type='P AND sv. Number BETWEEN 1 AND LEN (t.n um) 
And the SUBSTRING (convert (VARCHAR, t.n um), sv. Number, 1)! 
='. 'FOR XML PATH (' ') 
), p (s) 
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull