sql习题

https://leetcode.cn/problems/customer-placing-the-largest-number-of-orders/

SELECT
    customer_number
FROM
    orders
GROUP BY 
    customer_number
ORDER BY
    COUNT(customer_number) DESC
LIMIT 1

嵌套函数

select customer_number
from orders
group by customer_number
having count(order_number)>=all(
    select count(order_number)
    from orders
    group by customer_number
)

用窗口函数

select t.customer_number
from
(select customer_number, rank() over(order by count(order_number) desc) as ranking
from orders
group by customer_number) t
where t.ranking = 1

rank() over(order by 列名)的用法

—rank()over(order by 列名排序)的结果是不连续的，如果有4个人，其中有3个是并列第1名，那么最后的排序结果结果如：1 1 1 4

—dense_rank()over(order by 列名排序)的结果是连续的，如果有4个人，其中有3个是并列第1名，那么最后的排序结果如：1 1 1 2

注意：使用rank()over(order by 排序字段 顺序)排序的时候，空值是最大的

如果排序字段为null，可能造成在排序时将null字段排在最前面，影响排序的正确性。
所以建议将dense_rank()over(order by 列名 排序)改为dense_rank()over(order by 列名排序 nulls last)
这样只要排序字段为null,就会放在最后，而不会影响排序结果

组内排序

分组后组内排序

select sell_date,count(distinct product) as num_sold,
group_concat(distinct product order by product asc) as products from Activities
  group by sell_date order by sell_date

分组后组内取最大值

字符串拆分
前缀查询 就用like吧

using等价于join操作中的on
使用using必须满足如下两个条件：

1. 查询必须是等值连接。
2. 等值连接中的列必须具有相同的名称和数据类型。

销售分析III
Sales.sale_date 在且只在 ‘2019-01-01’ and ‘2019-03-31’ 的Product.product_id

SELECT p.product_id,product_name FROM sales s,product p 
WHERE s.product_id=p.product_id
GROUP BY p.product_id
HAVING SUM(sale_date < '2019-01-01')=0
AND SUM(sale_date>'2019-03-31')=0;

select product_id, product_name
from Sales join Product
using(product_id)
group by product_id
having sum(sale_date between "2019-01-01" and "2019-03-31") = count(sale_date)

1158. 市场分析 I
我写的

select 
   Users.user_id as buyer_id,
   Users.join_date as join_date,
  count(SUBSTRING(Orders.order_date,1,4) = '2019') as orders_in_2019
   from Users join Orders on Users.user_id = Orders.buyer_id 
   group by Users.user_id
   order by Users.user_id asc

输出

{"headers": ["buyer_id", "join_date", "orders_in_2019"],
 "values": [[1, "2018-01-01", 2],
                [2, "2018-02-09", 2],
                [3, "2018-01-19", 1],
                [4, "2018-05-21", 1]]}

正确答案

{"headers": ["buyer_id", "join_date", "orders_in_2019"],
 "values": [[1, "2018-01-01", 1],
  [2, "2018-02-09", 2], 
  [3, "2018-01-19", 0], 
  [4, "2018-05-21", 0]]}

我的答案 订单年份为2019这个条件没生效，统计了所有订单

正确答案

select Users.user_id as buyer_id, join_date, ifnull(UserBuy.cnt, 0) as orders_in_2019
from Users
left join (
    select buyer_id, count(order_id) cnt 
    from Orders
    where order_date between '2019-01-01' and '2019-12-31'
    group by buyer_id
) UserBuy
on Users.user_id = UserBuy.buyer_id

或者

select u.user_id as buyer_id,
       u.join_date,
       count(if(year(o.order_date) = '2019', 1, null)) as orders_in_2019
from users u
         left join orders o on o.buyer_id = u.user_id
group by user_id;

分组，组内统计筛选

184. 部门工资最高的员工
185. 
     分组，且找出该组中某个字段的最值

答案

