我的数据库中有4个表。
Students（Idno, Name, CourseId）样本数据：
Idno Name CourseId
------------------------
-101123456 Vijay 101
-101123457 John 102
-101123458 Sam 101
-101123459 Arvind 102
-101123460 Smith 101
Courses（CourseId, CourseNo, CourseName, StreamId）样本数据：
CourseId CourseNo CourseName StreamId
------------------------------------------
-101 53245 C 1
-102 53245 C++ 2
Streams（StreamId, StreamName）样本数据：
StreamId StreamName
---------------------------
-1 Engineering
-2 Medical
Booking（BId, Idno, BStatus）样本数据：
Bid Idno BStatus
--------------------------------
-1110 101123456 Confirmed
-1111 101123456 Confirmed
-1112 101123457 Confirmed
-1113 101123458 Confirmed
-1114 101123459 Confirmed
-1115 101123460 Confirmed
-1116 101123456 Confirmed
-1117 101123457 Confirmed
-1118 101123458 Confirmed
-1119 101123459 Confirmed
-1119 101123460 Cancelled
我在生成以下输出时遇到问题
SNo Stream BookedOnce BookedTwice NonBooked
1 Engineering 2 3 0
2 Medical 3 1 1
谢谢
我认为这需要两步过程。首先，计算每个学生流的预订数量。然后，按每个流计算预订一，两或零的学生数量。
这是一个示例，其中包含内部查询的第一步：
select StreamId
, StreamName
, sum(case when Bookings = 1 then 1 else 0 end) as BookedOnce
, sum(case when Bookings = 2 then 1 else 0 end) as BookedTwice
, sum(case when Bookings = 0 then 1 else 0 end) as NoneBooked
from (
select str.StreamId
, str.StreamName
, s.Idno
, count(b.BId) as Bookings
from Students s
left join
Booking b
on b.Idno = s.Idno
left join
Courses c
on c.CourseId = s.CourseId
left join
Streams str
on str.StreamId = c.StreamId
group by
str.StreamId
, str.StreamName
, s.Idno
) BookingsPerStudentPerStream
group by
StreamId
, StreamName
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句