预定表:
CREATE TABLE `t3` (
`MenuID` int(11) NOT NULL default '0',
`Total` decimal(19,4) default '0.0000',
`TransStatus` int(11) default '0',
`BusinessDate` datetime default NULL
);
insert into t3 value (182,100,2,'2013-01-07');
insert into t3 value(184,100,7,'2013-01-07');
CREATE TABLE `t2` (
`PaymentID` int(11) NOT NULL default '0',
`MenuID` int(11) NOT NULL default '0',
`PaymentAmount` decimal(19,4) NOT NULL default '0.0000',
`BusinessDate` datetime default NULL
);
insert into t2 value (176,182,50,'2013-01-07');
insert into t2 value(177,182,50,'2013-01-08');
insert into t2 value(178,184,100,'2013-01-07');销售表:
CREATE TABLE `t4` (
`MenuID` int(11) NOT NULL default '0',
`Total` decimal(19,4) default '0.0000',
`TransStatus` int(11) default '0',
`BusinessDate` datetime default NULL
)
insert into t4 value (182,100,2,'2013-01-08');
insert into t4 value(184,100,7,'2013-01-08');CREATE TABLE `t5` (
`PaymentID` int(11) NOT NULL default '0',
`MenuID` int(11) NOT NULL default '0',
`PaymentAmount` decimal(19,4) NOT NULL default '0.0000',
`BusinessDate` datetime default NULL
);
insert into t2 value (176,182,50,'2013-01-07');
insert into t2 value(177,182,50,'2013-01-08');
insert into t2 value(178,184,100,'2013-01-07');'*********************************************
Select sum(total),sum(aa),sum(DEP_BF) as DEP_BF
From (SELECT aa.MenuID, aa.BusinessDate,C.total,SUM(aa) as aa,IF(DEP_BF1 IS NULL,0,MAX(c.total)-SUM(aa)) AS DEP_BF
FROM ( SELECT a1.menuid as MenuID,a1.businessdate as BusinessDate,SUM(a1.Paymentamount) AS aa ,
(Select sum(a2.Paymentamount) FROM t2 a2 where a2.businessDate<a1.businessdate
AND A1.MENUID=A2.MENUID GROUP BY A2.MENUID) AS DEP_BF1 FROM t2 a1
WHERE a1.businessdate Between '2013-01-07' And '2013-01-07' GROUP BY a1.menuid,a1.businessdate) aa
LEFT JOIN t3 c ON c.menuid=aa.menuid Where c.TransStatus=2 Group by c.menuid)a没有转去销售表的时候,数据是正确的;
'2013-01-07':
+------------+---------+---------+
| sum(total) | sum(aa) | DEP_BF |
+------------+---------+---------+
| 100.0000 | 50.0000 | 0.0000 |
+------------+---------+---------+'2013-01-08':
+------------+---------+---------+
| sum(total) | sum(aa) | DEP_BF |
+------------+---------+---------+
| 100.0000 | 50.0000 | 50.0000 |
+------------+---------+---------+当在'2013-01-08'时,把预定表的数据转到销售表的时候,
'2013-01-07':
+------------+---------+---------+
| sum(total) | sum(aa) | DEP_BF |
+------------+---------+---------+
| 200.0000 |150.0000 | 0.0000 |
+------------+---------+---------+'2013-01-08':
+------------+---------+---------+
| sum(total) | sum(aa) | DEP_BF |
+------------+---------+---------+
| 100.0000 | 50.0000 | 50.0000 |
+------------+---------+---------+正确的应该是:
'2013-01-07':
+------------+---------+---------+
| sum(total) | sum(aa) | DEP_BF |
+------------+---------+---------+
| 150.0000 | 150.0000 |0.0000 |
+------------+---------+---------+'2013-01-08'(150是在01-08号之前收的算DEP_BF):
+------------+---------+---------+
| sum(total) | sum(aa) | DEP_BF |
+------------+---------+---------+
| 200.0000 | 50.0000 | 150.0000|
+------------+---------+---------+
解决方案 »
- MySQL Insert 效率问题请教
- 请问如果CPU是64拉的,那么MYSQL5.0的64位版比32位版快吗?谢谢
- how to read data from mysql except "select * from mysql.database" sentens?
- MySQL权威指南(原书第2版)和 MySQL权威指南(第二版) 是同一本书吗?
- 存储图片问题?
- MYSQL备份问题
- mysql一定要通过命令行模式来建立一个新表么?
- Ubuntu下Mysql安装
- Can't send long data for non-string/non-binary data types (parameter: 0)
- 探讨一个关于数据库的操作问题,求解答
- 数据库性能测试与对比
- 关联表的删改,大家是怎么处理的?
当在01-08转去销售表的时候,02-07收的钱就变为DEP_BF=150.
FROM ( SELECT a1.menuid as MenuID,a1.businessdate as BusinessDate,SUM(a1.Paymentamount) AS aa,
(Select sum(a2.Paymentamount) FROM t2 a2 where a2.businessDate<a1.businessdate
AND A1.MENUID=A2.MENUID GROUP BY A2.MENUID) AS DEP_BF1
FROM t2 a1
WHERE a1.businessdate Between '2013-01-07' And '2013-01-07'
GROUP BY a1.menuid,a1.businessdate) aa
Inner JOIN t3 c
ON c.menuid=aa.menuid Where c.TransStatus=2 Group by c.menuid
'************
上面这个语句需要改更新t3的transstatus全部为2
查询7号:
+--------+---------------------+----------+----------+--------+
| MenuID | BusinessDate | total | aa | DEP_BF |
+--------+---------------------+----------+----------+--------+
| 182 | 2013-01-07 00:00:00 | 100.0000 | 50.0000 | 0 |
| 184 | 2013-01-07 00:00:00 | 100.0000 | 100.0000 | 0 |
+--------+---------------------+----------+----------+--------+
查询8号的数据要为:
+--------+---------------------+----------+----------+--------+
| MenuID | BusinessDate | total | aa | DEP_BF |
+--------+---------------------+----------+----------+--------+
| 182 | 2013-01-08 00:00:00 | 100.0000 | 50.0000 |50.0000 |
| 184 | 2013-01-08 00:00:00 | 100.0000 | 0.0000 |100.0000|
+--------+---------------------+----------+----------+--------+