方法1:使用 group by 和 子查询
先去重查出每个class里有几个学生,然后再挑选出符合要求的记录。
SELECT class, COUNT(DISTINCT student)
FROM courses
GROUP BY class
;
然后进行子查询
SELECT class FROM (
SELECT class, COUNT(DISTINCT student) as num
FROM courses
GROUP BY class
)
WHERE num>=5
;
方法二:使用 group by + having 进行查询
SELECT class
FROM courses
GROUP BY class
HAVING COUNT(DISTINCT student) >= 5
首先使用 group by 查询出class 的数量,利用having 在查出的集合中查询符合student要求的记录。
having字句可以让我们筛选成组后的各种数据,where字句在聚合前先筛选记录,也就是说作用在group by和having字句前。而 having子句在聚合后对组记录进行筛选。
select Name as Customers from Customers
where id NOT in ( select CustomerId from Orders) ;
select email from person group by email having count(Id) > 1
Select e1.Name as Employee from Employee e1 , Employee e2 where e1.ManagerId = e2.Id and e1.Salary > e2.Salary
错误示范:select Num ConsecutiveNums from Logs group by num having count(*)>3;
结果一样,但是不符合题目要求。
注意看题,是连续出现三次的数字,即 1 1 1才行 而不是 1 1 2 1 中的三个1
将表复制俩份,第一份的id 与第二份的id-1 进行对比,将第二份的id于第三份的id-1 进行对比,如果三份的id所对的num一样,那就是要查找的数字。
select distinct l1.Num ConsecutiveNums from Logs l1
left join Logs l2 on l1.Id = l2.Id - 1
left join Logs l3 on l1.Id = l3.Id - 2
where l1.Num = l2.Num and l2.Num = l3.Num;
参照博客:https://blog.csdn.net/wal1314520/article/details/80107916
可以使用 CASE...WHEN字句
同时
要想动态地将值设置成列,我们可以在使用 CASE...WHEN...
流程控制语句的同时使用 UPDATE
语句。
case when 条件1 then 取值1 when 条件2 then 取值2 else 取值3 end
when后接条件语句,then后为字段取值(数值或字符串等都可以,但类型须一致)。
对于这道题
case sex "f" then "m" else "f" 即可将 性别转换
UPDATE salary
SET
sex = CASE sex
WHEN "m" THEN "f"
ELSE "m"
END;
SELECT d.Name AS Department,e1.Name AS Employee,e1.Salary
FROM Employee e1 INNER JOIN Department d
ON e1.DepartmentId = d.Id
WHERE (e1.DepartmentId,e1.Salary)
IN (SELECT e2.DepartmentId,MAX(e2.Salary) AS Salary FROM Employee e2
GROUP BY e2.DepartmentId );
参考博客:https://blog.csdn.net/Genius9_9/article/details/81224589
持续更新中............
借鉴博客:http://www.cnblogs.com/lmaster/p/6373045.html