我有两张table:
表1(订阅)
ID - NAME - COURSEID - STATUS
1 - JOHN - 1 - 0
2 - MIKE - 1 - 0
3 - JANE - 2 - 0
4 - PAUL - 1 - 1
表2(课程描述)
COURSEID - COURSE - NAME - UNIT
1 - EXCEL - BASIC - XYZ Street
2 - WORD - MASTER - ABC Street
3 - PPOINT - BASIC - MNO Street
我需要这个:
结果表:
COURSEID - COURSE - NAME - UNIT - TOTAL SUBSCRIPTIONS WITH STATUS = 0
1 - EXCEL - BASIC - XYZ Street - 2
2 - WORD - MASTER - ABC Street - 1
3 - PPOINT - BASIC - MNO Street - 0
我尝试左连接:
SELECT TABLE2.COURSEID, TABLE2.COURSE, TABLE2.NAME, TABLE2.UNITY COUNT(*) TOTAL FROM TABLE1 LEFT JOIN TABLE2 ON TABLE1.ID = TABLE2.ID GROUP BY TABLE1.ID
但我只订了课程。
怎么了?
1条答案
按热度按时间kadbb4591#
此查询将执行您想要的操作。它使用条件聚合(conditional aggregation,即
SUM
的CASE
语句)以确定状态为0的订阅数。输出:
sqlfiddle演示