gpt4 book ai didi

mysql - 如何在 MySQL 右连接中对结果进行排序?

转载 作者:可可西里 更新时间:2023-11-01 08:03:33 26 4
gpt4 key购买 nike

我正在尝试使用以下两个表使用 MySQL 获取全年的月度数据,

  1. 贷款

贷款

` +--------+--------+-------+----------+
| idloan | amount | tot | interest |
+--------+--------+-------+----------+
| 3 | 10000 | 15000 | 50 |
| 4 | 5000 | 6000 | 10 |
| 5 | 20000 | 30000 | 10 |
| 6 | 30000 | 3000 | 10 |
| 7 | 15000 | 16500 | 10 |
+--------+--------+-------+----------+ `

月份

`+---------+-------+
| idmonth | month |
+---------+-------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
| 8 | 8 |
| 9 | 9 |
| 10 | 10 |
| 11 | 11 |
| 12 | 12 |
+---------+-------+ `

我使用这个查询来获取每个月的闲置贷款计数、金额总和和总和,

` SELECT
m.month,
COUNT(l.idloan)AS m_count,
COALESCE(SUM(l.amount),0)AS amount,
COALESCE(SUM(l.total),0)AS total
FROM loan l RIGHT JOIN month m using(month)
GROUP BY m.month `

输出是

` +-------+---------+--------+-------+
| month | m_count | amount | total |
+-------+---------+--------+-------+
| 1 | 0 | 0 | 0 |
| 10 | 1 | 15000 | 16500 |
| 11 | 1 | 30000 | 3000 |
| 12 | 3 | 35000 | 51000 |
| 2 | 0 | 0 | 0 |
| 3 | 0 | 0 | 0 |
| 4 | 0 | 0 | 0 |
| 5 | 0 | 0 | 0 |
| 6 | 0 | 0 | 0 |
| 7 | 0 | 0 | 0 |
| 8 | 0 | 0 | 0 |
| 9 | 0 | 0 | 0 |
+-------+---------+--------+-------+ `

我的问题是如何对它进行排序以获得这样的输出

` +-------+---------+--------+-------+
| month | m_count | amount | total |
+-------+---------+--------+-------+
| 1 | 0 | 0 | 0 |
| 2 | 0 | 0 | 0 |
| 3 | 0 | 0 | 0 |
| 4 | 0 | 0 | 0 |
| 5 | 0 | 0 | 0 |
| 6 | 0 | 0 | 0 |
| 7 | 0 | 0 | 0 |
| 8 | 0 | 0 | 0 |
| 9 | 0 | 0 | 0 |
| 10 | 1 | 15000 | 16500 |
| 11 | 1 | 30000 | 3000 |
| 12 | 3 | 35000 | 51000 |
+-------+---------+--------+-------+ `

最佳答案

我相信您只需要一个 order by 子句。所以你的查询应该是

 SELECT
m.month,
COUNT(l.idloan)AS m_count,
COALESCE(SUM(l.amount),0)AS amount,
COALESCE(SUM(l.total),0)AS total
FROM loan l RIGHT JOIN month m using(month)
GROUP BY m.month
ORDER BY CONVERT(m.month,UNSIGNED INTEGER)

关于mysql - 如何在 MySQL 右连接中对结果进行排序?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/41319084/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com