时间戳

首页 > 解决方案 > 用于汇总表数据的 SQL

mysql - 用于汇总表数据的 SQL

问题描述

考虑以下 3 个表：

  1. 交易表
+---------+--------------+---------------+---------+----------------+------------+------------+
| Tran_id | Tran_seq_nbr | Tran_sold_amt | Card_no | Payment_Method | Card_type  | Tran_Date  |
+---------+--------------+---------------+---------+----------------+------------+------------+
| 1010    | 1            | 10            | 123     | Card           | Visa       | 2019-01-01 |
| 1010    | 2            | 20            | 124     | Card           | MasterCard | 2019-01-01 |
| 2020    | 1            | 30            | 125     | Card           | Visa       | 2019-01-31 |
| 2020    | 2            | 40            | null    | Cash           | Null       | 2019-01-31 |
| 10101   | 1            | 100           | 123     | Card           | Visa       | 2019-02-01 |
| 1011    | 1            | 100           | null    | Cash           | null       | 2019-02-01 |
+---------+--------------+---------------+---------+----------------+------------+------------+
  1. 客户表
+---------+-----------+
| Cust_id | Cust_name |
+---------+-----------+
|       1 | Jane Doe  |
|       2 | John Doe  |
+---------+-----------+
  1. Credit_card 表 
+---------+---------+------------------+
| Card_no | Cust_id |    Card_type     |
+---------+---------+------------------+
|     123 |       1 | Visa             |
|     124 |       1 | MasterCard       |
|     125 |       2 | Visa             |
|     126 |       2 | MasterCard       |
|     127 |       3 | Visa             |
|     128 |       4 | American Express |
+---------+---------+------------------+

为以下内容编写 SQL 查询：

对于每个客户，找出他们在过去一年内通过每张卡和现金支付的费用。例如：

按卡销售


+---------+-----------+---------+--------------+---------------+
| Cust_id | Cust_name | Card_no |  Card_type   | Tran_sold_amt |
+---------+-----------+---------+--------------+---------------+
|       1 | Jane Doe  |     123 | Visa         |           110 |
|       1 | Jane Doe  |     124 | MasterCard   |            20 |
|       2 | John Doe  |     125 | Visa         |            30 |
|       2 | John Doe  |     125 | Cash_Payment |            40 |
+---------+-----------+---------+--------------+---------------+

我设法获得了数据卡，但无法获得现金交易。

我使用的查询是

    SELECT cu.cust_id
    ,cu.cust_name
    ,cr.card_no
    ,cr.card_type
    ,sum(tr.tran_sold_amt)
    FROM TRANSACTION tr
    INNER JOIN credit_card cr ON cr.card_no = tr.card_no
    INNER JOIN customer_table cu ON cu.cust_id = cr.cust_id
    GROUP BY tr.card_no
    ORDER BY cu.cust_name ASC;

标签： mysqlsql

解决方案

你接近解决方案:)

SELECT cu.cust_id,
       cu.cust_name,
       tr.card_no,
       tr.card_type,
       tr.Payment_Method,
       sum(tr.tran_sold_amt)
FROM TRANSACTION tr
         INNER JOIN customer_table cu ON cu.cust_id = (
    SELECT cust_id
    FROM TRANSACTION tr2
             INNER JOIN credit_card cr2 ON tr2.Card_no = cr2.Card_No
    WHERE tr2.Tran_id = tr.Tran_id
    LIMIT 1
)
GROUP BY tr.Payment_Method, tr.card_no
ORDER BY cu.cust_name ASC;

应该做的伎俩。您必须先按付款方式分组，然后按卡号分组。

推荐阅读