小探知识库

# 练习-综合性练习二

有一张流水表(bill)，包含下面字段

  • 流水号: bill_no
  • 流水类型: bill_type, 1=收入, 2=支出
  • 流水金额: bill_amount
  • 流水时间: bill_time
  • 流水来源: bill_source, 1=银行卡, 2=现金, 3=支付宝, 4=微信

以下是生成的 1-3 月流水数据，满足每天流水金额为 100-300 之间的随机金额，1 月有 6 条数据，2 月有 8 条数据，3 月有 4 条数据。 需要保证同一日都有支出和收入

流水号 流水类型 流水金额 流水时间 流水来源
B0001 1 150 2024-01-01 1
B0002 2 200 2024-01-01 2
B0003 1 250 2024-01-02 3
B0004 2 120 2024-01-02 4
B0005 1 180 2024-01-03 1
B0006 2 300 2024-01-03 2
B0007 1 150 2024-02-01 3
B0008 2 220 2024-02-01 4
B0009 1 100 2024-02-02 1
B0010 2 280 2024-02-02 2
B0011 1 200 2024-02-03 3
B0012 2 150 2024-02-03 4
B0013 1 180 2024-02-04 1
B0014 2 250 2024-02-04 2
B0015 1 120 2024-03-01 3
B0016 2 300 2024-03-01 4
B0017 1 150 2024-03-02 1
B0018 2 200 2024-03-02 2

# 查询每月结余

查询每月结余，返回字段

  • 日期：bill_time，格式为 YYYY-MM
  • 结余：surplus
select 
    date_format(bill_time, '%Y-%m') bill_time,
    (sum(case when bill_type = 1 then bill_amount else 0 end) - sum(case when bill_type = 2 then bill_amount else 0 end)) as surplus
from bill
group by date_format(bill_time, '%Y-%m')

# 查询每月结余增长率

查询每月结余增长率，返回字段

  • 日期：bill_time，格式为 YYYY-MM
  • 结余增长率：surplus_growth_rate
select 
    date_format(bill_time, '%Y-%m') bill_time,
    (surplus - lag(surplus) over (order by bill_time)) / lag(surplus) over (order by bill_time) * 100 as surplus_growth_rate
from (
    select 
        date_format(bill_time, '%Y-%m') bill_time,
        (sum(case when bill_type = 1 then bill_amount else 0 end) - sum(case when bill_type = 2 then bill_amount else 0 end)) as surplus
    from bill
    group by date_format(bill_time, '%Y-%m')
) t

上面sql解析

  • 子查询：首先，通过子查询从bill表中提取数据，计算每个月的余额。这里使用了date_format函数将bill_time字段格式化为YYYY-MM格式，然后使用sum函数分别计算bill_type为1（收入）和2（支出）的金额总和，并相减得到每个月的余额。

  • 计算余额增长百分比：在主查询中，使用lag窗口函数获取上个月的余额，然后计算当前月余额相对于上个月余额的增长百分比。

# 查询每月最后有数据的一天的结余

查询每个月最后有数据的一天的结余，返回字段

  • 日期：bill_time，格式为 YYYY-MM-DD
  • 结余：surplus

select
  t.bill_time bill_time,
  (sum(case when bill_type = 1 then bill_amount else 0 end) - sum(case when bill_type = 2 then bill_amount else 0 end)) as surplus
from (
  select
    date_format(bill_time, '%Y-%m-%d') bill_time,
    bill_type,
    bill_amount,
    row_number() over (partition by date_format(bill_time, '%Y-%m') order by bill_time desc) as rank,
  from bill
  where rank = 1
) t
group by t.bill_time

解析：

  • 子查询：首先，从bill表中选取每一天的最后一条记录。这是通过dense_rank()窗口函数实现的，该函数根据bill_time按月分区并按时间降序排列，然后选取每个分区中排名为1的记录。这些记录包括bill_time、bill_type和bill_amount。

  • 主查询：在子查询的基础上，计算每一天的收入和支出总额。具体来说，使用case语句分别计算bill_type为1（收入）和2（支出）的bill_amount总和，然后相减得到余额。

  • 分组：最后，按bill_time进行分组，以便计算每一天的余额。

# 'a,b,c,d' 转换为数组

在MySQL中，现有表如下：

  • 导航表nav，
    • id: 主键
    • type_id: 导航类型id，类型为“1,2,3,4”。最多不超过100个
  • 导航类型表nav_type，
    • id: 主键
    • type_name，导航类型名称

需要查询导航表中的所有数据，并根据导航类型的type_id，查询导航类型表中的导航类型名称，并将将type_id扁平化。


WITH nav_type_all AS (
  select
    nav.id,
    nav_type.name name,
    substring_index(substring_index(nav.type_id, ',', numbers.n), ',', -1) as type_id
  from nav
  -- 生成1 - 100的数字
  CROSS JOIN (
    SELECT 1 + units.i + tens.i * 10 as n
    FROM (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) units

    CROSS JOIN (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) tens
    WHERE 1 + units.i + tens.i * 10 <= 100
  ) numbers
)
select * from nav_type_all
left join nav_type on nav_type_all.type_id = nav_type.id

该sql的核心操作是：

  • 生成1 - 100的数字
  • 将nav.type_id扁平化
  • 将扁平化的type_id与nav_type.id进行关联
substring_index(substring_index(nav.type_id, ',', numbers.n), ',', -1) as type_id
  • substring_index(nav.type_id, ',', numbers.n)：将nav.type_id按逗号分割，并取第numbers.n个值。
  • substring_index(substring_index(nav.type_id, ',', numbers.n), ',', -1)：将上一步的结果再按逗号分割，并取最后一个值。
  SELECT 1 + units.i + tens.i * 10 as n
  FROM (SELECT 0 i UNION ... SELECT 9) units
  CROSS JOIN (SELECT 0 i UNION ... SELECT 9) tens
  WHERE 1 + units.i + tens.i * 10 <= 100

这部分生成1到100的数字序列，用于拆分字符串。通过交叉连接两个包含0-9数字的表，生成了所有可能的十位和个位组合。

select * from nav_type_all
left join nav_type on nav_type_all.type_id = nav_type.id

最后，将拆分后的数据与nav_type表进行左连接，根据拆分出的type_id匹配nav_type表中的id字段。

实际的效果

nav表

id type_id ...
1 "1,3,5" ...
2 "2,4" ...

nav_type表

id name ...
1 "类型1" ...
2 "类型2" ...
3 "类型3" ...
4 "类型4" ...
5 "类型5" ...

执行结果

id name x type_id id name ...
1 (原name) 1 1 "类型1" ...
1 (原name) 3 3 "类型3" ...
1 (原name) 5 5 "类型5" ...
2 (原name) 2 2 "类型2" ...
2 (原name) 4 4 "类型4" ...

← MySQL数据类型 →

练习-综合性练习二