select 
    d.Name as Department,
    e.Name as Employee,
    e.Salary 
from 
    Employee e,Department d 
where
    e.DepartmentId=d.id 
    and
    (e.Salary,e.DepartmentId) 
    in (select max(Salary),DepartmentId from Employee group by DepartmentId);

利用开窗函数可以取每个部门最高，也可以取前二高，前三高，也可以只取第一第三，都OK的

- 每个部门最高
SELECT S.NAME, S.EMPLOYEE, S.SALARY
  FROM (SELECT D.NAME,
               T.NAME EMPLOYEE,
               T.SALARY,
               ROW_NUMBER() OVER(PARTITION BY T.DEPARTMENTID ORDER BY T.SALARY DESC) RN
          FROM EMPLOYEE T
          LEFT JOIN DEPARTMENT D
            ON T.DEPARTMENTID = D.ID) S
 WHERE S.RN = 1

SQL中 ROW_NUMBER 函数的用法
ROW_NUMBER()函数将针对SELECT语句返回的每一行，从1开始编号，赋予其连续的编号。在查询时应用了一个排序标准后，只有通过编号才能够保证其顺序是一致的，当使用ROW_NUMBER函数时，也需要专门一列用于预先排序以便于进行编号。

说明：返回结果集分区内行的序列号，每个分区的第一行从1开始。
语法：ROW_NUMBER () OVER ([ <partition_by_clause> ] <order_by_clause>) 。
备注：ORDER BY 子句可确定在特定分区中为行分配唯一 ROW_NUMBER 的顺序。
参数：<partition_by_clause> ：将 FROM 子句生成的结果集划入应用了 ROW_NUMBER 函数的分区。

我的答案

select user_id,
if(length(name)=1,upper(name),
concat(upper(substr(name,1,1)),lower(substr(name,2,length(name))))
) as name
from Users order by user_id 

评论区别人的答案

select
    user_id,
    concat(upper(left(name,1)),lower(substr(name,2))) as name
from 
    Users
order by 
    user_id

select user_id,
concat(upper(left(name, 1)), lower(right(name, length(name) - 1))) name
from Users
order by user_id

● CONCAT 用来拼接字符串
● LEFT 从左边截取字符
● RIGHT 从右边截取字符
● UPPER 变为大写
● LOWER 变为小写
● LENGTH 获取字符串长度

行程和用户
这里是分组后，，每组里，status !=‘completed’ 占改组总数的比例，保留两位小数

select 
request_at 'Day', 
convert(sum(status !='completed')/count(*),decimal(15,2)) 'Cancellation Rate'
from Trips 
left join Users a on Trips.client_id = a.users_id 
left join Users b on Trips.driver_id = b.users_id
where a.banned = 'No' and b.banned = 'No'
and request_at >= '2013-10-01'
and request_at <= '2013-10-03'
group by request_at
order by request_at

最主要的是
convert(sum(status !=‘completed’)/count(),decimal(15,2))
我一开始用的是
count(status !=‘completed’)/count()
一直不对，其实因为
status !=‘completed’ 为true=1，false时=0
而count(0)=1,count(1)=1
所以应该用sum

除法
convert(除数/被除数,decimal(15,2))
decimal(15,2)中的2是保留几位小数

这一行
convert(sum(status !='completed')/count(*),decimal(15,2)) 'Cancellation Rate'
还可以替换为
round(avg(Status!='completed'), 2) 'Cancellation Rate'

SQL AVG()函数
AVG()通过对表中行数计数并计算其列值之和，求得该列的平均值。AVG()可用来返回所有列的平均值，也可以用来返回特定列或行的平均值。

ROUND() 函数
ROUND() 函数用于把数值字段舍入为指定的小数位数。
SELECT ROUND(column_name,decimals) FROM TABLE_NAME;

601. 体育馆的人流量

这里阻塞我的点
将一行转成多行，比如一个表
table
字段 field1 field2 field3
记录1 1 2 3
记录2 4 5 6
我想转成
field
1
2
3
4
5
6

