选择哪里计数 = 1

本文介绍了选择哪里计数 = 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.