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;

WITH ProjectGroups AS (
    SELECT 
        Task_ID,
        Start_Date,
        End_Date,
        -- Identify groups of consecutive tasks using the difference between 
        -- row number and End_Date to find gaps
        DATE_SUB(End_Date, INTERVAL ROW_NUMBER() OVER (ORDER BY End_Date) DAY) AS group_id
    FROM Projects
),
ProjectSummary AS (
    SELECT 
        MIN(Start_Date) as project_start_date,
        MAX(End_Date) as project_end_date,
        DATEDIFF(MAX(End_Date), MIN(Start_Date)) AS completion_days
    FROM ProjectGroups
    GROUP BY group_id
)
SELECT 
    project_start_date,
    project_end_date
FROM ProjectSummary
ORDER BY 
    completion_days ASC,
    project_start_date ASC;