ผมติดตรงหาผลรวมกิตเกรด A จำนวน 3 หน่วยเ่านั้นรวม15 หน่วยกิต และเกรดในปีนั้นห้ามมี F และ W ของวิชาทั้งหมดในเทอมนั้น
Code ที่ผมใช้ Code (SQL)
SELECT student1.ID,student1.MAJOR,student1.STATUS,scoreed.GRADE,scoreed.YRSM,count(scoreed.GRADE) as cnt FROM [transcripts].[dbo].[student1]
INNER JOIN [transcripts].[dbo].[scoreed] ON scoreed.ID = student1.ID
where ((((student1.MAJOR) NOT LIKE '%x%') AND ((student1.CAMPUS)='S') AND (((student1.STATUS)='y')
or ((student1.STATUS)='b')or ((student1.STATUS)='r') or ((student1.STATUS)='l') )
and ((scoreed.CS_CODE) NOT LIKE '%00%') and (scoreed.YRSM<=592) and ((student1.ID) NOT LIKE '%60%')
and ((scoreed.YRSM) NOT LIKE '0%')) and scoreed.GRADE='A' )
group by student1.ID,scoreed.GRADE,student1.MAJOR,student1.STATUS,scoreed.YRSM
having count(scoreed.GRADE)=5
order by student1.ID,scoreed.YRSM ASC
ปี 592
รหัส ชื่อวิชา หน่วยกิต เกรด
xxx Badminton 1 A
xxx Foundation English I 3 A
xxx The Use of Library Resources 1 A
xxx Calculus I 3 W
xxx Introduction to Computer Science 4 C
xxx Digital Computer Logic 3 D+
xxx Laboratory in Abridged Physics 1 B
xxx Abridged Physics 3 A
xxx Personality Development 3 A
x Bowling 1 A
x Foundation English II 3 A
x Philosophy of Sufficiency Economics and Buddhism 3 A
x Calculus I 3 A
x Computer Programming 3 A
x Health for Life 3 A
x Thai Language for Communication 3 C
x Concept of Science and Philosophy 3 C
select * from (
select id, count(*) as c from table
where grade=A and หน่วยกิต=3
group by id
having c>4
) as t
where not exist ( select * from table where id=t.id and (grade=F or grade=W) limit 1 )
error
Msg 1033, Level 15, State 1, Line 13
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.
Msg 156, Level 15, State 1, Line 23
Incorrect syntax near the keyword 'order'.
แก้ขอย่างไรครับ Code (SQL)
select * from (
SELECT student1.ID,student1.MAJOR,student1.STATUS,scoreed.GRADE,scoreed.YRSM
,count(scoreed.GRADE) as cnt , sum(CAST((scoreed.CR_HR) AS INT)) as credit FROM [transcripts].[dbo].[student1]
INNER JOIN [transcripts].[dbo].[scoreed] ON scoreed.ID = student1.ID
where ((((student1.MAJOR) NOT LIKE '%x%') AND ((student1.CAMPUS)='S') AND (((student1.STATUS)='y')
or ((student1.STATUS)='b')or ((student1.STATUS)='r') or ((student1.STATUS)='l') )
and ((scoreed.CS_CODE) NOT LIKE '%00%') and (scoreed.YRSM=601)
and ((scoreed.YRSM) NOT LIKE '0%') and scoreed.GRADE!='F') and (scoreed.GRADE='A' and scoreed.CR_HR!=1)
)
group by student1.ID,scoreed.GRADE,student1.MAJOR,student1.STATUS,scoreed.YRSM,scoreed.CR_HR
having count(scoreed.GRADE)=5
order by student1.ID,scoreed.YRSM
)as qqqqqqqqqqqq
where NOT EXISTS(
SELECT student1.ID,count(scoreed.CS_CODE) as cnt,scoreed.CS_CODE FROM [transcripts].[dbo].[student1]
INNER JOIN [transcripts].[dbo].[scoreed] ON scoreed.ID = student1.ID
where ((((student1.MAJOR) NOT LIKE '%x%') AND ((student1.CAMPUS)='S') AND (((student1.STATUS)='y')
or ((student1.STATUS)='b')or ((student1.STATUS)='r') or ((student1.STATUS)='l') )
and ((scoreed.CS_CODE) NOT LIKE '%00%')))
group by student1.ID,scoreed.CS_CODE
having count(scoreed.CS_CODE)>1
order by student1.ID ASC
)
ในส่วนของ sub query ไม่มีการอ้างอิง student1.ID กับตารางหลัก ก็จะเอาใครมาก็ได้
และควรหัดใช้ alias name อ้างอิง ชื่อตารางแทน จะได้สั้นๆ หน่อย
ในส่วนของ or ถ้าเป็น field เดียวกัน ลอง ใช้ in () ดู จะสั้นกว่า
Code (SQL)
select * from (
SELECT
st.ID,st.MAJOR,st.STATUS,sc.GRADE,sc.YRSM,
count(sc.GRADE) as cnt , sum(CAST((sc.CR_HR) AS INT)) as credit
FROM [transcripts].[dbo].[student1] as st
INNER JOIN [transcripts].[dbo].[scoreed] as sc ON sc.ID = st.ID
where sc.GRADE='A'
and sc.CR_HR!=1
and (st.MAJOR NOT LIKE '%x%')
AND st.CAMPUS='S'
AND st.STATUS in('y','b','r','l' )
and (sc.CS_CODE NOT LIKE '%00%')
and sc.YRSM=601
and (sc.YRSM NOT LIKE '0%')
and sc.GRADE!='F'
group by st.ID,sc.GRADE,st.MAJOR,st.STATUS,sc.YRSM,sc.CR_HR
having cnt = 5
)as qqqqqqqqqqqq
where NOT EXISTS(
SELECT st1.ID, sc1.CS_CODE, count(sc1.CS_CODE) as cnt
FROM [transcripts].[dbo].[student1] as st1
INNER JOIN [transcripts].[dbo].[scoreed] as sc1 ON sc1.ID = st1.ID
where
(st1.MAJOR NOT LIKE '%x%')
AND (st1.CAMPUS='S')
AND st1.STATUS in ('y','b','r','l')
and (sc1.CS_CODE NOT LIKE '%00%')
and st1.ID=qqqqqqqqqqqq.ID #อ้างอิง ตารางหลักด้วย ไม่อย่างนั้นเอาใครก็ได้
group by st1.ID,sc1.CS_CODE
having cnt>1
)
order by qqqqqqqqqqqq.ID, qqqqqqqqqqqq.YRSM
ปล. order by จะไม่ใส่ใน sub query
ปล. อีกครั้ง ในส่วนของ having อ้างอิงชื่อ alias field ได้เลย
Load balance : Server 03
