mysql - 根据 knex 或 mysql 中的日期在一行中获取总贷方和总借方
问题描述
考虑以下架构:
DROP TABLE IF EXISTS balance_sheet;
CREATE TABLE `balance_sheet` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`amount` decimal(8,2) NOT NULL,
`type` enum('debit','credit') NOT NULL DEFAULT 'debit',
`remaining_amount` decimal(8,2) NOT NULL,
`shop_id` bigint(20) unsigned NOT NULL,
`sales_man_id` bigint(20) unsigned NOT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`last_updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`billing_id` bigint(20) unsigned DEFAULT NULL,
PRIMARY KEY (`id`)
);
INSERT INTO balance_sheet VALUES
(124, 40,'debit' ,540, 1,1,'2020-11-27 22:22:08','2020-11-27 22:22:08',82),
(125,290,'credit',250, 1,1,'2020-11-27 22:26:08','2020-11-27 22:26:08',null),
(126,175,'debit' ,675, 3,1,'2020-11-28 05:37:17','2020-11-28 05:37:17',83),
(127,200,'debit' ,500,218,1,'2020-11-28 05:37:40','2020-11-28 05:37:40',84),
(128, 10,'debit' , 15, 2,2,'2020-11-28 08:44:44','2020-11-28 08:44:44',85),
(129,185,'debit' ,385, 20,2,'2020-11-28 08:44:44','2020-11-28 08:44:44',85);
DROP TABLE IF EXISTS salesman;
CREATE TABLE `salesman` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`first_name` varchar(255) NOT NULL,
`last_name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
);
INSERT INTO salesman VALUES
(1,'Abdulmoiz','Ahmer'),
(2,'Umair','Manzoor');
我想根据日期获取业务员每日记录(他在某个日期借了多少,贷了多少,该日期的最终余额是多少)。
必填字段: created_at、credit、debit、first_name、last_name、sales_man_id
到目前为止我做了什么:
knex('balance_sheet as bs')
.join('salesman as sm', 'bs.sales_man_id', 'sm.id')
.select('bs.sales_man_id', 'bs.type', 'bs.created_at', 'sm.first_name', 'sm.last_name', 'bs.created_at')
.sum('amount as amount')
.groupBy('bs.sales_man_id', 'bs.type')
.groupByRaw('DATE(bs.created_at)');
我所做的结果:
"data": [
{
"sales_man_id": 2,
"type": "debit",
"created_at": "2020-11-28T08:44:44.000Z",
"first_name": "Umair",
"last_name": "Manzoor",
"amount": 195
},
{
"sales_man_id": 1,
"type": "credit",
"created_at": "2020-11-27T22:26:08.000Z",
"first_name": "Abdulmoiz",
"last_name": "Ahmer",
"amount": 290
},
{
"sales_man_id": 1,
"type": "debit",
"created_at": "2020-11-27T22:22:08.000Z",
"first_name": "Abdulmoiz",
"last_name": "Ahmer",
"amount": 415
}
]
如您所见,我的查询正在为同一记录生成两行,一行用于当天的总借方,另一行用于当天的总贷方,但我希望它们位于同一行。如下所示:
"data": [
{
"sales_man_id": 2,
"created_at": "2020-11-28T08:44:44.000Z",
"first_name": "Umair",
"last_name": "Manzoor",
"debit": 195,
"credit": 0,
},
{
"sales_man_id": 1,
"created_at": "2020-11-27T22:26:08.000Z",
"first_name": "Abdulmoiz",
"last_name": "Ahmer",
"credit": 290,
"debit": 415
}
]
如果您不能编写一个可以的 knex 查询,请给出一个 sql,我将自己转换它。
解决方案
SELECT s.*
, DATE(b.created_at) created_at
, SUM(CASE WHEN type = 'debit' THEN amount END) debit
, SUM(CASE WHEN type = 'credit' THEN amount END) credit
FROM salesman s
JOIN balance_sheet b
ON b.sales_man_id = s.id
GROUP
BY s.id
, DATE(b.created_at);
+----+------------+-----------+------------+--------+--------+
| id | first_name | last_name | created_at | debit | credit |
+----+------------+-----------+------------+--------+--------+
| 1 | Abdulmoiz | Ahmer | 2020-11-27 | 40.00 | 290.00 |
| 1 | Abdulmoiz | Ahmer | 2020-11-28 | 375.00 | NULL |
| 2 | Umair | Manzoor | 2020-11-28 | 195.00 | NULL |
+----+------------+-----------+------------+--------+--------+
推荐阅读
- c# - C# 不能采用 Windows InstallDate (fe CIM_OperatingSystem)
- python - discord.py BS4 选择具有相同类名的第二个 div
- snowflake-cloud-data-platform - 如何在雪花日期数据类型字段中插入儒略日期
- java - “错误:无法初始化主类,原因:java.lang.NoClassDefFoundError:[LString”
- matlab - 保存和加载 3D 矩阵的方法
- c# - ASP.NET Core 中删除的视图导致生成错误
- php - WP Loop 中的帖子计数不计算用于 div 类的帖子
- python-3.x - 如何将值传递给条目 validatecommand
- javascript - Google-App-Script 条件循环正在运行,但未按预期运行(java 脚本)
- weblogic - 在 Java 11 中使用枚举时出现 UnsupportedOperationException,在 Java 8 中运行良好