我正在建立一个会计表,我需要显示每笔交易的当前余额。
在我的本地环境(Windows 10 + MySQL 5.7.19)中一切正常,但相同的查询在我的 VPS(Linux + MariaDB 10.2.31)和 SQL Fiddle(MySQL 5.6)上给出了奇怪的结果。
首先是代码,然后是示例(SQL Fiddle)。
表:
CREATE TABLE `transaction_types` (
`id` bigint(20) UNSIGNED NOT NULL,
`name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`slug` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`amount` decimal(13,4) NOT NULL DEFAULT 0.0000,
`interests` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
`hourly` tinyint(1) NOT NULL DEFAULT 0,
`salary` tinyint(1) NOT NULL DEFAULT 0,
`income` tinyint(1) NOT NULL DEFAULT 1,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `transaction_types` (`id`, `name`, `slug`, `amount`, `interests`, `hourly`, `salary`, `income`, `created_at`, `updated_at`) VALUES
(1, 'Alquiler', 'alquiler', '43000.0000', NULL, 0, 0, 0, '2021-06-07 09:52:49', '2021-06-10 03:26:17'),
(2, 'Campus Nube', 'campus-nube', '13500.0000', NULL, 0, 0, 0, '2021-06-07 09:52:49', '2021-06-10 03:26:33'),
(3, 'Impuestos', 'impuestos', '8400.0000', NULL, 0, 0, 0, '2021-06-07 09:52:49', '2021-06-10 03:26:47'),
(4, 'Marketing', 'marketing', '0.0000', NULL, 0, 0, 0, '2021-06-07 09:52:49', '2021-06-07 09:52:49'),
(5, 'Otros', 'otros', '0.0000', NULL, 0, 0, 0, '2021-06-07 09:52:49', '2021-06-07 09:52:49'),
(6, 'Sueldo', 'sueldo', '750.0000', NULL, 1, 1, 0, '2021-06-07 09:52:49', '2021-06-10 03:28:49'),
(7, 'Videollamadas', 'videollamadas', '0.0000', NULL, 0, 0, 0, '2021-06-07 09:52:49', '2021-06-07 09:52:49'),
(8, 'Cuota', 'cuota', '0.0000', NULL, 0, 0, 1, '2021-06-07 09:52:49', '2021-06-07 09:52:49'),
(9, 'Derecho a examen', 'derecho-a-examen', '0.0000', NULL, 0, 0, 1, '2021-06-07 09:52:49', '2021-06-07 09:52:49'),
(10, 'Materiales', 'materiales', '0.0000', NULL, 0, 0, 1, '2021-06-07 09:52:49', '2021-06-07 09:52:49'),
(11, 'Matrícula', 'matricula', '0.0000', NULL, 0, 0, 1, '2021-06-07 09:52:49', '2021-06-07 09:52:49'),
(12, 'Otros', 'otros-1', '0.0000', NULL, 0, 0, 1, '2021-06-07 09:52:49', '2021-06-07 09:52:49'),
(13, 'Salida didáctica', 'salida-didactica', '0.0000', NULL, 0, 0, 1, '2021-06-07 09:52:49', '2021-06-07 09:52:49'),
(14, 'Sueldo Secretaria', 'sueldo-secretaria', '25780.0000', NULL, 0, 1, 0, '2021-06-10 03:29:21', '2021-06-10 03:29:21'),
(15, 'Gas natural', 'gas-natural', '400.0000', NULL, 0, 0, 0, '2021-06-29 20:40:16', '2021-06-29 20:40:16');
CREATE TABLE `transactions` (
`id` bigint(20) UNSIGNED NOT NULL,
`transaction_type_id` bigint(20) UNSIGNED NOT NULL,
`payment_id` bigint(20) UNSIGNED DEFAULT NULL,
`salary_id` bigint(20) UNSIGNED DEFAULT NULL,
`division_id` bigint(20) UNSIGNED DEFAULT NULL,
`user_id` bigint(20) UNSIGNED DEFAULT NULL,
`amount` decimal(13,4) NOT NULL DEFAULT 0.0000,
`discount` decimal(13,4) NOT NULL DEFAULT 0.0000,
`interest` decimal(13,4) NOT NULL DEFAULT 0.0000,
`total` decimal(13,4) NOT NULL DEFAULT 0.0000,
`date` date NOT NULL,
`comment` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `transactions` (`id`, `transaction_type_id`, `payment_id`, `salary_id`, `division_id`, `user_id`, `amount`, `discount`, `interest`, `total`, `date`, `comment`, `created_at`, `updated_at`) VALUES
(6, 8, NULL, NULL, 96, 81, '3200.0000', '0.0000', '0.0000', '3200.00', '2021-05-31', 'Cuotas Mayo', '2021-06-10 03:23:38', '2021-06-10 03:23:38'),
(7, 8, NULL, NULL, 96, 80, '3200.0000', '0.0000', '0.0000', '3200.00', '2021-05-31', 'Cuotas Mayo', '2021-06-10 03:23:38', '2021-06-10 03:23:38'),
(8, 8, NULL, NULL, 96, 96, '3200.0000', '0.0000', '0.0000', '3200.00', '2021-05-31', 'Cuotas Mayo', '2021-06-10 03:23:38', '2021-06-10 03:23:38'),
(9, 8, NULL, NULL, 96, 120, '3200.0000', '0.0000', '0.0000', '3200.00', '2021-05-31', 'Cuotas Mayo', '2021-06-10 03:23:38', '2021-06-10 03:23:38'),
(10, 8, NULL, NULL, 96, 31, '3200.0000', '0.0000', '0.0000', '3200.00', '2021-05-31', 'Cuotas Mayo', '2021-06-10 03:23:38', '2021-06-10 03:23:38'),
(11, 8, NULL, NULL, 96, 25, '3200.0000', '0.0000', '0.0000', '3200.00', '2021-05-31', 'Cuotas Mayo', '2021-06-10 03:23:38', '2021-06-10 03:23:38'),
(12, 8, NULL, NULL, 96, 35, '3200.0000', '0.0000', '0.0000', '3200.00', '2021-05-31', 'Cuotas Mayo', '2021-06-10 03:23:38', '2021-06-10 03:23:38'),
(13, 8, NULL, NULL, 96, 46, '3200.0000', '0.0000', '0.0000', '3200.00', '2021-05-31', 'Cuotas Mayo', '2021-06-10 03:23:38', '2021-06-10 03:23:38'),
(14, 8, NULL, NULL, 96, 202, '3200.0000', '0.0000', '0.0000', '3200.00', '2021-05-31', 'Cuotas Mayo', '2021-06-10 03:23:38', '2021-06-10 03:23:38'),
(15, 8, NULL, NULL, 96, 40, '3200.0000', '0.0000', '0.0000', '3200.00', '2021-05-31', 'Cuotas Mayo', '2021-06-10 03:23:38', '2021-06-10 03:23:38'),
(19, 8, NULL, NULL, 95, 111, '5000.0000', '700.0000', '900.0000', '5200.00', '2021-06-06', 'pago cuotas', '2021-06-18 20:50:08', '2021-06-18 20:50:08'),
(20, 8, NULL, NULL, 95, 68, '5000.0000', '700.0000', '900.0000', '5200.00', '2021-06-06', 'pago cuotas', '2021-06-18 20:50:08', '2021-06-18 20:50:08'),
(21, 7, NULL, NULL, NULL, NULL, '2000.0000', '0.0000', '0.0000', '2000.00', '2021-06-04', 'zoom', '2021-06-18 20:50:51', '2021-06-18 20:50:51'),
(36, 14, NULL, 4, NULL, 32, '29780.0000', '0.0000', '0.0000', '29780.00', '2021-06-05', 'aaa', '2021-06-24 16:44:34', '2021-06-24 16:44:34'),
(37, 6, NULL, 5, NULL, 105, '30000.0000', '0.0000', '0.0000', '30000.00', '2021-06-01', NULL, '2021-06-24 16:45:25', '2021-06-24 16:45:25'),
(43, 8, 14, NULL, 95, 63, '5000.0000', '0.0000', '0.0000', '5000.00', '2021-06-01', NULL, '2021-06-24 18:45:19', '2021-06-24 18:45:19'),
(81, 4, NULL, NULL, NULL, NULL, '4000.0000', '0.0000', '0.0000', '4000.00', '2021-06-02', 'asasas', '2021-06-29 18:43:49', '2021-06-29 18:43:49'),
(83, 6, NULL, 15, NULL, 89, '18250.0000', '0.0000', '0.0000', '18250.00', '2021-06-01', NULL, '2021-06-29 19:04:20', '2021-06-29 19:04:20');
询问:
SET @balance = 0;
SET @temp = 0;
SELECT SUM(IF(`income`, +`total`, -`total`)) INTO @balance
FROM (
SELECT `income`, `total`
FROM `transactions`
INNER JOIN `transaction_types` ON `transactions`.`transaction_type_id` = `transaction_types`.`id`
ORDER BY
`date` desc,
`transactions`.`created_at` desc,
`transactions`.`id` asc
LIMIT 9223372036854775807 OFFSET 0
) AS `sub`;
SELECT
`transactions`.`id`,
`transactions`.`date`,
`transactions`.`total`,
`transaction_types`.`name` AS `typeName`,
`transaction_types`.`income`,
@balance := (@balance - @temp) AS `balance`,
@temp := IF(`income`, +`total`, -`total`) AS `temp`
FROM `transactions`
INNER JOIN `transaction_types` on `transactions`.`transaction_type_id` = `transaction_types`.`id`
ORDER BY
`date` desc,
`transactions`.`created_at` desc,
`transactions`.`id` asc;
这是预期的输出(本地):
ID | 日期 | 收入 | 全部的 | 平衡 | 温度 |
---|---|---|---|---|---|
19 | 2021-06-06 | 1 | 5200.0000 | -36630.0000 | 5200.0000 |
20 | 2021-06-06 | 1 | 5200.0000 | -41830.0000 | 5200.0000 |
36 | 2021-06-05 | 0 | 29780.0000 | -47030.0000 | -29780.0000 |
21 | 2021-06-04 | 0 | 2000.0000 | -17250.0000 | -2000.0000 |
81 | 2021-06-02 | 0 | 4000.0000 | -15250.0000 | -4000.0000 |
83 | 2021-06-01 | 0 | 18250.0000 | -11250.0000 | -18250.0000 |
43 | 2021-06-01 | 1 | 5000.0000 | 7000.0000 | 5000.0000 |
37 | 2021-06-01 | 0 | 30000.0000 | 2000.0000 | -30000.0000 |
6 | 2021-05-31 | 1 | 3200.0000 | 32000.0000 | 3200.0000 |
7 | 2021-05-31 | 1 | 3200.0000 | 28800.0000 | 3200.0000 |
8 | 2021-05-31 | 1 | 3200.0000 | 25600.0000 | 3200.0000 |
9 | 2021-05-31 | 1 | 3200.0000 | 22400.0000 | 3200.0000 |
10 | 2021-05-31 | 1 | 3200.0000 | 19200.0000 | 3200.0000 |
11 | 2021-05-31 | 1 | 3200.0000 | 16000.0000 | 3200.0000 |
12 | 2021-05-31 | 1 | 3200.0000 | 12800.0000 | 3200.0000 |
13 | 2021-05-31 | 1 | 3200.0000 | 9600.0000 | 3200.0000 |
14 | 2021-05-31 | 1 | 3200.0000 | 6400.0000 | 3200.0000 |
15 | 2021-05-31 | 1 | 3200.0000 | 3200.0000 | 3200.0000 |
这就是我得到的(服务器):
ID | 日期 | 全部的 | 收入 | 平衡 | 温度 |
---|---|---|---|---|---|
19 | 2021-06-06 | 5200.0000 | 1 | -14380.0000 | 5200.0000 |
20 | 2021-06-06 | 5200.0000 | 1 | -19580.0000 | 5200.0000 |
36 | 2021-06-05 | 29780.0000 | 0 | -29780.0000 | -29780.0000 |
21 | 2021-06-04 | 2000.0000 | 0 | 15620.0000 | -2000.0000 |
81 | 2021-06-02 | 4000.0000 | 0 | -36630.0000 | -4000.0000 |
83 | 2021-06-01 | 18250.0000 | 0 | -2630.0000 | -18250.0000 |
43 | 2021-06-01 | 5000.0000 | 1 | -24780.0000 | 5000.0000 |
37 | 2021-06-01 | 30000.0000 | 0 | -32630.0000 | -30000.0000 |
6 | 2021-05-31 | 3200.0000 | 1 | 17620.0000 | 3200.0000 |
7 | 2021-05-31 | 3200.0000 | 1 | 14420.0000 | 3200.0000 |
8 | 2021-05-31 | 3200.0000 | 1 | 11220.0000 | 3200.0000 |
9 | 2021-05-31 | 3200.0000 | 1 | 8020.0000 | 3200.0000 |
10 | 2021-05-31 | 3200.0000 | 1 | 4820.0000 | 3200.0000 |
11 | 2021-05-31 | 3200.0000 | 1 | 1620.0000 | 3200.0000 |
12 | 2021-05-31 | 3200.0000 | 1 | -1580.0000 | 3200.0000 |
13 | 2021-05-31 | 3200.0000 | 1 | -4780.0000 | 3200.0000 |
14 | 2021-05-31 | 3200.0000 | 1 | -7980.0000 | 3200.0000 |
15 | 2021-05-31 | 3200.0000 | 1 | -11180.0000 | 3200.0000 |
typeName
如果我在上有列,SQL Fiddle会给我相同的结果SELECT
,如果我删除它,结果会更改:SQL Fiddle。可能跟这个有关JOIN
?
如果您从下到上阅读第一个表,您将看到temp
列(已total
签名)正在添加到balance
. -36630.0000 是总余额,两个表都有这个数字,但这个数字应该是第一个。
如果我删除一些记录,有时查询会给我预期的结果。
我不知道是怎么回事。