Shulou(Shulou.com)05/31 Report--

This article mainly introduces myql how to achieve row-to-column statistical query, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

1 original data

-- Table structure for `troombmforth repeat _ roomase`

DROP TABLE IF EXISTS `troombmforth repeat _ roomase`

CREATE TABLE `troombmforth repeat _ roomase` (

`months` int (2) DEFAULT NULL COMMENT 'month'

`total`bigint (21) NOT NULL DEFAULT'0' COMMENT 'query the number of purchased users after the next month or a few months corresponding to the month'

`seq`bigint (20) DEFAULT NULL COMMENT 'serial number'

The month after which the field `next_ months` bigint (4) DEFAULT NULL COMMENT 'months corresponds, after January, after February, after March.

) ENGINE=InnoDB DEFAULT CHARSET=utf8

-- Records of t_bm_repeat_purchase

INSERT INTO `troombmforth repeat _ roomase` VALUES ('1percent,' 1170percent, '2percent,' 2')

INSERT INTO `troombmforth repeat _ roomase` VALUES ('1percent,' 2144percent, '2percent,' 3')

INSERT INTO `troombmforth repeat _ roomase` VALUES ('1x, '1012m,' 2pm,'4')

INSERT INTO `troombmforth repeat _ roomase` VALUES ('1percent,' 873 percent, '2percent,' 5')

INSERT INTO `troombmforth repeat _ roomase` VALUES ('1percent,' 785 percent, '2percent,' 6')

INSERT INTO `troombmforth repeat _ roomase` VALUES ('1x, '1008pm,' 2pm,'7')

INSERT INTO `troombmforth repeat _ roomase` VALUES ('1percent,' 773 percent, '2percent,' 8')

INSERT INTO `troombmforth repeat _ roomase` VALUES ('2percent,' 1446percent, '2percent,' 3')

INSERT INTO `troombmforth repeat _ roomase` VALUES ('2percent,' 700percent, '2percent,' 4')

INSERT INTO `troombmforth repeat _ roomase` VALUES ('2percent,' 665percent, '2percent,' 5')

INSERT INTO `troombmforth repeat _ roomase` VALUES ('2percent,' 533percent, '2percent,' 6')

INSERT INTO `troombmforth repeat _ roomase` VALUES ('2percent,' 694 percent, '2percent,' 7')

INSERT INTO `troombmforth repeat _ roomase` VALUES ('2percent,' 551 percent, '2percent,' 8')

INSERT INTO `troombmforth repeat _ roomase` VALUES ('3percent,' 1530seconds, '2cycles,' 4')

INSERT INTO `troombmforth repeat _ roomase` VALUES ('3percent,' 1273percent, '2percent,' 5')

INSERT INTO `troombmforth repeat _ roomase` VALUES ('3percent,' 1062percent, '2percent,' 6')

INSERT INTO `troombmforth repeat _ roomase` VALUES ('3percent,' 1367percent, '2percent,' 7')

INSERT INTO `troombmforth repeat _ roomase` VALUES ('3percent,' 1044percent, '2percent,' 8')

INSERT INTO `troombmforth repeat _ roomase` VALUES ('4percent,' 1035percent, '2percent,' 5')

INSERT INTO `troombmforth repeat _ roomase` VALUES ('4percent,' 775grams, '2cycles,' 6')

INSERT INTO `troombmforth repeat _ roomase` VALUES ('4percent,' 949percent, '2percent,' 7')

INSERT INTO `troombmforth repeat _ roomase` VALUES ('4percent,' 790seconds, '2cycles,' 8')

INSERT INTO `troombmforth repeat _ roomase` VALUES ('5percent,' 939percent, '2percent,' 6')

INSERT INTO `troombmforth repeat _ roomase` VALUES ('5percent,' 1304 seconds, '2cycles,' 7')

INSERT INTO `troombmforth repeat _ roomase` VALUES ('5percent,' 1066percent, '2percent,' 8')

INSERT INTO `troombmforth repeat _ roomase` VALUES ('6percent,' 1110percent, '2percent,' 7')

INSERT INTO `troombmforth repeat _ roomase` VALUES ('6x, '899m,' 2pm,'8')

INSERT INTO `troombmforth repeat _ roomase` VALUES ('7percent,' 1589percent, '2percent,' 8')

To become

2 use dynamic query:

SET @ EE=''

Set @ str_tmp=''

SELECT @ EE:=CONCAT (@ EE,'SUM (IF (next_months=\', next_months,'\',', total,null)) AS ", next_months,'",') as aa into @ str_tmp

FROM (SELECT DISTINCT next_months FROM t_bm_repeat_purchase) An order by length (aa) desc limit 1

SET @ QQ=CONCAT ('SELECT tweebmless repeatable recording ase.months'), 'FROM t_bm_repeat_purchase GROUP BY months').

PREPARE stmt FROM @ QQ

EXECUTE stmt

Deallocate prepare stmt

Dynamic query results: this is not what we want in the end, abandon this query method, because the previous empty data, but also translate the latter data to the left as a whole.

3. Use static query

SELECT t.months

IF (0 > num,NULL,SUBSTRING_INDEX (total,',', 1)) AS'1'

IF (1 > num,NULL,SUBSTRING_INDEX (SUBSTRING_INDEX (total,',', 2),',',-1) AS '2numbers,-- this is the first number to be calculated.

IF (2 > num,NULL,SUBSTRING_INDEX (SUBSTRING_INDEX (total,',', 3),',',-1) AS '3percent,-- take the second number

IF (3 > num,NULL,SUBSTRING_INDEX (SUBSTRING_INDEX (total,',', 4),',',-1) AS '4numbers,-- take the third number

IF (4 > num,NULL,SUBSTRING_INDEX (SUBSTRING_INDEX (total,', 5),',',-1)) AS'5'

IF (5 > num,NULL,SUBSTRING_INDEX (SUBSTRING_INDEX (total,', 6),',',-1) AS'6'

IF (6 > num,NULL,SUBSTRING_INDEX (SUBSTRING_INDEX (total,', 7),',',-1)) AS'7'

IF (7 > num,NULL, SUBSTRING_INDEX (SUBSTRING_INDEX (total,', 8),',',-1) AS'8'

IF (8 > num,NULL, SUBSTRING_INDEX (SUBSTRING_INDEX (total,',', 9),',',-1) AS'9'

IF (9 > num,NULL, SUBSTRING_INDEX (SUBSTRING_INDEX (total,',', 10),',',-1) AS '10'

IF (10 > num,NULL, SUBSTRING_INDEX (SUBSTRING_INDEX (total,',', 11),',',-1) AS '11'

FROM

(SELECT a.months

CHAR_LENGTH (GROUP_CONCAT (a.total))-CHAR_LENGTH (replace (GROUP_CONCAT (a.total),'') as num, this is how many commas there are each month.

GROUP_CONCAT (a.total ORDER BY a.next_months) AS total

FROM t_bm_repeat_purchase a

GROUP BY a.months) t

SELECT a.months

CHAR_LENGTH (GROUP_CONCAT (a.total))-CHAR_LENGTH (replace (GROUP_CONCAT (a.total),'') as num, this is how many commas there are each month.

GROUP_CONCAT (a.total ORDER BY a.next_months) AS total

FROM t_bm_repeat_purchase a

Query the result under the statement GROUP BY a.months:

Optimize it

SELECT t.months

IF (num > = 1) AS'1' (total,',', 1) AS'1'

IF (num > = 2 NULL SUBSTRING index (SUBSTRING_INDEX (total,',', 2),',',-1), AS'2'

IF (num > = 3 NULL SUBSTRING index (SUBSTRING_INDEX (total,',', 3),',',-1), AS'3'

IF (num > = 4BSTRING index (SUBSTRING_INDEX (total,',', 4),',',-1), NULL) AS'4'

IF (num > = 5jue SUBSTRING index (SUBSTRING_INDEX (total,',', 5),',',-1), NULL) AS'5'

IF (num > = 6 NULL SUBSTRING index (SUBSTRING_INDEX (total,',', 6),',',-1), AS'6'

IF (num > = 7 SUBSTRING_INDEX (total,',', 7),',',-1), NULL) AS'7'

IF (num > = 8 NULL SUBSTRING index (SUBSTRING_INDEX (total,',', 8),',',-1), AS'8'

IF (num > = 9 SUBSTRING_INDEX (total,',', 9),',',-1), NULL) AS'9'

IF (num > = 10) SUBSTRING_INDEX (total,',', 10),',',-1), NULL) AS '10'

IF (num > = 11 NULL SUBSTRING index (SUBSTRING_INDEX (total,',', 11),',',-1), AS '11'

FROM

(SELECT a.months

COUNT (*) as num,-- how many data are there each month?

GROUP_CONCAT (a.total ORDER BY a.next_months) AS total

FROM t_bm_repeat_purchase a

GROUP BY a.months) t

4 advantages and disadvantages of dynamic query and static query

Dynamically, I have not been able to achieve the final result at present, and it is not convenient to make an insert table, but it can be unlimited, that is, it is suitable for unlimited data.

Static words are suitable for those whose cardinality is not large, such as 12 months or 6 months, which can be enumerated more quickly, but it is not convenient for those with large cardinality, but it is convenient to make insert tables, and static ones can now meet the requirements, so I am adopting a dynamic approach at present.

Thank you for reading this article carefully. I hope the article "how to realize row swap statistical query in myql" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.