SQL 片段.sql

CREATE TABLE `a` (
  `id` INT(100) UNSIGNED NOT NULL AUTO_INCREMENT,
  `userid` VARCHAR(11) DEFAULT NULL,
  `subject` VARCHAR(11) DEFAULT NULL,
  `score` INT(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

INSERT INTO `a` (`id`, `userid`, `subject`, `score`)
VALUES
	(1, '001', '语文', 98),
	(2, '001', '数学', 96),
	(4, '001', '英语', 78),
	(5, '002', '英语', 78),
	(6, '002', '数学', 68),
	(7, '002', '语文', 73),
	(8, '003', '英语', 76),
	(9, '003', '数学', 50),
	(10, '003', '语文', 74);


CREATE VIEW a_view AS(
	SELECT a.*,  
	CASE WHEN SUBJECT='语文' THEN score END AS '语文',
	CASE WHEN SUBJECT='数学' THEN score END AS '数学',
	CASE WHEN SUBJECT='英语' THEN score END AS '英语',
	CASE WHEN SUBJECT='政治' THEN score ELSE 0 END AS '政治' FROM a
);					

SELECT 
userid, 
max(语文) AS 语文,  
max(数学) AS 数学,
max(英语) AS 英语,
max(政治) AS 政治
FROM a_view GROUP BY userid;