【LeetCode 2989. 班级表现】动态sql编程示例

题目地址

https://leetcode.cn/problems/class-performance/description/

题目大意

编写一个查询，计算学生获得的 最高分 和 最低分 之间的 总分差（3 次作业的总和）。

assignment1, assignment2, assignment3三列

可以对这三列加和得到一个总分列total_score，需要计算total_score这一列的极差(最大值-最小值)

代码

T-SQL

/* Write your T-SQL query statement below */

DECLARE @sql_dynamic NVARCHAR(MAX);

SELECT @sql_dynamic = 'SELECT MAX(total_score) - MIN(total_score) AS difference_in_score FROM (SELECT ' + STRING_AGG(COLUMN_NAME, ' + ') + ' AS total_score FROM Scores) as t1'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Scores'
AND COLUMN_NAME LIKE 'assignment%';


-- SELECT @sql_dynamic;
EXEC sp_executesql @sql_dynamic;

Oracle

SELECT 'SELECT MAX(total_score) - MIN(total_score) AS difference_in_score FROM (SELECT ' ||
           LISTAGG(column_name, ' + ') WITHIN GROUP (ORDER BY column_name) ||
           ' AS total_score FROM Scores) t1'
    FROM all_tab_cols
    WHERE table_name = 'SCORES'
    AND column_name LIKE 'ASSIGNMENT%';

-- 这里仅展示了部分代码

MySQL

SELECT CONCAT('SELECT MAX(total_score) - MIN(total_score) AS difference_in_score FROM (SELECT ', GROUP_CONCAT(COLUMN_NAME SEPARATOR ' + '), ' AS total_score FROM Scores) as t1') 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'test'
AND TABLE_NAME = 'Scores'
AND COLUMN_NAME LIKE 'assignment%';

-- 这里仅展示了部分代码