SQL纠错&注意事项

cannot recognize input near in subquery
1、HQL子查询别名问题
备注：子查询需要加上别名 否则报错。
HQL的书写，select * from (select * from table) ;
正确的方法：select * from (select * from table) a ;

2、SQL中union all的用法
select…from…
union all
select…from…
上下表格的列数应相等
如果想对上下两个表格同时排序，在各自的select from语句中都加order by（要用括号）是无效的，如：

(select
    exam_id,
    COUNT(DISTINCT uid) as uv,
    COUNT(*) as pv
from
    exam_record
GROUP BY exam_id
ORDER BY uv DESC, pv DESC) t1

UNION all

select
    question_id,
    COUNT(DISTINCT uid) as uv,
    COUNT(*) as pv
from
    practice_record
GROUP BY question_id
ORDER BY uv DESC, pv DESC) t2

这时需要在两个select外再套一个select让order生效，如：

SELECT * FROM
(select
    exam_id,
    COUNT(DISTINCT uid) as uv,
    COUNT(*) as pv
from
    exam_record
GROUP BY exam_id
ORDER BY uv DESC, pv DESC) t1

UNION all

select * from
(select
    question_id,
    COUNT(DISTINCT uid) as uv,
    COUNT(*) as pv
from
    practice_record
GROUP BY question_id
ORDER BY uv DESC, pv DESC) t2

union和union all有区别，union是自动去重后合并

3、
语句：select中exam_cnt_rank_21-exam_cnt_rank_20 as rank_delta
报错：SQL_ERROR_INFO: “BIGINT UNSIGNED value is out of range in ‘(f.exam_cnt_rank_21 - d.exam_cnt_rank_20)’”
原因：一般数据库默认都是unsigned, 是不能出现负数的, 可用cast(字段 as signed)即可
改正：cast(exam_cnt_rank_21 as SIGNED)-cast(exam_cnt_rank_20 as SIGNED) as rank_delta