56,678
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE `tbl07` (
`nid` VARCHAR(20) COLLATE utf8_unicode_ci DEFAULT NULL,
`kemu` VARCHAR(20) COLLATE utf8_unicode_ci DEFAULT NULL,
`chji` INT(11) DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
INSERT INTO tbl07 (nid, kemu, chji) VALUES ('李明', '1', '95');
INSERT INTO tbl07 (nid, kemu, chji) VALUES ('李明', '2', '89');
INSERT INTO tbl07 (nid, kemu, chji) VALUES ('李明', '4', '74');
CREATE TABLE `tbl08` (
`id` INT(11) DEFAULT NULL,
`kemu` VARCHAR(20) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
INSERT INTO tbl08(id, kemu) VALUES ('1', '语文');
INSERT INTO tbl08(id, kemu) VALUES ('2', '数学');
INSERT INTO tbl08(id, kemu) VALUES ('3', '英语');
INSERT INTO tbl08(id, kemu) VALUES ('4', '物理');
INSERT INTO tbl08(id, kemu) VALUES ('5', '化学');
动态
SET @sql := 'SELECT nid';
SELECT @sql := CONCAT(@sql, ', max(case kemu when ', id, ' then chji else 0 end) as ', kemu)
FROM (SELECT DISTINCT id, kemu FROM tbl08) AS a;
SELECT @sqlstr := CONCAT(@sql, ' from tbl07 group by nid');
PREPARE rc FROM @sqlstr;
EXECUTE rc;
SELECT nid,
IFNULL((SELECT MAX(chji) FROM tbl07 WHERE kemu = '语文'), 0) AS '语文',
IFNULL((SELECT MAX(chji) FROM tbl07 WHERE kemu = '数学'), 0) AS '数学',
IFNULL((SELECT MAX(chji) FROM tbl07 WHERE kemu = '英语'), 0) AS '英语',
IFNULL((SELECT MAX(chji) FROM tbl07 WHERE kemu = '物理'), 0) AS '物理'
FROM tbl07 GROUP BY nid