mysql左关联和分组

-- databasekk.project definition

CREATE TABLE `project` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '项目名称',
  `status` varchar(100) DEFAULT NULL COMMENT '状态',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


INSERT INTO databasekk.project (name,status) VALUES
	 ('项目1','zaijian'),
	 ('项目2','wangong'),
	 ('项目3','zaijian'),
	 ('项目4','zaijian'),
	 ('项目5','tinggong'),
	 ('项目6','zaijian'),
	 ('项目7','zaijian'),
	 ('项目8','');
         
         
-- databasekk.project_status definition

CREATE TABLE `project_status` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL COMMENT '状态名称',
  `code` varchar(100) DEFAULT NULL COMMENT '状态编码',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


INSERT INTO databasekk.project_status (name,code) VALUES
	 ('停工','tinggong'),
	 ('在建','zaijian'),
	 ('完工','wangong'),
	 ('终止','stp');
         

         
-- databasekk.schedule definition

CREATE TABLE `schedule` (
  `id` int NOT NULL AUTO_INCREMENT,
  `subject` varchar(100) DEFAULT NULL COMMENT '学科',
  `credit` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


INSERT INTO databasekk.schedule (subject,credit) VALUES
	 ('高数',NULL),
	 ('英语',NULL),
	 ('语文',NULL),
	 ('操作系统',NULL);
         
         

-- databasekk.student definition

CREATE TABLE `student` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL COMMENT '姓名',
  `gender` varchar(100) DEFAULT NULL COMMENT '性别',
  `phone` varchar(100) DEFAULT NULL COMMENT '手机号码',
  `addr` varchar(100) DEFAULT NULL COMMENT '地址',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


INSERT INTO databasekk.student (name,gender,phone,addr) VALUES
	 ('张三','男','18511451456','北京海淀区大牛坊'),
	 ('李四','男','18511451456','北京海淀区大牛坊'),
	 ('王五','男','12311451456','北京海淀区大牛坊'),
	 ('赵柳','女','18111451456','北京海淀区大牛坊'),
	 ('赵云','男','18511451436','北京海淀区大牛坊'),
	 ('刘备','男','14561451456','北京海淀区大牛坊');
         
         
-- databasekk.transcript definition

CREATE TABLE `transcript` (
  `id` int NOT NULL AUTO_INCREMENT,
  `student_id` int DEFAULT NULL COMMENT '学生id',
  `schedule_id` int DEFAULT NULL COMMENT '学科id',
  `score` float DEFAULT NULL COMMENT '分数',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO databasekk.transcript (student_id,schedule_id,score) VALUES
	 (1,1,50.0),
	 (1,2,20.0),
	 (1,3,30.0),
	 (1,4,20.0),
	 (2,1,40.0),
	 (2,2,50.0),
	 (2,3,90.0),
	 (2,4,10.0),
	 (3,1,70.0),
	 (3,2,10.0);
INSERT INTO databasekk.transcript (student_id,schedule_id,score) VALUES
	 (3,3,80.0),
	 (3,4,10.0);

-- 一对多左关联
SELECT  p.id, p.name, ps.name FROM  project p  left join project_status ps  on  p.status  = ps.code 

-- 一对多 分组
SELECT   ps.name, COUNT(1) FROM  project p  left join project_status ps  on  p.status  = ps.code GROUP BY   ps.name

-- 多对多 左关联
SELECT  s.name , s2.subject , t.score  FROM transcript t left join student s on t.student_id  = s.id  LEFT  JOIN schedule s2 on t.schedule_id  = s2.id 
-- 多对多 分组
SELECT  s.name ,  SUM(t.score) 总分 FROM transcript t left join student s on t.student_id  = s.id  LEFT  JOIN schedule s2 on t.schedule_id  = s2.id  GROUP by s.name