LeetCode(数据库)- 每月交易II

骑猪看日落 2022-09-09 13:49 220阅读 0赞

题目链接:点击打开链接

题目大意:略。

解题思路:先将退单表 union all 到交易表,无非是把 state 标记为一个新的状态,比如 cancel,这样两张表可以合并操作,思路就清晰很多。

第二个案例为什么二月份 CB 会没有?

因为此时此地 approved 和 charge 都没有,所以根据题目要求需要过滤掉。

AC 代码

  1. -- 解决方案(1)
  2. WITH t1 AS(SELECT * FROM Transactions
  3. UNION ALL
  4. SELECT trans_id, country, 'cancel' state, amount, c.trans_date
  5. FROM Chargebacks c JOIN Transactions t
  6. ON c.trans_id = t.id),
  7. t2 AS(SELECT DATE_FORMAT(trans_date, '%Y-%m') month,
  8. country,
  9. COUNT(IF(state = 'approved', 1, null)) approved_count,
  10. SUM(IF(state = 'approved', amount, 0)) approved_amount,
  11. COUNT(IF(state = 'cancel', 1, null)) chargeback_count,
  12. SUM(IF(state = 'cancel', amount, 0)) chargeback_amount
  13. FROM t1
  14. GROUP BY month, country)
  15. SELECT *
  16. FROM t2
  17. WHERE !(approved_count = 0 AND chargeback_count = 0)
  18. -- 解决方案(2)
  19. SELECT month, country,
  20. COUNT(IF(tag=1, 1, NULL)) AS approved_count,
  21. SUM(IF(tag=1, amount, 0)) AS approved_amount,
  22. COUNT(IF(tag=0, 1, NULL)) AS chargeback_count,
  23. SUM(IF(tag=0, amount, 0)) AS chargeback_amount
  24. FROM (
  25. SELECT country, amount, 1 AS tag,
  26. date_format(trans_date, '%Y-%m') AS month
  27. FROM Transactions
  28. WHERE state='approved'
  29. UNION ALL
  30. SELECT country, amount, 0 AS tag,
  31. date_format(c.trans_date, '%Y-%m') AS month
  32. FROM Transactions AS t RIGHT OUTER JOIN Chargebacks AS c
  33. ON t.id = c.trans_id
  34. ) AS temp
  35. GROUP BY month, country;

发表评论

表情:
评论列表 (有 0 条评论,220人围观)

还没有评论,来说两句吧...

相关阅读

    相关 leetcode 92II

    反转从位置 m 到 n 的链表。请使用一趟扫描完成反转。 说明: 1 ≤ m ≤ n ≤ 链表长度。 示例: 输入: 1->2->3->4->5->NULL, m =