选择哪里计数 = 1 [英] SELECT WHERE COUNT = 1
问题描述
表 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的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!