The use of the DB2 database decode () function

first, according to the field content grouping;

Less gossip.

1. Create a table T1:sql statement as follows

CREATE TABLE T1 (
ID integer NOT NULL primary key,
Name varchar (10),
Job varchar (10)
);

2. Insert some data:

INSERT into T1 values (1, ' Jack ', ' VP ');
INSERT into T1 values (2, ' Tony ', ' CEO ');
INSERT into T1 values (3, ' Merry ', ' VP ');
INSERT into T1 values (4, ' Linda ', ' Operation ');
INSERT into T1 values (5, ' James ', ' Operation ');

Example: Statistics on the number of ' VP ' and above positions and the number of ordinary employees.

The SQL statement is as follows:

Select decode (Job, ' VP ', ' Vp_ceo ', ' CEO ', ' Vp_ceo ', ' operation '), COUNT (*) Job_count from T1 Group by decode (Job, ' VP ', ' Vp_ CEO ', ' CEO ', ' Vp_ceo ', ' operation ');

second, according to the field content sorting;

1. Create the table T2;sql statement as follows:

CREATE TABLE T2 (
ID integer NOT NULL primary key,
Dept_name varchar (10),
region_id integer
);

2. Insert Data:

INSERT into T2 values (1, ' Depta ', 12);
INSERT into T2 values (2, ' Depta ', 10);
INSERT into T2 values (3, ' Depta ', 9);
INSERT into T2 values (4, ' Depta ', 7);
INSERT into T2 values (5, ' DEPTB ', 12);
INSERT into T2 values (6, ' DEPTB ', 13);
INSERT into T2 values (7, ' DEPTB ', 22);
INSERT into T2 values (8, ' DEPTB ', 9);
INSERT into T2 values (9, ' DEPTC ', 8);
INSERT into T2 values (, ' DEPTC ', 10);
INSERT into T2 values (one, ' DEPTC ', 11);

Example 1: Sort by department Dept_name (A->B->C), for each department internally according to Dept Ascending.

SELECT * FROM T2 ORDER by decode (dept_name, ' Depta ', 1, ' DEPTB ', 2, ' DEPTC ', 3), region_id;

Example 2; If Dept_name is Depta, it is arranged in ascending order by ID, otherwise it is arranged in ascending order of region_id.

SELECT * FROM T2 ORDER by decode (dept_name, ' Depta ', id,region_id);