Mysql column-to-row and column-to-row

Mysql column-to-row and column-to-row

Today, I saw a row-to-column and column-to-row problem when I went around Java. After reading this, I really don't know how to do it. I may usually use hibernate as the persistent layer, the SQL statement won't be written either. Hurry up and chat with du Niang, and then create a table for a test. Now, I haven't forgotten to improve and summarize it, and I forgot to go back to the province.

 

Create a new table:

DROP TABLE IF EXISTS `test1`;CREATE TABLE `test1` (  `Id` int(11) NOT NULL AUTO_INCREMENT,  `ye` varchar(20) DEFAULT NULL,  `me` varchar(20) DEFAULT NULL,  `Scount` double DEFAULT NULL,  PRIMARY KEY (`Id`)) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

After several data entries are inserted, the effect is as follows:

What is the final effect? As follows:

 

The result is as shown above. How can this problem be achieved? The SQL statement is as follows:

SELECT ye,(MAX(CASE me WHEN '1' THEN Scount ELSE 0 END)) AS M1,(MAX(CASE me WHEN '2' THEN Scount ELSE 0 END)) AS M2,(MAX(CASE me WHEN '3' THEN Scount ELSE 0 END)) AS M3,(MAX(CASE me WHEN '4' THEN Scount ELSE 0 END)) AS M4FROM test1 GROUP BY ye;

I wrote this for the time being. There should be other methods, which should be further explained;

1. case me when '1' then Scount else 0 end is actually a condition judgment, equivalent to if (me = '1') {Scount} else {0 }, therefore, we can also use the IF expression in SQL to replace this expression. How can we use the if expression to write it? See the following:

SELECT ye,(MAX(IF(me='1',Scount,0))) AS M1,(MAX(IF(me='2',Scount,0))) AS M2,(MAX(IF(me='3',Scount,0))) AS M3,(MAX(IF(me='4',Scount,0))) AS M4FROM test1 GROUP BY ye;

In the if (expr1, expr2, expr3) expression, if the expr1 expression is true, the result is expr2. if the expr1 expression is false, the result is expr3;

2. When you see this function group_concat () during column-to-row reading, you can record it as fun:

SELECT ye, GROUP_CONCAT (me, 'quantity: ', Scount) as mfrom test1 group by ye;

The result is as follows:

In fact, three fields are combined, the specific usage of this function is group_concat ([DISTINCT] the field to be connected [Order by asc/DESC sorting field] [Separator 'delimiter']). you can also sort fields and separate data with specific symbols;

Speaking of group_concat, let's review the related concat (). concat (str1, str2, str3 .....) connect the following str, as shown in the following example:

SELECT CONCAT("aa","bb","cc") AS result;

The query result is as follows:

However, str cannot make null. If it is null, the returned result is null. I will not write this example.

Next is concat_ws (Separator, str1, str2 .....), this function uses the first separator to connect the following str. This separator can be used for symbols or strings. The following is an example:

We can see that the three strings are connected using _ 888 _. In this function, Separator can be a NULL string, but cannot be NULL. Otherwise, the result is Null, and str can be NULL.

 

For column-to-row queries, use the concat method. First, modify the data table. The table structure remains unchanged. Modify the data as follows:

 

The SQL statement is as follows:

SELECT t. ye, CONCAT ('sum of 1 ', CAST (SUM (CASE t. me WHEN '1' THEN t. scount ELSE 0 END) as char) AS M1, CONCAT ('sum of 2 ', CAST (SUM (CASE t. me WHEN '2' THEN t. scount ELSE 0 END) as char) AS m2FROM test1 tGROUP BY t. ye;

The query result is as follows:

 

Okay. This is the case for Row-to-column conversion. Goodbye next time.