题目 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 belowselect `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_amountfrom (select country, amount,date_format(t.trans_date,‘%Y-%m‘) as `month`, 0 as flagfrom Transactions twhere state=‘approved‘union allselect country,amount,date_format(c.trans_date,‘%Y-%m‘) as `month` ,1 as flagfrom Transactions t right join Chargebacks con id = trans_id) tmp group by `month`,countryorder 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 resultelse resultend

• 此题若第一个case不满足，会进入第二个case，首先会执行赋值操作 @pre := Num，又由于第二个case一定为真，会执行赋值操作@cnt := 1

代码

# Write your MySQL query statement belowselect Num, select distinct Num as ConsecutiveNumsfrom ( 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) tmpwhere occurCnt >= 3