创建表testTableOne

Id INT NOT NULL AUTO_INCREMENT,

Empno INT NULL,

Name VARCHAR(45)NULL,

Balance DECIMAL(6,2)NULL,

Place VARCHAR(45)NULL,

主键(Id));

创建表testTableTwo

Id INT NOT NULL AUTO_INCREMENT,

Date DATE NULL,

Empno INT NULL,

Receipt DECIMAL(6,2)NULL,

Payment DECIMAL(6,2)NULL,

Status VARCHAR(45)NULL,

主键(Id));

插入testtableoneEmpnoNameBalancePlace)值(100,'John','1500','Wasinton DC');

插入testtableoneEmpnoNameBalancePlace)值(101,'Joselin','1000','Dexcity');

插入testtableoneEmpnoNameBalancePlace)值(102,'Rusfal','0','Donxes');
插入testtableoneEmpnoNameBalancePlace)值(103,'Raser','100','versity');

插入testtableoneEmpnoNameBalancePlace)值(104,'rse','2500','sew');

插入testTableTwoDateEmpnoReceiptPaymentStatus)值('2016-08-15',100,'1000','0', '好');
插入testTableTwoDateEmpnoReceiptPaymentStatus)值('2016-08-15',100,'0','1000', '好');

插入testTableTwoDateEmpnoReceiptPaymentStatus)值('2016-08-17',101,'0','2000', '好');

插入testTableTwoDateEmpnoReceiptPaymentStatus)值('2016-08-18',103,'100','0', '不好');
插入testTableTwoDateEmpnoReceiptPaymentStatus)值('2016-08-19',100,'1500','0', '好');
插入testTableTwoDateEmpnoReceiptPaymentStatus)值('2016-08-20',100,'0','1000', '好');

然后我需要输出

开始日期'2016-08-18'结束日期'2016-08-20'

Empno名称地方OB CB
100 John Wasinton DC 2400 2000
101何塞琳·敏捷1000 0
102鲁斯法尔·唐克斯0 0

等等
103
104



条件
OB少的数量少于开始日期(基于状态)OK仅

论坛
OB =余额+收款

条件
根据状态每天进行的CB计算仅限OK

论坛
CB =余额+收款

最佳答案

用这个:

SELECT a.id,a.name,sum(amount1)as amount1,sum(Receipt) as Receipt,sum(payment) as payment,
sum(amount1)+sum(Receipt)-sum(payment) as Total,nameid,b.name
FROM test.mas as a left join test.trans as b on a.id=b.nameid GROUP BY a.id;

关于mysql - 如何修复MySQL查询?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/38734443/

10-09 08:27