SELECT days, CASE WHEN days >= 0 AND days <31 THEN '0-1 month' WHEN days >= 31 AND days <62 THEN '1-2 month' WHEN days >= 62 AND days <93 THEN '2-3 month' WHEN days >= 93 THEN '3 months' END AS timeGroup FROM v_select_resignlinkup group by timeGroup这个sql有什么问题
时间: 2023-05-21 20:03:37 浏览: 53
这个 SQL 语句没有语法错误,但是它的 group by 子句只包含了一个列,而这个列是通过 CASE 语句生成的,这可能会导致结果不准确。正确的做法是在 group by 子句中使用原始的 days 列。
相关问题
select lot_id, max(claim_time)claim_time, count(case when(ope_category='Ship')then lot_id else null end) as LS, count(case when(ope_category='Unship') then lot_id else null end) as LUS from f3rpt.F3_TB_DAILY_FABOUT where substr(lot_id,1,2) not in('CA','CW','ES','E0','EM') and lot_type = 'Production' AND LOT_ID NOT LIKE 'H%' and substr(lot_id,7,4)='.00F' and ope_category in ('Ship','Unship') and year(claim_time) = year(current date - 1 days) and month(claim_time) = month(current date - 1 days) group by lot_id) as a where LS - LUS > 0 ,以上sql如何优化
这个SQL语句中,可以优化的地方如下:
1. 在WHERE子句中,使用函数year()和month()可能导致索引失效,可以考虑将其改为claim_time的范围查询,如下所示:
```
AND claim_time >= DATE(current date - 1 month) AND claim_time < DATE(current date)
```
2. 在COUNT函数中,使用CASE语句进行条件统计,可以改为使用SUM函数,如下所示:
```
SUM(CASE WHEN ope_category='Ship' THEN 1 ELSE 0 END) AS LS
SUM(CASE WHEN ope_category='Unship' THEN 1 ELSE 0 END) AS LUS
```
3. 在WHERE子句中,使用了聚合函数,将查询结果限制在LS-LUS>0的记录,这种方式不利于索引的使用,可以考虑使用HAVING子句,如下所示:
```
HAVING LS > LUS
```
4. 在子查询中,只需要查询claim_time的最大值,可以使用MAX函数来代替ORDER BY和LIMIT 1子句,如下所示:
```
MAX(claim_time) AS claim_time
```
综上所述,优化后的SQL语句如下:
```
SELECT lot_id, MAX(claim_time) AS claim_time,
SUM(CASE WHEN ope_category='Ship' THEN 1 ELSE 0 END) AS LS,
SUM(CASE WHEN ope_category='Unship' THEN 1 ELSE 0 END) AS LUS
FROM f3rpt.F3_TB_DAILY_FABOUT
WHERE substr(lot_id,1,2) NOT IN ('CA','CW','ES','E0','EM')
AND lot_type = 'Production'
AND LOT_ID NOT LIKE 'H%'
AND substr(lot_id,7,4)='.00F'
AND ope_category IN ('Ship','Unship')
AND claim_time >= DATE(current date - 1 month) AND claim_time < DATE(current date)
GROUP BY lot_id
HAVING LS > LUS;
```
select u_i.uid as uid, count(distinct act_month) as act_month_total, count(distinct case when year(act_time) = 2021 then act_day end) as act_days_2021, count(distinct case when year(act_time) = 2021 and tag = 'exam' then act_day end) as act_days_2021_exam, count(distinct case when year(act_time) = 2021 and tag = 'question' then act_day end) as act_days_2021_question from user_info u_i left join (select uid, start_time as act_time, date_format(start_time, '%Y%m') as act_month, date_format(start_time, '%Y%m%d') as act_day, 'exam' as tag from exam_record union all select uid, submit_time as act_time, date_format(submit_time, '%Y%m') as act_month, date_format(submit_time, '%Y%m%d') as act_day, 'question' as tag from practice_record ) exam_and_practice on exam_and_practice.uid = u_i.uid where u_i.level >= 6 group by uid order by act_month_total desc, act_days_2021 desc; 请用oracle语法来改写这段sql
以下是使用 Oracle 语法改写后的 SQL 查询语句:
```
SELECT u_i.uid,
COUNT(DISTINCT act_month) AS act_month_total,
COUNT(DISTINCT CASE
WHEN EXTRACT(YEAR FROM act_time) = 2021 THEN act_day
END) AS act_days_2021,
COUNT(DISTINCT CASE
WHEN EXTRACT(YEAR FROM act_time) = 2021 AND tag = 'exam' THEN act_day
END) AS act_days_2021_exam,
COUNT(DISTINCT CASE
WHEN EXTRACT(YEAR FROM act_time) = 2021 AND tag = 'question' THEN act_day
END) AS act_days_2021_question
FROM user_info u_i
LEFT JOIN (SELECT uid,
start_time AS act_time,
TO_CHAR(start_time, 'YYYYMM') AS act_month,
TO_CHAR(start_time, 'YYYYMMDD') AS act_day,
'exam' AS tag
FROM exam_record
UNION ALL
SELECT uid,
submit_time AS act_time,
TO_CHAR(submit_time, 'YYYYMM') AS act_month,
TO_CHAR(submit_time, 'YYYYMMDD') AS act_day,
'question' AS tag
FROM practice_record) exam_and_practice
ON exam_and_practice.uid = u_i.uid
WHERE u_i.level >= 6
GROUP BY u_i.uid
ORDER BY act_month_total DESC, act_days_2021 DESC;
```
在 Oracle 中,日期时间的函数使用 `EXTRACT`,日期格式化使用 `TO_CHAR`。同时,Oracle 中使用分号作为语句结束符。