SQL: SELECT WHERE COUNT = 1
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22502932/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL: SELECT WHERE COUNT = 1
提问by Display_name
Table LESSONhas fields LessonDate, MemberId(Among others but only these two are relevant)
表LESSON有字段LessonDate, MemberId（除其他外，但只有这两个是相关的）
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
Just keeps returning SQL errors. Obviously syntax and logic is off.
只是不断返回 SQL 错误。显然语法和逻辑是关闭的。
回答by Gordon Linoff
The query may seem a little counter-intuitive at first. You need to group by MemberIdto get the students who only took one class:
该查询起初可能看起来有点违反直觉。您需要分组MemberId以获取只上过一门课的学生：
select max(l.LessonDate) as LessonDate
from Lesson l
group by l.MemberId
having count(*) = 1;
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.
因为学生只有一节课，max(l.LessonDate)就是那个日期。因此，这可以满足您的要求：它获取只参加过一门课程的成员的所有日期。
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
);
This approach is more generalizable, if you want to get the dates for members that have 2 or 3 rows.
如果您想获取具有 2 或 3 行的成员的日期，则此方法更具通用性。
回答by talegna
You need to look into SELECT ... FROM ... GROUP BY ... HAVINGThere is a lot of documentation available online by searching GROUP BYe.g. This article
你需要查看SELECT ... FROM ... GROUP BY ... HAVING网上有很多文档可以通过搜索GROUP BY例如这篇文章
The following SQL groups by MemberIdwhich I thnk is wrong as you want to count the number of member id's,
我认为以下 SQL 组MemberId是错误的，因为您想计算成员 ID 的数量，
SELECT
LessonDate
, MemberId
, COUNT(*) AS Lesson_Count
FROM
Lesson
GROUP BY
LessonDate
, MemberId
HAVING
COUNT(*) = 1
The above query will give you a list of "dates", "members", and the number of lessons taken on that date by that "member". I don't think you need to have the aggregate function (COUNT(*) AS Lesson_Count) as part of the select statement but it's often "nice to have" to give you the confidence that your results are as you would expect.
上述查询将为您提供“日期”、“成员”以及该“成员”在该日期上的课程数量的列表。我认为您不需要将聚合函数 ( COUNT(*) AS Lesson_Count) 作为 select 语句的一部分，但它通常是“很好的”，可以让您确信您的结果如您所愿。
Your query is actually failing because the subquery doesn't have a FROMstatement but the above is better practice:
您的查询实际上失败了，因为子查询没有FROM语句，但以上是更好的做法：
SELECT LessonDate
FROM LESSON
WHERE (SELECT COUNT(MemberId) FROM Lesson GROUP BY (MemberId)) = 1
回答by vhadalgi
select count(LessonDate) as Lesson_date,MemberId from Lesson group by MemberId having Lesson_Date=1
or
或者
select * from
(
select *,rn=Row_number()over(partition by LessonDate order by MemberId) from Lesson
)
where rn=1
回答by needfulthing
The following should work:
以下应该工作：
SELECT LessonDate
FROM LESSION L
WHERE (SELECT COUNT(1) FROM LESSION WHERE MemberId=L.MemberId)=1
