MySql 数据库 -----每日一题

SQL执行顺序2/18

1. 某网站包含两个表，Customers 表和 Orders 表。编写一个 SQL 查询，找出所有从不订购任何东西的客户。
   

解答

1. select c.Name as Customers from Customers c left join Orders o on o.CustomerId = c.Id where o.Id is null;

2. select c.Name as Customers from Customers c where not exists (select * from Orders o where o.CustomerId = c.Id);

3. select c.Name as Customers from Customers c where c.Id not in (select distinct o.CustomerId from Orders o);

收获 :查询A表在B表没有的数据时
可以用联表查询,在表A里的对应B表数据为null,用where条件来筛选
2. 用 not exists 来取子查询的反元素
3. 用 not in 来取子查询的反元素

---

1. select name
   from customer
   where referee_id!=2|| referee_id=null
   select name from customer
   where
   ifnull(referee_id,0)!=2;
2. select name
   from customer
   where id not in
   (select id
   from customer where referee_id =2)

1. 其中使用ifnull(referee_id,0)!=2;referee_id是空返回0否则返回具体值,且值不等于2
2. not in 取子列表里的反元素,即(不在子列表里的父元素)

2.19

select order_number
from Orders
group by customer_number
order by count(customer_number) desc
limit 0,1

通过id分组,再求该id下的订单数量总和,合成大表后顺序排序,只展示订单最多的

收获: 可以group by 后的小表,通过count来排序

2.23

三表查询

1. select s.name
   from salesperson s
   left join Orders o
   on s.sales_id=o.sales_id
   left join Company c
   on o.com_id=c.com_id and c.name=‘RED’
   group by s.name
   having count(c.name)=0;

通过一次左查询合成新表之后,再次进行左查询,然后因为订单原因就会有重复的人名,再通过人名的分组,用having来获取符合条件的表

2. select
   name
   from
   salesperson
   where
   sales_id not in
   (select sales_id from orders where com_id in
   (select com_id from company where name = ‘RED’))

用in 与 not in 来筛选表内容,内容由子查询来获取

收获

4.29

判断是否是一个三角形


1.运用if(reg,a,b)

if语句里,如果reg执行成功, 就是a,不然就是b

select *,if(
    (x+y>z)and( x + z> y)and(y+z>x)
    ,'Yes','No') as Triangle
from Triangle

5.5

请你编写一个 SQL 查询来报告最大的 单一数字 。如果不存在 单一数字 ，查询需报告 null 。

解决:此处要解决的问题是排除重复的数字,重复数字不参与排序

1. select
   (select num
   from my_numbers
   group by num
   having count(num)=1
   order by num desc limit 1)
   as num

此处用分组后求和,让重复的

注意
这道题的坑在null上，一般select不到结果会返回null 如果是条件限制，那么行被删去，什么都不会返回