一个简单功能的SQL 实现

-- 创建cj表sql
CREATE TABLE `cj` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `subject` varchar(20) DEFAULT NULL,
  `result` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

-- 插入数据SQL
INSERT INTO cj
   (`id`, `name`, `subject`, `result`)
VALUES
   (1, ‘张三‘, ‘语文‘, 80);

INSERT INTO cj
   (`id`, `name`, `subject`, `result`)
VALUES
   (2, ‘张三‘, ‘数学‘, 90);

INSERT INTO cj
   (`id`, `name`, `subject`, `result`)
VALUES
   (3, ‘张三‘, ‘物理‘, 85);

INSERT INTO cj
   (`id`, `name`, `subject`, `result`)
VALUES
   (4, ‘李四‘, ‘语文‘, 85);

INSERT INTO cj
   (`id`, `name`, `subject`, `result`)
VALUES
   (5, ‘李四‘, ‘数学‘, 92);

INSERT INTO cj
   (`id`, `name`, `subject`, `result`)
VALUES
   (6, ‘李四‘, ‘物理‘, 89);


-- 查询SQL
select 
    distinct a.name 姓名,
    (select result from cj where name = a.name and subject = ‘语文‘) 语文,
    (select result from cj where name = a.name and subject = ‘数学‘) 数学,
    (select result from cj where name = a.name and subject = ‘物理‘) 物理   
from cj a;

一个简单功能的SQL 实现

标签：数学   har   rom   log   incr   not   engine   sub   char