当前位置：网站首页>[MySQL project practical optimization] convert multiple rows of data into the same row and multiple columns for display

[MySQL project practical optimization] convert multiple rows of data into the same row and multiple columns for display

2022-07-18 19:55:00 【InfoQ】

In my work , Received such a demand , The data that needs to be merged vertically is 1 Records are displayed .

Business scenario

In business terms , I have 1 Individual materials , Among them is n Attributes , Each attribute is configured with a switch whether to display , For this material n Attributes , Namely n Records are stored in this table ; Now you need to group this table by material code , Data after grouping , Extract the property and configuration of the material , Display as a single record , Draw a general diagram

The reason for this demand is that after a large amount of data is pulled out , Reading data in the above mode will be very intuitive

The operation of this transformation , We can use java To deal with it , After pulling all the data , Then group according to the material code and merge the data ; but , This time I choose to mysql Deal with , Get the data you need directly

Skills used in business

The left connection gets the data in the remaining tables

case
Convert status data into text descriptions , Be similar to
1 -> Enable , 0 -> Ban

Table multi-level nested query

Table data grouping query +
group_concat
The connection target field is merged into 1 In records

substring_index
, Intercept string according to separator

replace
Replace string data

From inside to outside, query and process as a temporary table

Prepare basic data for joint table query

In this step , Get all the basic data , At the same time, the tag is converted into a bit text description , Mark the temporary table obtained after the operation as

b

SELECT
 c.mdm_code cCode,
 c.mdm_name cName,
 a.mdm_name aName,
 ( CASE a.mdm_splicing_type WHEN 'B_01' THEN ' Only the material description is displayed ' WHEN 'B_02' THEN ' Both material description and material abbreviation are displayed ' ELSE ' Neither material description nor material abbreviation is displayed ' END ) aType,
 a.mdm_required aRequired ,
 ap.mdm_des apDes,
 np.mdm_des npDes,
 sp.mdm_des spDes
FROM
 attribute_1 a
 LEFT JOIN mdm_classification c ON a.mdm_classifi_note_id = c.id 
 LEFT JOIN mdm_abbr_spl ap on ap.mdm_product_code2 = left(c.mdm_code,4)
 LEFT JOIN mdm_note_spl np on np.mdm_product_code2 = left(c.mdm_code,4)
 LEFT JOIN mdm_spemod_spl sp on sp.mdm_product_code3 = c.mdm_code
WHERE
 c.mdm_code IS NOT NULL 
) b

The effect of the temporary table is as follows ：

A temporary table to code Group and merge data

In the temporary table

b

On the basis of , according to

code

Group the data in the following columns through

group_concat

Merge into 1 On a field

The temporary table obtained by this operation is marked

c

SELECT
 cCode,
 cName,
 CONCAT('_',GROUP_CONCAT( aName SEPARATOR ' ' )) aNames,
 CONCAT('_',GROUP_CONCAT( aType SEPARATOR ' ' )) aTypes,
 GROUP_CONCAT( apDes SEPARATOR ' ' ) apDes,
 GROUP_CONCAT( npDes SEPARATOR ' ' ) npDes,
 GROUP_CONCAT( spDes SEPARATOR ' ' ) spDes
FROM
 temp_b

Now all the data can be in accordance with

code

Display as a single record , The effect is as follows ：

Split data into multiple columns

In the temporary table

c

On the basis of , We intercept the target field , Each column is part of the content intercepted from the target field

Intercepted sketch ：

It's here

REPLACE + SUBSTRING_INDEX

The data corresponding to the subscript after cutting is obtained through combination processing

SELECT
 cCode ' Three level classification code ',
 cName ' The name of the third level classification ',
 REPLACE(SUBSTRING_INDEX(aNames,' ',1),'_','') ' attribute 1',
 REPLACE(SUBSTRING_INDEX(aTypes,' ',1),'_','') ' Limit 1',
 REPLACE(SUBSTRING_INDEX(aNames,' ',2),SUBSTRING_INDEX(aNames,' ',1),'') ' attribute 2',
 REPLACE(SUBSTRING_INDEX(aTypes,' ',2),SUBSTRING_INDEX(aTypes,' ',1),'') ' Limit 2',
 REPLACE(SUBSTRING_INDEX(aNames,' ',3),SUBSTRING_INDEX(aNames,' ',2),'') ' attribute 3',
 REPLACE(SUBSTRING_INDEX(aTypes,' ',3),SUBSTRING_INDEX(aTypes,' ',2),'') ' Limit 3'
FROM
 temp_c

sql The effect is as follows ：

Solve the problem perfectly

SUBSTRING_INDEX function

In the process of dealing with this problem , A core method is used

SUBSTRING_INDEX(str, delim, count)

, The function is string interception

But it is different from the conventional understanding , Incoming subscript

count

Not selection

str

The corresponding subscript character after interception ; It's understandable ,

count

It refers to the separator that matches the specified number of times from left to right

delim

, At the last match, the subscript position of the string corresponding to the separator will be recorded

n

, The final result is

str

Before

n

Characters （ Do not include the delimiter at the end ）

Let's take a vivid example ：

str = aa_bb_cc_dd
substring_index(str, '_', 1) = aa
substring_index(str, '_', 2) = aa_bb
substring_index(str, '_', 3) = aa_bb_cc

原网站

版权声明
本文为[InfoQ]所创，转载请带上原文链接，感谢
https://yzsam.com/2022/199/202207161640169263.html