mysql排序问题 求指点

MySql 码拜 8年前 (2015-11-16) 688次浏览
表格数据
mysql排序问题 求指点
要的结果  
就是   2个张医生中间放一个王医生,并且按照xh排序
mysql排序问题 求指点
表格创建语句:

CREATE TABLE `tab_ceshi` (                                                                                                                                                                                                                                                                                                   
             `id` bigint(20) NOT NULL AUTO_INCREMENT,                                                                                                                                                                                                                                                                                   
             `name` varchar(30) DEFAULT NULL,                                                                                                                                                                                                                                                                                           
             `xh` int(11) DEFAULT NULL,                                                                                                                                                                                                                                                                                                 
             `doctor` varchar(30) DEFAULT NULL,                                                                                                                                                                                                                                                                                         
             `keshi` varchar(30) DEFAULT NULL,                                                                                                                                                                                                                                                                                          
             `bz` varchar(30) DEFAULT NULL,                                                                                                                                                                                                                                                                                             
             PRIMARY KEY (`id`)                                                                                                                                                                                                                                                                                                         
           ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1   
INSERT INTO `tab_ceshi` VALUES 
(1,""张1"",1,""张医生"",""儿科"",""1""),
(2,""张2"",2,""张医生"",""儿科"",""1""),
(3,""张3"",3,""张医生"",""儿科"",""1""),
(4,""张4"",4,""张医生"",""儿科"",""1""),
(5,""张5"",5,""张医生"",""儿科"",""1""),
(6,""张6"",6,""张医生"",""儿科"",""1""),
(7,""王1"",1,""王医生"",""骨科"",""1""),
(8,""王2"",2,""王医生"",""骨科"",""1""),
(9,""王3"",3,""王医生"",""骨科"",""1""),
(10,""王4"",4,""王医生"",""骨科"",""1"");
解决方案:50分
SELECT *,CEILING(xh/2) AS ss,””1″” AS bz1 FROM `tab_ceshi` A WHERE a.`keshi`=””儿科””                                     
UNION
SELECT *,CEILING(xh/1) AS ss,2 AS bz2 FROM `tab_ceshi` A WHERE a.`keshi`=””骨科””                                       
ORDER BY ss,bz1,xh
解决方案:50分
mysql> select * from `tab_ceshi`;
+--+--+--+--+--+--+
| id | name | xh   | doctor | keshi | bz   |
+--+--+--+--+--+--+
|  1 | 张1  |    1 | 张医生 | 儿科  | 1    |
|  2 | 张2  |    2 | 张医生 | 儿科  | 1    |
|  3 | 张3  |    3 | 张医生 | 儿科  | 1    |
|  4 | 张4  |    4 | 张医生 | 儿科  | 1    |
|  5 | 张5  |    5 | 张医生 | 儿科  | 1    |
|  6 | 张6  |    6 | 张医生 | 儿科  | 1    |
|  7 | 王1  |    1 | 王医生 | 骨科  | 1    |
|  8 | 王2  |    2 | 王医生 | 骨科  | 1    |
|  9 | 王3  |    3 | 王医生 | 骨科  | 1    |
| 10 | 王4  |    4 | 王医生 | 骨科  | 1    |
+--+--+--+--+--+--+
10 rows in set (0.00 sec)
mysql> select *
    -> from `tab_ceshi`
    -> order by if(doctor=""张医生"",xh + (xh-1) div 2,xh*3);
+--+--+--+--+--+--+
| id | name | xh   | doctor | keshi | bz   |
+--+--+--+--+--+--+
|  1 | 张1  |    1 | 张医生 | 儿科  | 1    |
|  2 | 张2  |    2 | 张医生 | 儿科  | 1    |
|  7 | 王1  |    1 | 王医生 | 骨科  | 1    |
|  3 | 张3  |    3 | 张医生 | 儿科  | 1    |
|  4 | 张4  |    4 | 张医生 | 儿科  | 1    |
|  8 | 王2  |    2 | 王医生 | 骨科  | 1    |
|  5 | 张5  |    5 | 张医生 | 儿科  | 1    |
|  6 | 张6  |    6 | 张医生 | 儿科  | 1    |
|  9 | 王3  |    3 | 王医生 | 骨科  | 1    |
| 10 | 王4  |    4 | 王医生 | 骨科  | 1    |
+--+--+--+--+--+--+
10 rows in set (0.00 sec)
mysql>

CodeBye 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权 , 转载请注明mysql排序问题 求指点
喜欢 (0)
[1034331897@qq.com]
分享 (0)