mysql - sql如何修复MySQL查询?

  显示原文与译文双语对照的内容
119 0

创建表 testTableOne (

Id 类型不是空的AUTO_INCREMENT,

Empno INT NULL,

Name VARCHAR(45) 为空,

Balance DECIMAL(6,2) 为空,

Place VARCHAR(45) 为空,

主密钥( Id ) ) ;

创建表 testTableTwo (

Id 类型不是空的AUTO_INCREMENT,

Date 日期为空,

Empno INT NULL,

Receipt DECIMAL(6,2) 为空,

Payment DECIMAL(6,2) 为空,

Status VARCHAR(45) 为空,

主密钥( Id ) ) ;

插入 testTableOne ( 。EmpnoNameBalancePlace ) 值( 100'约翰','1500','wasinton dc') ;

插入到 testTableOne ( 。EmpnoNameBalancePlace ) 值( 101'joselin','1000','dexcity') 中;

插入到 testTableOne ( 。EmpnoNameBalancePlace ) 值( 102'rusfal','0','donxes') ;插入到 testTableOne ( 。EmpnoNameBalancePlace ) 值( 103'raser','100','versity') ;

插入 testTableOne ( 。EmpnoNameBalancePlace ) 值( 104'','2500','缝合') ;

testTableTwo ( 。DateEmpnoReceiptPaymentStatus ) 值插入到 2016-08-15 1000',100,'','0','确定') ;插入 testTableTwo ( 。DateEmpnoReceiptPaymentStatus ) 值('2016-08-15',100,'0','1000','好') ;

testTableTwo ( 。DateEmpnoReceiptPaymentStatus ) 值中插入('0',101,'','2000','ok') ;

插入到 testTableTwo ( 。DateEmpnoReceiptPaymentStatus ) 值('2016-08-18',103,'100','0','不确定') ;插入 testTableTwo ( 。DateEmpnoReceiptPaymentStatus ) 值('2016-08-19',100,'1500','0','确定') ;插入到 testTableTwo ( 。DateEmpnoReceiptPaymentStatus ) 值('2016-08-20',100,'0','1000','好') ;

然后我需要输出这个

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

Empno名称放置 OB
100 John Wasinton 2400 2000 101 Joselin Dexcity 1000 0 102 Rusfal Donxes 0 0

等 103 104

条件OB仅根据状态确定获取的起始日期小于开始日期

forumula OB=Balance+Receipt-Payment

根据状态确定从日期到日期的条件CB计算

forumula CB=Balance+Receipt-Payment

时间:原作者:0个回答

134 2

使用这个:

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;
原作者:
...