选择计数 = 1

2022-01-23 00:00:00 sql subquery sql-server

表 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.

相关文章