5.mysql-应用

5.1.行转列

CREATE TABLE `table1` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `year` varchar(50) DEFAULT NULL,
  `month` varchar(50) DEFAULT NULL,
  `amount` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1

SELECT
    `year`,
    MAX(
        CASE
        WHEN MONTH = '1' THEN
            amount
        END
    ) AS '一月',
    MAX(
        CASE
        WHEN MONTH = '2' THEN
            amount
        END
    ) AS '二月',
    MAX(
        CASE
        WHEN MONTH = '3' THEN
            amount
        END
    ) AS '三月',
    MAX(
        CASE
        WHEN MONTH = '4' THEN
            amount
        END
    ) AS '四月'
FROM
    table1
GROUP BY
    `year`;

5.2列转行

CREATE TABLE `table2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(50) DEFAULT NULL,
  `file_url` varchar(50) DEFAULT NULL,
  `file_name` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

SELECT 
    user_id,
    GROUP_CONCAT(file_name,':',file_url) AS file
FROM
    table2 GROUP BY user_id