我的做法

 select 
a.id as id,
b.id as id,
c.id as id
from Stadium a left join Stadium b on a.id+1 = b.id 
left join Stadium c on a.id+2 = c.id 
where a.people >=100 and b.people >=100 and c.people >= 100

最终还是错的，，

还是无法成为三行
我的本意是 distinct(5，6，7，6，7，8)

？？？？？还是不会，，再看

1179. 重新格式化部门表

将多条数据合成一条数据，多条数据以扩展字段点方式存储
我的答案

select 
id as id,
(CASE   WHEN month = 'Jan' THEN revenue as Jan_revenue
        WHEN month = 'Feb' THEN revenue as Feb_revenue
        WHEN month = 'Mar' THEN revenue as Mar_revenue
        WHEN month = 'Apr' THEN revenue as Apr_revenue
        ELSE revenue as May_revenue
         END) AS REMARK
from Department
group by id order by id

当然，错了，语法错误

正确答案

SELECT id, 
SUM(CASE WHEN month='Jan' THEN revenue END) AS Jan_Revenue,
SUM(CASE WHEN month='Feb' THEN revenue END) AS Feb_Revenue,
SUM(CASE WHEN month='Mar' THEN revenue END) AS Mar_Revenue,
SUM(CASE WHEN month='Apr' THEN revenue END) AS Apr_Revenue,
SUM(CASE WHEN month='May' THEN revenue END) AS May_Revenue,
SUM(CASE WHEN month='Jun' THEN revenue END) AS Jun_Revenue,
SUM(CASE WHEN month='Jul' THEN revenue END) AS Jul_Revenue,
SUM(CASE WHEN month='Aug' THEN revenue END) AS Aug_Revenue,
SUM(CASE WHEN month='Sep' THEN revenue END) AS Sep_Revenue,
SUM(CASE WHEN month='Oct' THEN revenue END) AS Oct_Revenue,
SUM(CASE WHEN month='Nov' THEN revenue END) AS Nov_Revenue,
SUM(CASE WHEN month='Dec' THEN revenue END) AS Dec_Revenue
FROM department
GROUP BY id
ORDER BY id;

首先，，同一个月份的奖金要相加后放在一个字段，这个忘了，，其次，case–when–用的不对

by the way，提点别的
group_concat()详解
是将分组中括号里对应的字符串进行连接.如果分组中括号里的参数xxx有多行，那么就会将这多行的字符串连接，每个字符串之间会有特定的符号进行分隔。

将分组中column1这一列对应的多行的值按照column2 升序或者降序进行连接，其中分隔符为seq
如果用到了DISTINCT，将表示将不重复的column1按照column2升序或者降序连接
如果没有指定SEPARATOR的话，也就是说没有写，那么就会默认以 ','分隔
GROUP_CONCAT([DISTINCT] column1 [ORDER BY column2 ASC\DESC] [SEPARATOR seq]);

[ ORDER BY column2 ASC\DESC] :表示将会根据column2升序或者降序连接.其中column2不一定一定要求是column1,只要保证column2在这个分组中即可.如果没有写ORDER BY句段，那么连接是没有顺序的。
[ SEPARATOR seq] : 表示各个column1将会以什么分隔符进行分隔，例如SEPARATOR '’,则表示column1将会以进行分隔。如果没有指定seq的时候，也即没有写SEPARATOR seq这个句段，那么就会默认是以,分隔的。
CONCAT函数中要连接的数据含有NULL，最后返回的是NULL，但是GROUP_CONCAT不会这样，他会忽略NULL值。

值得一提的是，GROUP_CONCAT只是将xxx这一列中的多行数据进行连接成为一行字符串，而CONCAT则是可以将多列数据进行连接。

CASE WHEN条件表达式函数

CASE WHEN condition THEN result
 
[WHEN...THEN...]
 
ELSE result
 
END