sql中的行转列

来源：互联网发布：数据导出到excel 编辑：程序博客网时间：2024/05/17 03:32

/*
Navicat MySQL Data Transfer

Source Server : localhost
Source Server Version : 50625
Source Host : localhost:3306
Source Database : test

Target Server Type : MYSQL
Target Server Version : 50625
File Encoding : 65001

Date: 2015-07-14 00:59:46
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for aticle
-- ----------------------------
DROP TABLE IF EXISTS `aticle`;
CREATE TABLE `aticle` (
`id` int(11) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`typeName` varchar(255) DEFAULT NULL,
`content` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of aticle
-- ----------------------------
INSERT INTO `aticle` VALUES ('1', '老人与海', '散文', '阿迪嘎然');
INSERT INTO `aticle` VALUES ('2', '盗墓笔记', '小说', '阿嘎如果');
INSERT INTO `aticle` VALUES ('3', '春', '散文', '了可能会尽快');
INSERT INTO `aticle` VALUES ('4', '静夜思', '诗歌', '创奇那名也啊');
INSERT INTO `aticle` VALUES ('5', '西游记', '小说', '阿宝色哈尔啊啊');
INSERT INTO `aticle` VALUES ('6', '致富', '微博', '色弱各色如何');
INSERT INTO `aticle` VALUES ('7', '我爱你', '说说', '阿瓦尔噶人噶 ');
INSERT INTO `aticle` VALUES ('8', '世界五百强', '微博', '啊哇嘎任何');
INSERT INTO `aticle` VALUES ('9', '爱你到永远', '说说', '阿瓦尔噶人噶');
INSERT INTO `aticle` VALUES ('10', '真的爱你', '诗歌', '啊娃娃儿');

-- ----------------------------
-- Table structure for people
-- ----------------------------
DROP TABLE IF EXISTS `people`;
CREATE TABLE `people` (
`id` int(11) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`aticleId` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of people
-- ----------------------------
INSERT INTO `people` VALUES ('1', '赵森', '1');
INSERT INTO `people` VALUES ('2', '赵森', '2');
INSERT INTO `people` VALUES ('3', '张敏', '3');
INSERT INTO `people` VALUES ('4', '刘瑶', '4');
INSERT INTO `people` VALUES ('5', '赵俊淇', '6');
INSERT INTO `people` VALUES ('6', '赵森', '5');
INSERT INTO `people` VALUES ('7', '张敏', '7');
INSERT INTO `people` VALUES ('8', '苏萌萌', '8');
INSERT INTO `people` VALUES ('9', '张敏', '9');
INSERT INTO `people` VALUES ('10', '刘瑶', '10');

sql语句如下：

SELECT t4.name,

max(case when t4.typeName='微博' then t4.typeNumber else 0 end)'微博Num',
max(case when t4.typeName='小说' then t4.typeNumber else 0 end)'小说Num',
max(case when t4.typeName='说说' then t4.typeNumber else 0 end)'说说Num',
max(case when t4.typeName='诗歌' then t4.typeNumber else 0 end)'诗歌Num',
max(case when t4.typeName='散文' then t4.typeNumber else 0 end)'散文Num'
from
(select t3.name,t3.typeName,count(typeName) as typeNumber from
(select t1.id,t1.name,t2.name as aticleName,t2.typeName,
t2.content from people t1,aticle t2 where t1.aticleId=t2.id) t3
GROUP BY t3.typeName,t3.name) t4

GROUP BY name;

效果图如下:

0 0