Mysql 如何将查询结果分组并行展示？

Question

假设表结构是

name	start_date	num
ceshi	2021-11-22	1
sss	2021-11-21	2
ceshi	2021-01-20	3

根据name分组和start_date分组上半年下半年，并且一个name一行展示上半年和下半年的统计num和，如何实现以下查询结果呢？

name	start_date	shang_sum	xia_sum
ceshi	上半年	3	1
sss	下半年	0	2

我尝试用如下sql实现，但是还是达不到预期

SELECT 
    `name`,
    start_date,
    IF(MONTH(start_date) >= 7,'下半年','上半年') as cycle,
    IF(IF(MONTH(start_date) >= 7,'下半年','上半年') = '上半年',SUM(num),0) as shang_sum,
    IF(IF(MONTH(start_date) >= 7,'下半年','上半年') = '下半年',SUM(num),0) as xia_sum
FROM 
    `task` 
GROUP BY `name`,cycle

表结构sql如下：

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for task
-- ----------------------------
DROP TABLE IF EXISTS `task`;
CREATE TABLE `task`  (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `project_id` int(11) NULL DEFAULT NULL,
  `name` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  `start_date` date NULL DEFAULT NULL,
  `end_date` date NULL DEFAULT NULL,
  `num` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 4 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of task
-- ----------------------------
INSERT INTO `task` VALUES (1, 111, 'ceshi', '2021-11-22', NULL, 1);
INSERT INTO `task` VALUES (2, 111, 'sss', '2021-11-21', NULL, 2);
INSERT INTO `task` VALUES (3, 222, 'ceshi', '2021-01-20', NULL, 3);

SET FOREIGN_KEY_CHECKS = 1;

求大佬给点儿思路

Answer 1

SELECT
    t.name,
    start_date,
    SUM( t.num ) AS shang_sum,
    IFNULL(t2.xia_sum,0)
FROM
    `task` t
    LEFT JOIN ( SELECT NAME, SUM( num ) xia_sum FROM task WHERE MONTH ( start_date ) < 7 GROUP BY `name` ) t2 ON t2.NAME = t.NAME 
WHERE
    MONTH ( start_date ) >= 7 
GROUP BY
    `name`