쿼리 좀 봐 주세요.
본문
교과 등급평균을 구하는 쿼리문인데 좀 더 간단한 방법이 없을까요. 뭔가 지저분한 느낌이...
select
(sum(case when (grade > 0 and (gyoga_1 = '국어' or gyoga_1 = '영어' or gyoga_1 = '수학')) then grade_hab else 0 end)/sum(case when (grade > 0 and (gyoga_1 = '국어' or gyoga_1 = '영어' or gyoga_1 = '수학')) then danwisu else 0 end)) as sum1,
(sum(case when (grade >0 and (gyoga_1 = '국어' or gyoga_1 = '영어' or gyoga_1 = '수학' or left(gyoga_1,2) = '사회')) then grade_hab else 0 end)/sum(case when (grade >0 and (gyoga_1 = '국어' or gyoga_1 = '영어' or gyoga_1 = '수학' or left(gyoga_1,2) = '사회')) then danwisu else 0 end)) as sum2,
(sum(case when (grade > 0 and (gyoga_1 = '국어' or gyoga_1 = '영어' or gyoga_1 = '수학' or gyoga_1 = '과학')) then grade_hab else 0 end)/sum(case when (grade > 0 and (gyoga_1 = '국어' or gyoga_1 = '영어' or gyoga_1 = '수학' or gyoga_1 = '과학')) then danwisu else 0 end)) as sum3,
(sum(case when (grade >0 and (gyoga_1 = '국어' or gyoga_1 = '영어' or gyoga_1 = '수학' or left(gyoga_1,2) = '사회' or gyoga_1 = '과학')) then grade_hab else 0 end)/sum(case when (grade >0 and (gyoga_1 = '국어' or gyoga_1 = '영어' or gyoga_1 = '수학' or left(gyoga_1,2) = '사회' or gyoga_1 = '과학')) then danwisu else 0 end)) as sum4,
(sum(case when grade > 0 then grade_hab else 0 end)/sum(case when grade > 0 then danwisu else 0 end)) as sum5
from {$table_sungjuk} where stu_id = $stu_id
답변 2
아래처럼 하시면 될듯 합니다.
SELECT
SUM(CASE WHEN (grade > 0 AND (gyoga_1 IN ('국어', '영어', '수학'))) THEN grade_hab ELSE 0 END) / SUM(CASE WHEN (grade > 0 AND (gyoga_1 IN ('국어', '영어', '수학'))) THEN danwisu ELSE 0 END) AS sum1,
SUM(CASE WHEN (grade > 0 AND (gyoga_1 IN ('국어', '영어', '수학', '사회') OR LEFT(gyoga_1, 2) = '사회')) THEN grade_hab ELSE 0 END) / SUM(CASE WHEN (grade > 0 AND (gyoga_1 IN ('국어', '영어', '수학', '사회') OR LEFT(gyoga_1, 2) = '사회')) THEN danwisu ELSE 0 END) AS sum2,
SUM(CASE WHEN (grade > 0 AND (gyoga_1 IN ('국어', '영어', '수학', '과학'))) THEN grade_hab ELSE 0 END) / SUM(CASE WHEN (grade > 0 AND (gyoga_1 IN ('국어', '영어', '수학', '과학'))) THEN danwisu ELSE 0 END) AS sum3,
SUM(CASE WHEN (grade > 0 AND (gyoga_1 IN ('국어', '영어', '수학', '사회', '과학') OR LEFT(gyoga_1, 2) = '사회'))) THEN grade_hab ELSE 0 END) / SUM(CASE WHEN (grade > 0 AND (gyoga_1 IN ('국어', '영어', '수학', '사회', '과학') OR LEFT(gyoga_1, 2) = '사회'))) THEN danwisu ELSE 0 END) AS sum4,
SUM(CASE WHEN grade > 0 THEN grade_hab ELSE 0 END) / SUM(CASE WHEN grade > 0 THEN danwisu ELSE 0 END) AS sum5
FROM
{$table_sungjuk}
WHERE
stu_id = $stu_id;
감사합니다. 가독성이 좋습니다.
쳇gpt에 물으니 띄어쓰기 해서 보여주네요.
너무 복잡해서 챗gpt에 질문하시는게 더 빠른 답변 받으실 듯 합니다.^^;
감사합니다.