1. The tables of database can be divided into two categories: vertical table and horizontal table

Vertical table: the fields and field values in the table are in the form of key value, that is, two fields are defined in the table, one of which stores the field name and the other stores the value of the field represented by the field name.

For example, the following project_audit_log table, where date_ The type field indicates why the time type is, followed by the date_value represents the value of this time

Horizontal table: all fields are defined in the table structure. If you put the date in the above table_ Type and date_ The value field is converted to shelve_date,offShelve_date,

advance_finish_date is a horizontal table.

Advantages and disadvantages: the table structure of the horizontal table is clearer, and some sql statements of the associated query are easier for subsequent developers to take over. However, if there are not enough fields and new fields are needed, the table structure will be changed.

The vertical table has higher expansibility. If you want to add a field, you don't need to change the table structure, but some association queries will be more troublesome and inconvenient for maintenance and follow-up personnel.

In normal development, try to use horizontal tables instead of vertical tables. The maintenance cost is relatively high, and some association queries are troublesome.

2. How to convert a vertical table to a horizontal table

(1) First, we take the field names and the values of the corresponding fields from the vertical table

select b.project_id,b.version,
(case b.date_type when 'shelveDate' then b.date_value else '' end )shelveDate,
(case b.date_type when 'offShelveDate' then b.date_value else '' end)offShelveDate,
(case b.date_type when 'advanceFinishDate' then b.date_value else '' end) advanceFinishDate
from project_audit_log b;

result:

Through the case statement, the fields are successfully taken out from the vertical table, but it is still not a horizontal table. We need to take the same project_ The rows of ID and version are merged (these two fields together ensure the uniqueness of data in the table).

Note: you need to take out each field and case it. The number of fields requires the number of case statements. Because a case statement will not be executed after meeting the qualified when statement.

(2) Group and merge the same rows to generate a horizontal table

select b.project_id,b.version,
max(case b.date_type when 'shelveDate' then b.date_value else '' end )shelveDate,
max(case b.date_type when 'offShelveDate' then b.date_value else '' end)offShelveDate,
max(case b.date_type when 'advanceFinishDate' then b.date_value else '' end) advanceFinishDate
from project_audit_log b group by b.project_id,b.version;

Note: when using group by grouping here, you need to add the max function to the field. When group by is used for grouping, it is generally used in combination with aggregate functions. Common aggregate functions:

• AVG() average
• COUNT() counts the total number of columns
• MAX() maximum
• MIN() minimum
• Sum

If the max aggregate function is not used here, the field value that appears for the first time will be displayed from the group, and the sql after removing the max function:

select b.project_id,b.version,
(case b.date_type when 'shelveDate' then b.date_value else '' end )shelveDate,
(case b.date_type when 'offShelveDate' then b.date_value else '' end)offShelveDate,
(case b.date_type when 'advanceFinishDate' then b.date_value else '' end) advanceFinishDate
from project_audit_log b group by b.project_id,b.version;

Query results:

(3) After the vertical table becomes a horizontal table, you can easily perform association query and condition query on the vertical table fields. For example, there is another table project_info, and project_audit_log, and find out that the shelveDate time is greater than 16:23:59 on August 3, 2018 and the offShelveDate is less than 15:12:22 on August 6, 2018

select a.id,a.version,a.project_name,e.shelveDate,e.offShelveDate,e.advanceFinishDate
from project_info a left join 
(select b.project_id,b.version,
max(case b.date_type when 'shelveDate' then b.date_value else '' end )shelveDate,
max(case b.date_type when 'offShelveDate' then b.date_value else '' end)offShelveDate,
max(case b.date_type when 'advanceFinishDate' then b.date_value else '' end) advanceFinishDate
from project_audit_log b group by b.project_id,b.version) e on a.id = e.project_id and a.version = e.version where e.shelveDate >= '2018-08-03 16:23:59' and e.offShelveDate <= '2018-08-06 15:12:22';

(4) Adopt GROUP_CONCAT, merge fields to form a horizontal table

select a.project_id,a.version,GROUP_CONCAT(a.date_type,a.date_value) date from project_audit_log a  GROUP BY a.project_id,a.version;

Then perform the associated query with other tables, but this method is also inconvenient for the field condition query in the vertical table. It is also inconvenient if we want to filter out the shelveDate and offShelveDate or advanceFinishDate conditions. It is more suitable for not performing the conditional query on the vertical table fields, but only associating with other tables to query the vertical table information.