求一个SQL排序方法，请进，谢谢！

MUGOER 2016-11-15 02:58:07

加精

根据以下排序优先权排序
1、所有数据相同如 1.11 2.22 3.33 ~ 9.99 小数点左边大优先排序
2、小数后两位为0 1.00 2.00 3.00 ~9.00 小数点左边大优先排序
3、小数后两位相同 0.11 0.22 0.33 ~0.99 小数点右边大优先排序
4、三位连数 1.23 2.34 3.45 ~ 7.89 数值大优先排序
5 、三位连数 9.87 8.76 7.65 ~ 3.21 数值大优化先排序
6、以上无符合的排序三位相加 0.35 ＝ 8 1.69 ＝ 6 等于 10 为10 大于10去掉10取余数如13 取 3 数额大优先

以上排序从 1 ~ 6 排序

以下是数据
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

感谢前辈们的帮助！

...全文

6343 23 打赏收藏转发到动态举报

写回复

用AI写文章

23 条回复

切换为时间正序

请发表友善的回复…

发表回复

ottorz 2020-08-28

打赏
举报

写一个函数返回数值是传入参数属于123456那种，先按函数返回值排序再按数值排序

0与1之间 2016-11-29

打赏
举报

应该可以写一标量函数，将排序的规则放函数里再调用

叫我老刘 2016-11-28

打赏
举报

看楼上写的太费劲，简单回复下

叫我老刘 2016-11-28

打赏
举报

select * from test 
order by 
substr(test1,0,1) desc,
substr(test1,3,4) desc,
(case substr(test1,3,1) WHEN substr(test1,4,5) THEN substr(test1,3,4) else null end) desc;

以上只计算了1、2、3排序，4、5、6可以以此类推，逻辑都是一样的，望对你有帮助

weixin_30396303 2016-11-26

打赏
举报

不是很懂这个额

tcmakebest 2016-11-19

打赏
举报

根据规则, 把0.00到9.99全部写在一张表中, 放一个排序字段, 手工算好填进去, 也就1000条记录.

xiaoxiangqing 2016-11-19

打赏
举报

这个只能自己写程序处理了。

jrl1993 2016-11-18

打赏
举报

yimoj-。-

ljheee 2016-11-18

打赏
举报

这么多啊

cattpon 2016-11-16

打赏
举报

learning~

hugh_z 2016-11-16

打赏
举报

6666666

nettman 2016-11-16

打赏
举报

学习下

屎涂行者 2016-11-16

打赏
举报

Struggle灬 2016-11-16

打赏
举报

来学习学习

2000风雨兼程 2016-11-16

打赏
举报

好复杂，学习下

中国风 2016-11-15

打赏
举报

可自定义每一种排序顺序方法 WHEN num*100%111=0 THEN 6000+(10-num) WHEN CAST(num AS INT)=num THEN 5000+num 查看效果 e.g.

;WITH t(num) AS(
  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 UNION ALL
  SELECT 7.89
  )
  SELECT * FROM t ORDER BY CASE WHEN num*100%111=0 THEN 6000+(10-num)
								WHEN CAST(num AS INT)=num THEN 5000+num
								WHEN num*100%11=0  THEN 4000 
								WHEN num*100%111=12 THEN 3000 
								WHEN num*100%111=99 THEN 2000 
								ELSE  (CAST(num AS INT)+CAST(num*10%10 AS INT)+CAST(num*100%10 AS INT))%10*10
								END DESC 

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

中国风 2016-11-15

打赏
举报

用语句太耗性能，这不是数据库处理的事

借用#5测试数据
看你的规律可以用以下算法相对比较高效

e.g.

;WITH t(num) AS(

  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 UNION ALL

  SELECT 7.89

  )

  SELECT * FROM t ORDER BY CASE WHEN num*100%111=0 THEN 6000

								WHEN CAST(num AS INT)=t.num THEN 5000

								WHEN num*100%11=0  THEN 4000 

								WHEN num*100%111=12 THEN 3000 

								WHEN num*100%111=99 THEN 2000 

								ELSE  (CAST(num AS INT)+CAST(num*10%10 AS INT)+CAST(num*100%10 AS INT))%10*10

								END desc, num desc





/*

num

8.88

3.33

2.22

6.00

0.11

7.89

7.89

4.56

6.54

3.21

8.56

3.78

3.68

2.69

4.65

9.96

4.37

4.36

*/

道素 2016-11-15

打赏
举报

漏了一个条件，我写的这个语句还有其他不严谨的地方，考虑可能有重复的num，最好能创建一个唯一的序号，另外对于最后取和后与10 取余，24和14的值相同，数值可能不仅三个数字等


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

  )
SELECT a.num,a.tn FROM a ORDER BY NumType,CASE WHEN a.NumType LIKE '[1245]' THEN a.num WHEN a.NumType=3 THEN  -num ELSE a.tn%10 END desc

中国风 2016-11-15

打赏
举报

你要实现这样的功能，只能用加权在表加多一个栏位记录权值，最次变更数据都用加权公式去刷新

道素 2016-11-15

打赏
举报

你看下面是不是你要的？


;WITH t(num) AS(
  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 UNION ALL
  SELECT 7.89
  )
  SELECT * INTO #td FROM t

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

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

/*
num	s	NumType
8.88	0,0,0	1
3.33	0,0,0	1
2.22	0,0,0	1
6.00	0,-6,0	2
0.11	0,1,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,4,3	6
3.68	0,3,2	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,-3	6

*/
/*
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
*/