mysql - 按总价值和交易总数查找顶级支付类型

Question

我有表Payments，其中包含以下数据：

架构（MySQL v5.7）

CREATE TABLE payments (
  Transaction_ID INT NOT NULL,
  Business_ID INT NOT NULL,
  Payment_Amount DECIMAL(25,2),
  Payment_Type VARCHAR(6),
  PRIMARY KEY (Transaction_ID)
 );

CREATE TABLE business_results (
  Business_ID INT NOT NULL,
  Top_Payment_Type_by_Dollar_Amount VARCHAR(6),
  Top_Payment_Type_by_Transactions VARCHAR(6),
  PRIMARY KEY (Business_ID)
);

INSERT INTO payments (Transaction_ID, Business_ID, Payment_Amount, Payment_Type)
  VALUES (1,2, 3.00, 'CASH'),
    (2,1,15.00,'CREDIT'),
    (3,4,5.00,'CASH'),
    (4,3,31.00,'CASH'),
    (5,4,2.00,'CREDIT'),
    (6,2,25.00,'CASH'),
    (7,2,30.00,'CREDIT'),
    (8,3,16.00,'CASH'),
    (9,1,7.00,'CREDIT'),
    (10,4,4.00,'CREDIT');

---

查询 #1

select * from payments;

| Transaction_ID | Business_ID | Payment_Amount | Payment_Type |
| -------------- | ----------- | -------------- | ------------ |
| 1              | 2           | 3              | CASH         |
| 2              | 1           | 15             | CREDIT       |
| 3              | 4           | 5              | CASH         |
| 4              | 3           | 31             | CASH         |
| 5              | 4           | 2              | CREDIT       |
| 6              | 2           | 25             | CASH         |
| 7              | 2           | 30             | CREDIT       |
| 8              | 3           | 16             | CASH         |
| 9              | 1           | 7              | CREDIT       |
| 10             | 4           | 4              | CREDIT       |

---

在 DB Fiddle 上查看

交易类型包括“信用”或“现金”

我正在尝试使用以下结果创建查询：

• 第 1 列：Business_ID
• 第 2 列：按支付类型的总和计算，哪些支付类型的总美元金额最大
• 第 3 列：哪些支付类型的交易次数最多

根据提供的数据，结果应如下：

---

+-------------+-----------------------------------+----------------------------------+
| Business_ID | Top_Payment_Type_by_Dollar_Amount | Top_Payment_Type_by_Transactions |
+-------------+-----------------------------------+----------------------------------+
|           1 | CREDIT                            | CREDIT                           |
|           2 | CREDIT                            | CASH                             |
|           3 | CASH                              | CASH                             |
|           4 | CREDIT                            | CREDIT                           |
+-------------+-----------------------------------+----------------------------------+

---

我将如何在单个MySQL 查询中执行此操作？

--

标签： mysqlgroup-byleft-join

Answer 1

所以，问题的第一部分可以这样解决......

SELECT x.business_id
     , x.payment_type
  FROM payments x 
  JOIN 
     ( SELECT business_id
            , MAX(payment_amount) payment_amount 
         FROM (SELECT * FROM payments) n
        GROUP  
           BY business_id
     ) y 
    ON y.business_id = x.business_id 
   AND y.payment_amount = x.payment_amount
 ORDER
    BY x.business_id;

...子查询n不是绝对必要的-我可以（并且应该）刚刚使用过FROM payments，但是这样它为解决问题的下一部分提供了线索，本质上，您只需要将 n 替换为 .. .

SELECT business_id
     , payment_type
     , SUM(payment_amount) total 
  FROM payments 
 GROUP 
    BY business_id
     , payment_type; 

...并稍微调整主连接子句