drop table if exists t1;
Create table T1 (id INTEGER NOT NULL AUTO_INCREMENT,total decimal(19,4) default 0,
primary key (id));
insert into T1 value (1,20);
drop table if exists t2;
Create table T2 (uid INTEGER NOT NULL AUTO_INCREMENT,id INTEGER NOT NULL,
Payamount decimal(19,4) default 0,
PaymentDate datetime default null,
primary key (uid));
insert into t2 value (1,1,10,'2011-04-26'),(2,1,10,'2011-04-27');时间是paymentdate between '2011-04-26' and '2011-04-26'26号的查询结果是:
paymentamount DEP_BF
10, 027号的查询结果是:
paymentamount DEP_BF
10, 1026-27号的查询结果是:
paymentamount DEP_BF
20, 0
Create table T1 (id INTEGER NOT NULL AUTO_INCREMENT,total decimal(19,4) default 0,
primary key (id));
insert into T1 value (1,20);
drop table if exists t2;
Create table T2 (uid INTEGER NOT NULL AUTO_INCREMENT,id INTEGER NOT NULL,
Payamount decimal(19,4) default 0,
PaymentDate datetime default null,
primary key (uid));
insert into t2 value (1,1,10,'2011-04-26'),(2,1,10,'2011-04-27');时间是paymentdate between '2011-04-26' and '2011-04-26'26号的查询结果是:
paymentamount DEP_BF
10, 027号的查询结果是:
paymentamount DEP_BF
10, 1026-27号的查询结果是:
paymentamount DEP_BF
20, 0
LEFT JOIN t1 c ON c.id=a1.id
GROUP BY DATE(a1.PaymentDate)
SELECT a1.id,DATE(a1.PaymentDate),SUM(a1.Payamount) AS aa ,SUM(a2.Payamount) AS DEP_BF1 FROM t2 a1 LEFT JOIN t2 a2 ON a1.id=a2.id AND a1.PaymentDate>a2.PaymentDate
WHERE a1.paymentdate BETWEEN '2011-04-26' AND '2011-04-27'
GROUP BY a1.id,DATE(a1.PaymentDate)) aa
LEFT JOIN t1 c ON c.id=aa.id;