with e as (select * from exam_record join examination_info using(exam_id) where tag = "SQL") select * from e哪里错了，请改对

这个SQL查询看起来试图从`exam_record`表中选择所有列，并通过`exam_id`关联到`examination_info`表，然后筛选出tag为"SQL"的相关记录。如果语法上没有错误，那么它是有效的。但是，如果遇到问题，可能是以下几个方面：

1. 错误：如果`exam_id`不是两个表之间的公共键或没有正确的外键约束，join可能会失败。
2. 错误：如果`tag`字段不存在于`exam_record`表中，你需要确认是在`examination_info`表里查找。
3. 错误：如果有额外的条件需要满足，如时间范围、特定的学生ID等，应该添加在where子句中。

改正后的版本假设`tag`字段在`exam_record`表中存在，如果是正确的外键并能找到匹配项：

WITH e AS (
    SELECT er.*, ei.*
    FROM exam_record er
    INNER JOIN examination_info ei ON er.exam_id = ei.exam_id
    WHERE er.tag = 'SQL'
)
SELECT * FROM e;

如果你需要进一步帮助，提供具体的错误提示会有助于找出问题所在。

相关问题

SET sql_mode = 'NO_UNSIGNED_SUBTRACTION'; select * ,exam_cnt_rank_21-exam_cnt_rank_20 as rank_delta from( select *,concat(round((exam_cnt_21-exam_cnt_20)*100/exam_cnt_20,1),'%') as growth_rate, rank()over(order by exam_cnt_20 desc) as exam_cnt_rank_20, rank()over(order by exam_cnt_21 desc) as exam_cnt_rank_21 from( select i.tag,count(if(year(submit_time)=2020 and month(submit_time)<=6,r.id,null)) as exam_cnt_20, count(if(year(submit_time)=2021 and month(submit_time)<=6,r.id,null)) as exam_cnt_21 from exam_record r left join examination_info i on r.exam_id=i.exam_id group by i.tag ) a )b where exam_cnt_20*exam_cnt_21>0 order by growth_rate desc,exam_cnt_rank_21 desc ;

这是一个MySQL查询语句，用于计算考试记录的增长率和排名差异。同时，查询中设置了sql_mode为'NO_UNSIGNED_SUBTRACTION'，以避免无符号数相减时出现错误。

请注意，这是一个假设你已经创建了相关的表并插入了数据的查询语句。如果你还没有创建表或插入数据，请确保在运行此查询之前进行相应的准备工作。

以下是查询语句：

SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';

SELECT *, exam_cnt_rank_21 - exam_cnt_rank_20 AS rank_delta
FROM (
    SELECT *,
        CONCAT(ROUND((exam_cnt_21 - exam_cnt_20) * 100 / exam_cnt_20, 1), '%') AS growth_rate,
        RANK() OVER (ORDER BY exam_cnt_20 DESC) AS exam_cnt_rank_20,
        RANK() OVER (ORDER BY exam_cnt_21 DESC) AS exam_cnt_rank_21
    FROM (
        SELECT i.tag,
            COUNT(IF(YEAR(submit_time) = 2020 AND MONTH(submit_time) <= 6, r.id, NULL)) AS exam_cnt_20,
            COUNT(IF(YEAR(submit_time) = 2021 AND MONTH(submit_time) <= 6, r.id, NULL)) AS exam_cnt_21
        FROM exam_record r
        LEFT JOIN examination_info i ON r.exam_id = i.exam_id
        GROUP BY i.tag
    ) a
) b
WHERE exam_cnt_20 * exam_cnt_21 > 0
ORDER BY growth_rate DESC, exam_cnt_rank_21 DESC;

这个查询将返回按照考试记录的增长率和2021年上半年考试数量排名的结果集。其中，growth_rate表示增长率，exam_cnt_rank_20表示2020年上半年考试数量的排名，exam_cnt_rank_21表示2021年上半年考试数量的排名，rank_delta表示排名差异。查询结果按照增长率降序和2021年上半年考试数量排名降序排序。同时，通过`WHERE exam_cnt_20 * exam_cnt_21 > 0`条件过滤掉考试数量为零的记录。

请根据你的实际需求和数据表结构进行适当的调整和修改。

select distinct exam_id, duration, release_time from (select exam_id as exam_id, duration, release_time, sum(case when rank1 = 2 then costtime when rank2 = 2 then -costtime else 0 end) as sub from ( select e_i.exam_id, duration, release_time, timestampdiff(minute, start_time, submit_time) as costtime, row_number() over(partition by e_r.exam_id order by timestampdiff(minute, start_time, submit_time) desc) rank1, row_number() over(partition by e_r.exam_id order by timestampdiff(minute, start_time, submit_time) asc) rank2 from exam_record e_r join examination_info e_i on e_r.exam_id = e_i.exam_id where submit_time is not null ) table1 group by exam_id ) table2 where sub * 2 >= duration order by exam_id desc;把这段sql改写成oracle语法

SELECT DISTINCT exam_id, duration, release_time
FROM (
SELECT exam_id, duration, release_time, SUM(CASE WHEN rank1 = 2 THEN costtime WHEN rank2 = 2 THEN -costtime ELSE 0 END) AS sub
FROM (
SELECT e_i.exam_id, duration, release_time, TIMESTAMPDIFF(MINUTE, start_time, submit_time) AS costtime,
ROW_NUMBER() OVER(PARTITION BY e_r.exam_id ORDER BY TIMESTAMPDIFF(MINUTE, start_time, submit_time) DESC) rank1,
ROW_NUMBER() OVER(PARTITION BY e_r.exam_id ORDER BY TIMESTAMPDIFF(MINUTE, start_time, submit_time) ASC) rank2
FROM exam_record e_r JOIN examination_info e_i
ON e_r.exam_id = e_i.exam_id
WHERE submit_time IS NOT NULL
) table1
GROUP BY exam_id, duration, release_time
) table2
WHERE sub * 2 >= duration
ORDER BY exam_id DESC;