“This is the second day of my participation in the First Challenge 2022.

In my work, I received a request to merge vertical data into a single record display.

In business terms, I have a material with n properties, each of which is configured with a display switch. For n properties of this material, n records are stored in this table. Now we need to group this table according to material code. After grouping, we can extract the properties and configurations of materials, display them as a single record, and draw a general schematic diagram

The reason for this demand is that after a large amount of data is pulled out, the mode reading data on the top will be very unintuitive

We can use Java to process this kind of transformation. After pulling all the data, we can group the data according to the material code and then merge the data. However, this time I chose to handle it in mysql and get the required data directly

Skills used in this SQL:

  1. The left join gets the data from the remaining tables
  2. caseConvert status data into text descriptions, similar to1 -> Enable, 0 -> Disable
  3. The table has multiple layers of nested queries
  4. Table data group query +group_concatJoin the target field into a record
  5. substring_indexIntercepts the string based on the delimiter
  6. replaceReplace string data

Nested table queries start from inside out one by one:

  1. Obtain the basic data, along with the markup into bittext description, temporary table asb
SELECT
        c.mdm_code cCode,
        c.mdm_name cName,
        a.mdm_name aName,
        ( CASE a.mdm_splicing_type WHEN 'B_01' THEN 'Material description display only' WHEN 'B_02' THEN 'Material description and material abbreviation are displayed' ELSE 'Material description and material abbreviation are not 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 
Copy the code

Temporary tables have the following effects:

  1. In the temporary tablebOn the basis ofcodeGrouping passes the data in the following columnsgroup_concatMerge to 1 field. The temporary table obtained by this operation is aliasedctag
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
Copy the code

Now all data can be displayed as a single record according to code, the effect is as follows:

  1. In the temporary tablecOn the basis of, we intercept the target field, and then display the data corresponding to the subscript in different columns

Schematic diagram:

Here we use the combination of REPLACE + SUBSTRING_INDEX to get the data with the corresponding subscript after cutting

SELECT
	cCode 'Tertiary classification code',
	cName 'Tertiary classification name',
	REPLACE(SUBSTRING_INDEX(aNames,' '.1),'_'.' ') Attributes' 1 ',
	REPLACE(SUBSTRING_INDEX(aTypes,' '.1),'_'.' ') 'limit 1',
	REPLACE(SUBSTRING_INDEX(aNames,' '.2),SUBSTRING_INDEX(aNames,' '.1),' ') Attributes' 2 ',
	REPLACE(SUBSTRING_INDEX(aTypes,' '.2),SUBSTRING_INDEX(aTypes,' '.1),' ') 'limit 2',
	REPLACE(SUBSTRING_INDEX(aNames,' '.3),SUBSTRING_INDEX(aNames,' '.2),' ') Attributes' 3 ',
	REPLACE(SUBSTRING_INDEX(aTypes,' '.3),SUBSTRING_INDEX(aTypes,' '.2),' ') 'limit 3'
FROM
        temp_c
Copy the code

The result of SQL execution is as follows:

Perfect problem solving

SUBSTRING_INDEX(STR, delim, count) is a key method for string interception. However, contrary to conventional understanding, the subscript count passed in does not select the subscript character corresponding to STR interception. Count refers to the delimiter matched a specified number of times from left to right. The last match records the subscript position n of that delimiter. The result is the first n characters of STR (excluding the trailing delimiter).

Here’s a graphic example:

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

Original article, without permission, prohibit reprinting

-Leonard: Create by the comfort of salt fish