表 LESSON 具有字段 LessonDate、MemberId (其中只有这两个是相关的)
Table LESSON has fields LessonDate, MemberId (Among others but only these two are relevant)
用英语:给我一份只上过一门课的学生上那门课的日期列表.
In English: Give me a list of the dates on which students that have only ever taken 1 class, took that class.
我已经尝试了很多东西.这是我最近的尝试:
I have tried a number of things. Here's my latest attempt:
SELECT LessonDate FROM LESSON WHERE (SELECT COUNT(MemberId) GROUP BY (MemberId)) = 1只是不断返回 SQL 错误.很明显,语法和逻辑都关了.
Just keeps returning SQL errors. Obviously, the syntax and logic are off.
推荐答案这个查询一开始可能看起来有点违反直觉.您需要按 MemberId 分组以获取只上过一门课的学生:
The query may seem a little counter-intuitive at first. You need to group by MemberId to get the students who only took one class:
select max(l.LessonDate) as LessonDate from Lesson l group by l.MemberId having count(*) = 1;因为学生只有一堂课,所以 max(l.LessonDate) 就是那个日期.因此,这可以满足您的要求:它会获取只参加过一门课程的成员的所有日期.
Because there is only one class for the student, max(l.LessonDate) is that date. Hence this does what you want: it gets all the dates for members who only took one class.
你的思路也很接近.这是我认为您正在寻找的查询:
Your line of thinking is also pretty close. Here is the query that I think you were looking for:
SELECT LessonDate FROM LESSON WHERE MemberId in (SELECT l2.MemberId FROM Lesson l2 GROUP BY l2.MemberId HAVING COUNT(*) = 1 );如果您想获取具有 2 或 3 行的成员的日期,这种方法更通用.
This approach is more generalizable, if you want to get the dates for members that have 2 or 3 rows.
更多推荐
选择计数 = 1
发布评论