当前位置:Gxlcms > 数据库问题 > [LeetCode]Sql系列2

[LeetCode]Sql系列2

时间:2021-07-01 10:21:17 帮助过:16人阅读

题目 1205. 每月交易II

Transactions 记录表

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| id             | int     |
| country        | varchar |
| state          | enum    |
| amount         | int     |
| trans_date     | date    |
+----------------+---------+
id 是这个表的主键。
该表包含有关传入事务的信息。
状态列是类型为 [approved(已批准)、declined(已拒绝)] 的枚举。
?

Chargebacks 表

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| trans_id       | int     |
| charge_date    | date    |
+----------------+---------+
退单包含有关放置在事务表中的某些事务的传入退单的基本信息。
trans_id 是 transactions 表的 id 列的外键。
每项退单都对应于之前进行的交易,即使未经批准。
?

编写一个 SQL?查询,以查找每个月和每个国家/地区的已批准交易的数量及其总金额、退单的数量及其总金额。

注意:在您的查询中,给定月份和国家,忽略所有为零的行。

查询结果格式如下所示:

Transactions 表:
+------+---------+----------+--------+------------+
| id   | country | state    | amount | trans_date |
+------+---------+----------+--------+------------+
| 101  | US      | approved | 1000   | 2019-05-18 |
| 102  | US      | declined | 2000   | 2019-05-19 |
| 103  | US      | approved | 3000   | 2019-06-10 |
| 104  | US      | declined | 4000   | 2019-06-13 |
| 105  | US      | approved | 5000   | 2019-06-15 |
+------+---------+----------+--------+------------+

Chargebacks 表:
+------------+------------+
| trans_id   | trans_date |
+------------+------------+
| 102        | 2019-05-29 |
| 101        | 2019-06-30 |
| 105        | 2019-09-18 |
+------------+------------+

Result 表:
+----------+---------+----------------+-----------------+-------------------+--------------------+
| month    | country | approved_count | approved_amount | chargeback_count  | chargeback_amount  |
+----------+---------+----------------+-----------------+-------------------+--------------------+
| 2019-05  | US      | 1              | 1000            | 1                 | 2000               |
| 2019-06  | US      | 2              | 8000            | 1                 | 1000               |
| 2019-09  | US      | 0              | 0               | 1                 | 5000               |
+----------+---------+----------------+-----------------+-------------------+--------------------+

来源:力扣(LeetCode)
链接:https://leetcode-cn.com/problems/monthly-transactions-ii
著作权归领扣网络所有。商业转载请联系官方授权,非商业转载请注明出处。

题解

  • 两张表加tag字段区分,查询出需要的子段,并用union all 合两张表(PS:union all不去重复数据行,union去重复行) 作为新表
  • 从新标查询结果,聚合函数内部使用if语句进行指定行数据的求和和计数。

代码

# Write your MySQL query statement below
select 
`month`,
country,
sum(if(flag = 0 ,1,0)) as approved_count,
sum(if(flag = 0, amount ,0)) as approved_amount,
sum(if(flag = 1, 1,0)) as chargeback_count,
sum(if(flag = 1, amount, 0)) as chargeback_amount
from 
(select country, amount,date_format(t.trans_date,‘%Y-%m‘) as `month`, 0 as flag
from Transactions t
where state=‘approved‘
union all
select country,amount,date_format(c.trans_date,‘%Y-%m‘) as `month` ,1 as flag
from Transactions t right join Chargebacks c
on id = trans_id
) tmp 
group by `month`,country
order by `month`,country

题目2

编写一个 SQL 查询,查找所有至少连续出现三次的数字。

+----+-----+
| Id | Num |
+----+-----+
| 1  |  1  |
| 2  |  1  |
| 3  |  1  |
| 4  |  2  |
| 5  |  1  |
| 6  |  2  |
| 7  |  2  |
+----+-----+
例如,给定上面的 Logs 表, 1 是唯一连续出现至少三次的数字。

+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+

来源:力扣(LeetCode)
链接:https://leetcode-cn.com/problems/consecutive-numbers
著作权归领扣网络所有。商业转载请联系官方授权,非商业转载请注明出处。

题解

  • 使用用户变量,@pre, @cnt,,(select @pre := null ,@cnt := null) initTable的方式为用户变量赋初值。
  • :=表示赋值,=表示相等判断
  • case 语句使用方法:
case  
    when condition then result
    when condition then result
    when condition then result
else result
end
  • 此题若第一个case不满足,会进入第二个case,首先会执行赋值操作 @pre := Num,又由于第二个case一定为真,会执行赋值操作@cnt := 1

代码

# Write your MySQL query statement belowselect Num, 
select distinct Num as ConsecutiveNums
from (
    select Num,
    case
        when Num = @pre then @cnt := @cnt + 1
        when @pre := Num then @cnt := 1
    end as occurCnt 
    from Logs,(select @pre := null ,@cnt := null) initTable
) tmp
where occurCnt >= 3

[LeetCode]Sql系列2

标签:arch   group by   tps   去重复   HERE   结果   The   计数   log   

人气教程排行