mysql - MYSQL 选择 COLUMN(CONDITION) WHERE 条件

问题描述

我正在尝试对我的列（AMOUNT）求和，其中值都是正数（+）作为我的 PAYABLES，并再次求和同一列（AMOUNT），其中值都是负数（-amount）作为 PAYMENTSMADE。然后我想比较 PAYMENTSMADE < PAYABLES 学生有余额还是 PAYMENTSMADE > PAYABLES 学生是否多付。

`SELECT
studentledger.ledgerno,
SUM(studentledger.amount(ALL POSITIVE AMOUNT)) AS payables
Sum(studentledger.amount(ALL NEGATIVE AMOUNT)) AS paymentsmade
FROM
studentledger
WHERE
studentledger.period =  '1'
GROUP BY
studentledger.ledgerno

数据库结构

CREATE TABLE IF NOT EXISTS `studentledger` (
  `ledgerno` int(11) NOT NULL 

  AUTO_INCREMENT,
  `sourcedoc` int(11) NOT NULL,
  `student` int(11) NOT NULL,
  `type` varchar(11) 

NOT NULL,
 `period` int(11) NOT NULL,
 `amount` decimal(11,2) NOT NULL DEFAULT '0.00',
 `date` 

date NOT NULL,
 PRIMARY KEY (ledgerno)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 `

样本记录

INSERT INTO `studentledger` 

(`ledgerno`, `sourcedoc`, `student`, `type`, `period`, `amount`, `date`) 
VALUES

(3644, 144444, 164, 'A', 1, '18080.67', '2019-02-08'),
(1462, 921020, 164, 'R', 1, '-5000.00', '2019-02-08'),
(1465, 921265, 164, 'R', 1, '-5000.00', '2019-02-08'),
(1467, 921592, 164, 'R', 1, '-3000.00', '2019-02-08'),
(1212, 121125, 164, 'SA', 1, '42.00', '2019-02-08'),
(6333, 916177, 164, 'R', 1, '-5122.67', '2019-02-12'),
(1111, 920001, 152, 'A', 1, '18696.95', '2019-02-13'),
(1023, 929258, 152, 'R', 1, '-2000.00', '2019-02-13'),
(1133, 929267, 152, 'R', 1, '-3500.00', '2019-02-13'),
(1211, 917588, 152, 'R', 1, '-500.00', '2019-02-13'),
(1365, 932504, 152, 'SA', 1, '-96.00', '2019-02-13'),
(1478, 920007, 152, 'R', 1, '-4000.00', '2019-02-13'),
(1599, 922291, 152, 'R', 1, '-5000.00', '2019-02-13'),
(1600, 932618, 152, 'R', 1, '-600.00', '2019-02-13'),
(1743, 932752, 152, 'R', 1, '-2692.95', '2019-02-13'),
(1630, 932618, 152, 'R', 1, '-400.00', '2019-02-13'),
(1610, 932618, 152, 'R', 1, '-100.00', '2019-02-13');

如果我想显示带有 BALANCE id 的记录，我试图得到最终结果

 `WHERE PAYABLES-PAYMENTSMADE != 0 //with remaining balance
 OR
  WHERE PAYABLES-PAYMENTSMADE < 0 //Overpayment`

标签： mysql

您可以简单地SUM为给定学生的所有金额获得余额。因为您使用的是聚合函数，所以您必须检查HAVING子句中的值：

SELECT ledgerno,
       SUM(CASE WHEN amount > 0 THEN amount ELSE 0 END) AS payables,
       -SUM(CASE WHEN amount < 0 THEN amount ELSE 0 END) AS paymentsmade,
       SUM(amount) AS balance
FROM studentledger
WHERE period = 1
GROUP BY ledgerno
HAVING balance != 0

输出（用于您的样本数据）：

ledgerno    payables    paymentsmade    balance
1023        0           2000            -2000
1111        18696.95    0               18696.95
1133        0           3500            -3500
1211        0           500             -500
1212        42          0               42
1365        0           96              -96
1462        0           5000            -5000
1465        0           5000            -5000
1467        0           3000            -3000
1478        0           4000            -4000
1599        0           5000            -5000
1600        0           600             -600
1610        0           100             -100
1630        0           400             -400
1743        0           2692.95         -2692.95
3644        18080.67    0               18080.67
6333        0           5122.67         -5122.67

dbfiddle 上的演示

mysql - MYSQL 选择 COLUMN(CONDITION) WHERE 条件

问题描述

解决方案

推荐阅读