SQL

Question

预定表:
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|
+------------+---------+---------+

Answer 1

就是2张单为预定状态的时候,DEP_BF为0
当在01-08转去销售表的时候,02-07收的钱就变为DEP_BF=150.

Answer 2

t5表没数据？最后的sql执行报错

Answer 3

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
 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|
+--------+---------------------+----------+----------+--------+