Mysql相关题目

1.

方法一：(子查询的方式)

select order_num, total_price from(
    select order_num, sum(item_price*quantity) as total_price from OrderItems
group by order_num
)t1
where t1.total_price >=1000
order by order_num；

方法二：having语句的使用

SELECT 
    order_num,
    SUM(item_price * quantity) as total_price
FROM
    OrderItems
GROUP BY
    order_num
HAVING
    total_price >= 1000
ORDER BY
    order_num;

总结：虽然sql语句的执行顺序是：group by 之后就having 再 select，但是having 语句中也可以使用select中的字段别名。

2.

问题：编写 SQL 语句，返回 2020 年 1 月的所有订单的订单号（order_num）和订单日期（order_date），并按订单日期升序排序。

方法：

select order_num,order_date from Orders
where date_format(order_date,'%Y-%m') ='2020-01'
order by order_date;

其他的操作时间的相关函数：

datadiff(日期1,日期2):返回两个日期相差的天数，日期1>日期2返回为正。不能定位到小时、分钟和秒。

例如：datediff('2022-03-14','2022-03-12')，返回2；// 判断是是否是新增用户

date_sub(日期，字段二)：返回的是 日期-多少天， 得到一个新日期。//判断连续登录

例如：date_sub('2022-03-12',2），返回2022-03-10。

timestampdiff(interval, starttime, endtime):返回的是两个时间的相差天数/小时/分钟/秒，比datediff更灵活，datediff只能返回两个日期相差的天数。其中，interval:day, hour, minute, second。

last_day(日期):返回这个月的最后一天的日期

dayofmonth(日期)：返回这个日期在该月的第几天，一般用于取某个月的天数。

dayofmonth('2022-04-18') :    18

 3、

 问题：编写 SQL 语句，返回顾客 ID（cust_id）、顾客名称（cust_name）和登录名（user_login），其中登录名全部为大写字母，并由顾客联系人的前两个字符（cust_contact）和其所在城市的前三个字符（cust_city）组成。提示：需要使用函数、拼接和别名

方法：

select cust_id,cust_name,
upper(concat(substring(cust_name,1,2),substring(cust_city,1,3)))  as user_login from Customers;

注意：substring(字段名，起始位置，截取的长度）

4.

问题：

 编写 SQL 语句，从 Products 表中检索所有的产品名称（prod_name），以及名为 quant_sold 的计算列，其中包含所售产品的总数（在 OrderItems 表上使用子查询和 SUM(quantity)检索）。

方法：

select prod_name, t1.quant_sold from (
    select prod_id, sum(quantity) as quant_sold 
from OrderItems o group by p rod_id
) as t1, Products p
where t1.prod_id = p.prod_id

5.返回每个顾客不同订单的总金额

问题：编写 SQL语句，返回顾客 ID（Orders 表中的 cust_id），并使用子查询返回total_ordered 以便返回每个顾客的订单总数，将结果按金额从大到小排序。

方法：将两个表连接，对cust_id分组求sum。

select cust_id, sum(item_price*quantity) as total_ordered from
OrderItems t1 join Orders  t2 on t1.order_num=t2.order_num
group by cust_id
order by total_ordered desc；

6.where 中是不能使用别名和聚合函数的：

原因：不能count()等聚合函数的原因是 ：聚合函数是针对结果集(select)进行的，但是where条件并不是在查询出结果集之后运行。 不能使用别名的原因：一样的道理：select是在where之后执行的，别名此时还不存在，所以where中不能使用字段别名。

 例子1：select deptno ,avg(sal) from emp where count（*）>3 group by deptno;（错误）

select deptno,avg(sal) from emp group by deptno having count(deptno)>3;（正确）而且having中还可以使用别名进行过滤。

例子2：select id, (c1 + c2) as s from t1  where s > 100（错误，where中使用了别名s，当然改成(c1+c2) > 100是可以的，但表达式复杂之后就太繁琐了）

解决此类问题的办法：

select t2.* from

(select id, (c1 + c2) as c from t1) t2

where c > 100（正确）

 7.确定哪些订单购买了 prod_id 为 BR01 的产品

问题：编写 SQL 语句，使用子查询来确定哪些订单（在 OrderItems 中）购买了 prod_id 为 "BR01" 的产品，然后从 Orders 表中返回每个产品对应的顾客 ID（cust_id）和订单日期（order_date），按订购日期对结果进行升序排序。 

方法一：使用子查询（先在OrderItems 中查询出prod_id 为BR01的订单号，再根据订单号在oders表中查出ID和order_date）

select cust_id,order_date from Orders where order_num in (
    select order_num from OrderItems where prod_id ='BR01'
)
order by order_date

方法二：使用内连接连接连接两个表，进行where过滤输出

select cust_id, order_date from OrderItems od join Orders o 
on od.order_num = o.order_num where od.prod_id = 'BR01'
order by order_date

8.确定最佳顾客（总消费）

 

问题： 

编写 SQL 语句，返回顾客消费不小于1000 的所有顾客的顾客名字和总消费金额。

提示：需要计算总和（item_price 乘以 quantity）。按订单号对结果进行排序，请使用INNER JOIN 语法。

方法：

select cust_name, sum(item_price*quantity) as total_price
from OrderItems od join Orders b on od.order_num = b.order_num
join Customers c on b.cust_id = c.cust_id  
group by c.cust_name
having total_price >=1000
order by  c.cust_name

注意：要对顾客姓名cust_name进行分组，求组内各个顾客的总花费。(看问题需要求每个...的描述来决定对哪个字段进行分组)

9、换座位（Leetcode 626）

问题：编写SQL查询来交换每两个连续的学生的座位号。如果学生的数量是奇数，则最后一个学生的id不交换。

按 id 升序 返回结果表。

方法：case when ... then ... else ... end的使用，将奇数的id+1，偶数的id-1，数量为奇数时，还是id不变。所以先检查数量是否为奇数。

select (case 

            when mod(id,2)!=0 and counts!=id then id+1

            when mod(id,2)!=0 and counts=id then id

            else id - 1

        end

) as id, student

from Seat, (

    select count(*) as counts

    from Seat

) t1

order by id;