Usage of the mysqlcase... when Statement _ MySQL

Usage of mysqlcase... when statement bitsCN.com

Usage of mysql case... when statement

Generally, case... when is used in select statements, but can be used in other clauses.

1. perform custom sorting in the order by clause

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

2 Ding

3 B

4 C

Now we want to sort the B field in the order of A and B. order by B cannot be implemented directly.
You can add a sort field to sort by this field.

However, it can be implemented using the case when statement.

SQL code

Select * from 20130225t1

Order by case B when 'A' then 1 when 'B' then 2

When 'Bing' then 3 when 'Ding' then 4 end

1

3 B

4 C

2 Ding

Another method is the find_in_set function.

SQL code

Select * from 20130225t1

Order by FIND_IN_SET (B, 'a, B, c, Ding ')

1

3 B

4 C

2 Ding

2. the where clause is used for conditional query.

Consider the following two tables:

SQL code

Show create table 20130225 work

Create table '2018025work '(

'Id' tinyint (4) not null AUTO_INCREMENT,

'Role _ id' tinyint (4) not null,

'Dep_id' tinyint (4) not null,

Primary key ('id ')

) ENGINE = InnoDB

SQL code

Show create table 20130225 role

Create table '201430225role '(

'Id' tinyint (4) not null,

'Role _ name' varchar (5) default null,

Primary key ('id ')

) ENGINE = InnoDB

20130225work is an application submitted by each department. role_id indicates the id of the role to be applied for, and dep_id indicates the department to submit the application.

20130225role is a role.

Select X from 20130225 role

1. department manager

2. general manager

Select * from 20130225 work

1 1 1

2 1 2

3 2 1

4 2 2

Now the problem is that the department manager role can only see the application of his own department, and the general manager can see the application of all departments.

(1) you can add the need_check field in 20130225work. 1 indicates that the application needs to be checked by the department. 0 indicates that the application does not need to be checked.

Search for the role application based on the role id and dep_id

SQL code

Set @ role_id = 1;

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 2 0

(2) add need_check to 20130225role.

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.

BitsCN.com