最近准备面试题,想之前面试过一家公司时遇到一个SQL题,横向展示一个表中数据,当时没写出现写下总结,以免忘记。
-- ----------------------------
-- Table structure for `t_subject`
-- ----------------------------
DROP TABLE IF EXISTS `t_subject`;
CREATE TABLE `t_subject` (
`id` int(11) NOT NULL DEFAULT '0',
`name` varchar(255) DEFAULT NULL,
`subject` varchar(255) DEFAULT NULL,
`results` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of t_subject
-- ----------------------------
INSERT INTO `t_subject` VALUES ('1', '小明', '语文', '50');
INSERT INTO `t_subject` VALUES ('2', '小明', '数学', '20');
INSERT INTO `t_subject` VALUES ('3', '小乐', '英语', '122');
INSERT INTO `t_subject` VALUES ('4', '小乐', '语文', '500');
INSERT INTO `t_subject` VALUES ('5', '小二', '数学', '100');
有一张表,表中包含一个三个人有三个科目。
SELECT
t.name ,
SUM(
IF (
t.subject = '语文',
t.results,
0
)
) AS '语文',
SUM(
IF (
t.subject = '数学',
t.results,
0
)
) as '数学',
SUM(
IF (
t.subject = '英语',
t.results,
0
)
) as '英语'
FROM
t_subject t
GROUP BY (t.name);
执行结果如下:
当时很好奇为什么需要加SUM函数进行求和,去掉之后执行SQL如下
初步分析如果不加此函数SQL执行默认只会对进行分组后的数据进行统计
SELECT
*
FROM
t_subject t
GROUP BY t.name;
-- ----------------------------
-- Table structure for `code_a`
-- ----------------------------
DROP TABLE IF EXISTS `code_a`;
CREATE TABLE `code_a` (
`id` int(2) NOT NULL AUTO_INCREMENT,
`time` date DEFAULT NULL,
`code` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of code_a
-- ----------------------------
INSERT INTO `code_a` VALUES ('1', '2015-03-24', '胜');
INSERT INTO `code_a` VALUES ('2', '2015-03-24', '胜');
INSERT INTO `code_a` VALUES ('3', '2015-03-24', '负');
INSERT INTO `code_a` VALUES ('4', '2015-03-23', '胜');
INSERT INTO `code_a` VALUES ('5', '2015-03-23', '负');
INSERT INTO `code_a` VALUES ('6', '2015-03-26', '负');
SELECT
t.time ,
SUM(
IF (
t.code = '胜',
1,
0
)
) AS '胜',
SUM(
IF (
t.code = '负',
1,
0
)
) as '负'
FROM
code_a t
GROUP BY (t.time);