Usage of mysql case... when statement

Mysql case... the usage of the when statement is generally case... when is used in select statements, but 1 can be used in other clauses to perform custom sorting in order by clause www.2cto.com SQL code show create table 20130225t1; create table '201430225t1 '('id' tinyint (4) not null AUTO_INCREMENT,' B 'char (1) NOT NULL, PRIMARY KEY ('id ')) ENGINE = InnoDB select * from 20130225t1; 1 A 2 Ding 3 B 4 Bing www.2cto.com now want B fields to be sorted in the order of A and B. order by B directly cannot be implemented. orthodox
You can add a sort field to sort by this field. however, you can use the case when statement to implement the SQL code select * from 20130225t1 order by case B when 'A' then 1 when 'B' then 2 when 'C' then 3 when 'ding' then 4 end 1 A 3 B 4 C 2 d Another means is the find_in_set function SQL code select * from 20130225t1 order by FIND_IN_SET (B, 'A, B, c, ding ') 1 A 3 B 4 C 2 D 2 is used in the where clause for condition query. Consider the following two tables: SQL code show create table 20130225 work CREATE TABLE '201430225work' ('id' tinyint (4) not null AUTO_INCREMENT, 'Role _ id' tinyint (4) not null, 'depp _ id' tinyint (4) not null, primary key ('id ')) ENGINE = InnoDB SQL code show create table 20130225 role CREATE TABLE '3030225role' ('id' tinyint (4) NOT NULL, 'Role _ name' varchar (5) DEFAULT NULL, primary key ('id') ENGINE = InnoDB 20130225work is the application submitted by each department. role_id is the id of the role to be applied for. dep_id is the role of the submitted department 20130225role. select * from 20130225 role 1 Department Manager 2 General Manager select * from 20130225 work 1 1 1 2 1 2 2 2 1 2 2 2 now the problem is that the department manager role can only see the application of its own department, the general manager can see the applications from all departments. (1) You can add the need_check field in 20130225work. 1 indicates that the application needs to check the department, 0 indicates that you do not need to search for the application SQL code set @ role_id = 1 for the role based on the role id and dep_id. set @ dept_id = 1; select * from 20130225 work where role_id = @ role_id and (case need_check when 1 then dep_id = @ dept_id else 1 = 1 end) 1 1 1 1 SQL code set @ role_id = 2; set @ dept_id = 1; select * from 20130225 work where role_id = @ role_id and (case need_check when 1 then dep_id = @ dept_id else 1 = 1 end) 3 2 1 0 4 2 0 (2) or add need_check to 20130225role for www.2cto.com SQL code set @ role_id = 1; set @ dept_id = 2; select * from 20130225 work w, 20130225 role r where r. id = @ role_id and w. role_id = r. id and (case need_check when 1 then dep_id = @ dept_id else 1 = 1 end) case when can also appear in the group by statement, but I don't know what it can do. in fact, I want it to appear in the from statement, and I can dynamically specify the from table.