CREATE TABLE `DepositDetail` (
`DepositID` INTEGER NOT NULL AUTO_INCREMENT,
`CustomerNumber` INTEGER NOT NULL DEFAULT 0,
`DepositType` varchar(16) ,
`Amount` DECIMAL(19,4),
`Description` VARCHAR(120),
`CashierID` INTEGER,
`TransDate` Datetime,
`TransStatus` INTEGER ,
`TransID` INTEGER DEFAULT 0,
`OrderID` INTEGER DEFAULT 0,
`PaymentType` VARCHAR(10),
`TermID` INTEGER,
`DepositGroupID` INTEGER,
PRIMARY KEY (`DepositID`),
INDEX `CustomerID`(`CustomerNumber`),
index `TransDate`(`TransDate`)
)ENGINE = InnoDB DEFAULT CHARSET=utf8;Create Table `DepositGroup` (
`DepositGroupID` INTEGER NOT NULL ,
`DepositName` varchar(45),
PRIMARY KEY (`DepositGroupID`)
)ENGINE = InnoDB DEFAULT CHARSET=utf8;Insert into DepositDetail(DepositID,CustomerNumber,DepositType,Amount,Description,CashierID,TransDate,TransStatus,TransID,OrdreID,PaymentType,TermID,DepositGroupID) value
(1, 'HK-00001', 'Cash', 800.0000, 'Normal', 1, '2010-12-14 12:44:36', 1, 0, 0, '', 1, 1),
(2, 'HK-00001', '', 310.0000, 'Paid Trans ', 1, '2010-12-14 12:44:45', 2, 1, 0, '', 1, 1),
(3, 'HK-00001', 'Cash', 500.0000, 'Fee', 1, '2010-12-14 12:45:20', 1, 0, 0, '', 1, 2),
(4, 'HK-00001', '', 50.0000, 'Paid Trans ', 1, '2010-12-14 12:46:00', 2, 3, 0, '', 1, 1),
(5, 'HK-00001', '', 275.0000, 'Paid Trans ', 1, '2010-12-14 12:46:00', 2, 3, 0, '', 1, 2),
(6, 'HK-00001', '', 22.5000, 'Paid Trans ', 1, '2010-12-14 14:01:35', 2, 4, 0, '', 1, 1),
(7, 'HK-00001', '', 25.0000, 'Paid Trans ', 1, '2010-12-14 14:02:02', 2, 5, 0, '', 1, 1),
(8, 'HK-00002', 'VISA ', 100.0000, 'Fee', 1, '2010-12-14 14:49:42', 1, 0, 0, '', 1, 2),
(9, 'HK-00002', 'Cash', 100.0000, 'Normal', 1, '2010-12-14 14:50:04', 1, 0, 0, '', 1, 1),
(10, 'HK-00001', 'EPS ', 50.0000, 'Normal', 1, '2010-12-14 14:50:28', 1, 0, 0, '', 1, 1),
(11, 'HK-00001', '', 80.4000, 'Paid Trans ', 1, '2010-12-14 15:26:39', 2, 8, 0, '', 1, 1),
(12, 'HK-00001', '', 200.0000, 'Paid Trans ', 1, '2010-12-14 15:27:59', 2, 9, 0, '', 1, 1),
(13, 'HK-00001', '', 185.0000, 'Paid Trans ', 1, '2010-12-14 15:27:59', 2, 9, 0, '', 1, 2),
(14, 'HK-00001', 'Cash', 50.0000, 'Normal', 1, '2010-12-15 09:15:50', 1, 0, 0, '', 1, 1),
(15, 'HK-00001', 'Cash', 500.0000, 'Normal', 1, '2010-12-15 09:46:53', 1, 0, 0, '', 1, 1),
(16, 'HK-00001', '', 32.8000, 'Paid Trans ', 1, '2010-12-15 11:12:20', 2, 10, 0, '', 1, 1),
(17, 'HK-00001', 'MASTERS ', 50.0000, 'Normal', 1, '2010-12-15 11:13:24', 1, 0, 0, '', 1, 1),
(18, 'HK-00001', 'VISA ', 50.0000, 'Normal', 1, '2010-12-15 11:13:34', 1, 0, 0, '', 1, 1),
(19, 'HK-00001', '', 267.0000, 'Redeem Deposit', 1, '2010-12-15 14:32:58', 2, 13, 0, '', 1, 1),
(20, 'HK-00001', 'Cash', 200.0000, 'Redeem Deposit', 1, '2010-12-15 14:35:47', 2, 0, 0, '', 1, 1),
(21, 'HK-00001', 'Cash', 100.0000, 'Top-up Deposit', 1, '2010-12-15 14:49:56', 1, 0, 0, '', 1, 1),
(22, 'HK-00001', 'Cash', 100.0000, 'Redeem Deposit', 1, '2010-12-15 14:50:05', 2, 0, 0, '', 1, 1),
(23, 'HK-00002', 'Cash', 100.0000, 'Top-up Normal', 1, '2010-12-15 14:54:21', 1, 0, 0, '', 1, 1),
(24, 'HK-00002', 'Cash', 100.0000, 'Return Normal', 1, '2010-12-15 14:55:07', 3, 0, 0, '', 1, 1),
(25, 'HK-00001', 'Cash', 100.0000, 'Return Customer Deposit', 1, '2010-12-15 14:58:55', 3, 0, 0, '', 1, 1),
(26, 'HK-00001', 'Cash', 200.0000, 'Top-up Customer Deposit', 1, '2010-12-15 14:59:05', 1, 0, 0, '', 1, 1),
(27, 'HK-00002', 'Cash', 100.0000, 'Return School Fee', 1, '2010-12-15 15:15:15', 3, 0, 0, '', 1, 2),
(28, 'HK-00001', 'Cash', 50.0000, 'top-up Deposit', 1, '2010-12-15 15:59:59', 1, 0, 0, '', 1, 1),
(29, 'HK-00001', 'Cash', 50.0000, 'top-up Deposit', 1, '2010-12-15 16:00:43', 1, 0, 0, '', 1, 1),
(30, 'HK-00008', 'Cash', 100.0000, 'Top-up Deposit', 1, '2010-12-15 16:01:04', 1, 0, 0, '', 1, 1),
(31, 'HK-00008', 'Cash', 100.0000, 'Return Deposit', 1, '2010-12-15 16:01:13', 3, 0, 0, '', 1, 1) insert into depositgroup(depositgroupid,depositname) value (1,'Deposit'),(2,'Fee')SELECT A.DepositID,A.TransDate,SUM(if(e.TransStatus=1,E.AMOUNT,-1*E.AMOUNT)) AS Balance,
c.CustomerName,A.Description,if(A.TransID=0,"",A.transid) as Receipt,D.operatorName,
if(A.DepositType='Cash',if(A.TransStatus=1,1,-1)*A.Amount,"") as Cash,
if(A.DepositType='VISA',if(A.TransStatus=1,1,-1)*A.Amount,"") as visa,
if(A.DepositType='EPS',if(A.TransStatus=1,1,-1)*A.Amount,"") as EPS,
if(A.DepositGroupID=1,if(A.TransStatus=1,1,-1)*A.Amount,"") as Group1,
if(A.DepositGroupID=2,if(A.TransStatus=1,1,-1)*A.Amount,"") as Group2,
if(A.DepositGroupID=3,if(A.TransStatus=1,1,-1)*A.Amount,"") as Group3
FROM depositdetail A INNER join DepositGroup B on A.DepositGroupID=B.DepositGroupID
LEFT Join Customers C on C.CustomerNumber=A.CustomerNumber
LEFT Join Operators D on D.OperatorID=A.CashierID
LEFT Join DepositDetail E on A.Customernumber=E.CustomerNUmber and A.DepositGroupID=E.DepositGroupID
where A.TransDate>=E.TransDATE AND A.CustomerNumber='HK-00001'
GROUP BY A.TransDate,A.DepositGroupID,A.CustomerNumber
Order by A.CustomerNumber,A.DepositGroupID,A.TransDateResult:
1, '2010-12-14 12:44:36', 800.0000, 'freda', 'Normal', '', 'SysAdm', '800.0000', '', '', '800.0000', '', ''
2, '2010-12-14 12:44:45', 490.0000, 'freda', 'Paid Trans ', '1', 'SysAdm', '', '', '', '-310.0000', '', ''
4, '2010-12-14 12:46:00', 440.0000, 'freda', 'Paid Trans ', '3', 'SysAdm', '', '', '', '-50.0000', '', ''
6, '2010-12-14 14:01:35', 417.5000, 'freda', 'Paid Trans ', '4', 'SysAdm', '', '', '', '-22.5000', '', ''
7, '2010-12-14 14:02:02', 392.5000, 'freda', 'Paid Trans ', '5', 'SysAdm', '', '', '', '-25.0000', '', ''
10, '2010-12-14 14:50:28', 442.5000, 'freda', 'Normal', '', 'SysAdm', '', '', '50.0000', '50.0000', '', ''
11, '2010-12-14 15:26:39', 362.1000, 'freda', 'Paid Trans ', '8', 'SysAdm', '', '', '', '-80.4000', '', ''
12, '2010-12-14 15:27:59', 162.1000, 'freda', 'Paid Trans ', '9', 'SysAdm', '', '', '', '-200.0000', '', ''
14, '2010-12-15 09:15:50', 212.1000, 'freda', 'Normal', '', 'SysAdm', '50.0000', '', '', '50.0000', '', ''
15, '2010-12-15 09:46:53', 712.1000, 'freda', 'Normal', '', 'SysAdm', '500.0000', '', '', '500.0000', '', ''
16, '2010-12-15 11:12:20', 679.3000, 'freda', 'Paid Trans ', '10', 'SysAdm', '', '', '', '-32.8000', '', ''
17, '2010-12-15 11:13:24', 729.3000, 'freda', 'Normal', '', 'SysAdm', '', '', '', '50.0000', '', ''
18, '2010-12-15 11:13:34', 779.3000, 'freda', 'Normal', '', 'SysAdm', '', '50.0000', '', '50.0000', '', ''
19, '2010-12-15 14:32:58', 512.3000, 'freda', 'Redeem Deposit', '13', 'SysAdm', '', '', '', '-267.0000', '', ''
20, '2010-12-15 14:35:47', 312.3000, 'freda', 'Redeem Deposit', '', 'SysAdm', '-200.0000', '', '', '-200.0000', '', ''
21, '2010-12-15 14:49:56', 412.3000, 'freda', 'Top-up Deposit', '', 'SysAdm', '100.0000', '', '', '100.0000', '', ''
22, '2010-12-15 14:50:05', 312.3000, 'freda', 'Redeem Deposit', '', 'SysAdm', '-100.0000', '', '', '-100.0000', '', ''
25, '2010-12-15 14:58:55', 212.3000, 'freda', 'Return Customer Deposit', '', 'SysAdm', '-100.0000', '', '', '-100.0000', '', ''
26, '2010-12-15 14:59:05', 412.3000, 'freda', 'Top-up Customer Deposit', '', 'SysAdm', '200.0000', '', '', '200.0000', '', ''
28, '2010-12-15 15:59:59', 462.3000, 'freda', 'top-up Deposit', '', 'SysAdm', '50.0000', '', '', '50.0000', '', ''
29, '2010-12-15 16:00:43', 512.3000, 'freda', 'top-up Deposit', '', 'SysAdm', '50.0000', '', '', '50.0000', '', ''
3, '2010-12-14 12:45:20', 500.0000, 'freda', 'Fee', '', 'SysAdm', '500.0000', '', '', '', '500.0000', ''
5, '2010-12-14 12:46:00', 225.0000, 'freda', 'Paid Trans ', '3', 'SysAdm', '', '', '', '', '-275.0000', ''
13, '2010-12-14 15:27:59', 40.0000, 'freda', 'Paid Trans ', '9', 'SysAdm', '', '', '', '', '-185.0000', ''上面Group1,Group2,Group3的名字能不能根据DepositGroup里面的DepositName对应的DepositGroupID来显示...
楼主能不能提供完整的表和需要的结果?
静态的SQL语句当中不能。除非弄成动态执行的SQL语句。
if(A.DepositType='Cash',if(A.TransStatus=1,1,-1)*A.Amount,"") as Cash,
if(A.DepositType='VISA',if(A.TransStatus=1,1,-1)*A.Amount,"") as visa,
if(A.DepositType='EPS',if(A.TransStatus=1,1,-1)*A.Amount,"") as EPS,
if(A.DepositGroupID=1,if(A.TransStatus=1,1,-1)*A.Amount,"") as Group1,
if(A.DepositGroupID=2,if(A.TransStatus=1,1,-1)*A.Amount,"") as Group2,
if(A.DepositGroupID=3,if(A.TransStatus=1,1,-1)*A.Amount,"") as Group3这6个怎么生成动态的? 刚研究到静态的.
if(A.DepositType='Cash',if(A.TransStatus=1,1,-1)*A.Amount,"") as Cash,
if(A.DepositType='VISA',if(A.TransStatus=1,1,-1)*A.Amount,"") as visa,
if(A.DepositType='EPS',if(A.TransStatus=1,1,-1)*A.Amount,"") as EPS,
if(A.DepositGroupID=1,if(A.TransStatus=1,1,-1)*A.Amount,"") as Group1,
if(A.DepositGroupID=2,if(A.TransStatus=1,1,-1)*A.Amount,"") as Group2,
if(A.DepositGroupID=3,if(A.TransStatus=1,1,-1)*A.Amount,"") as Group3
FROM depositdetail A INNER join DepositGroup B on A.DepositGroupID=B.DepositGroupID
LEFT Join DepositDetail E on A.Customernumber=E.CustomerNUmber and A.DepositGroupID=E.DepositGroupID
where A.TransDate>=E.TransDATE AND A.CustomerNumber='HK-00001'
GROUP BY A.TransDate,A.DepositGroupID,A.CustomerNumber
Order by A.CustomerNumber,A.DepositGroupID,A.TransDate这样的话,就不需要其他表了。
http://blog.csdn.net/ACMAIN_CHM/archive/2009/06/19/4283943.aspx
SELECT A.DepositID,A.TransDate,SUM(if(e.TransStatus=1,E.AMOUNT,-1*E.AMOUNT)) AS Balance,A.Description,if(A.TransID=0,"",A.transid) as Receipt,
if(A.DepositType='Cash',if(A.TransStatus=1,1,-1)*A.Amount,"") as Cash,
if(A.DepositType='VISA',if(A.TransStatus=1,1,-1)*A.Amount,"") as visa,
if(A.DepositType='EPS',if(A.TransStatus=1,1,-1)*A.Amount,"") as EPS,
if(A.DepositGroupID=1,if(A.TransStatus=1,1,-1)*A.Amount,"") as Group1,
if(A.DepositGroupID=2,if(A.TransStatus=1,1,-1)*A.Amount,"") as Group2,
if(A.DepositGroupID=3,if(A.TransStatus=1,1,-1)*A.Amount,"") as Group3
FROM depositdetail A INNER join DepositGroup B on A.DepositGroupID=B.DepositGroupID
LEFT Join DepositDetail E on A.Customernumber=E.CustomerNUmber and A.DepositGroupID=E.DepositGroupID
where A.TransDate>=E.TransDATE AND A.CustomerNumber='HK-00001'
GROUP BY A.TransDate,A.DepositGroupID,A.CustomerNumber
Order by A.CustomerNumber,A.DepositGroupID,A.TransDate把语句改成这样就可以测试了...
如果前者 ,要用MYSQL -U -P<123.SQL还有,你建表、插入记录的SQL语句要测试一下,CustomerNumber类型不符
咋搞?
mysql -uroot -p -h xx:xx:xx:xx < 1.sql
T)) AS Balance,A.Description,if(A.TransID=0,"",A.transid) as Receipt,
-> if(A.DepositType='Cash',if(A.TransStatus=1,1,-1)*A.Amount,"") as Cash,
-> if(A.DepositType='VISA',if(A.TransStatus=1,1,-1)*A.Amount,"") as visa,
-> if(A.DepositType='EPS',if(A.TransStatus=1,1,-1)*A.Amount,"") as EPS,
-> if(A.DepositGroupID=1,if(A.TransStatus=1,1,-1)*A.Amount,"") as Group1,
-> if(A.DepositGroupID=2,if(A.TransStatus=1,1,-1)*A.Amount,"") as Group2,
-> if(A.DepositGroupID=3,if(A.TransStatus=1,1,-1)*A.Amount,"") as Group3
-> FROM depositdetail A INNER join DepositGroup B on A.DepositGroupID=B.Depo
sitGroupID
-> LEFT Join DepositDetail E on A.Customernumber=E.CustomerNUmber and A.Depo
sitGroupID=E.DepositGroupID
-> where A.TransDate>=E.TransDATE AND A.CustomerNumber='HK-00001'
-> GROUP BY A.TransDate,A.DepositGroupID,A.CustomerNumber
-> Order by A.CustomerNumber,A.DepositGroupID,A.TransDate;
+-----------+---------------------+----------+-------------------------+---------+-----------+----------+---------+-----------+-----------+--------+
| DepositID | TransDate | Balance | Description | Receipt | Cash | visa | EPS | Group1 | Group2 | Group3 |
+-----------+---------------------+----------+-------------------------+---------+-----------+----------+---------+-----------+-----------+--------+
| 1 | 2010-12-14 12:44:36 | 800.0000 | Normal | | 800.0000 | | | 800.0000 | | |
| 2 | 2010-12-14 12:44:45 | 490.0000 | Paid Trans | 1 | | | | -310.0000 | | |
| 4 | 2010-12-14 12:46:00 | 440.0000 | Paid Trans | 3 | | | | -50.0000 | | |
| 6 | 2010-12-14 14:01:35 | 417.5000 | Paid Trans | 4 | | | | -22.5000 | | |
| 7 | 2010-12-14 14:02:02 | 392.5000 | Paid Trans | 5 | | | | -25.0000 | | |
| 9 | 2010-12-14 14:50:04 | 492.5000 | Normal | | 100.0000 | | | 100.0000 | | |
| 10 | 2010-12-14 14:50:28 | 542.5000 | Normal | | | | 50.0000 | 50.0000 | | |
| 11 | 2010-12-14 15:26:39 | 462.1000 | Paid Trans | 8 | | | | -80.4000 | | |
| 12 | 2010-12-14 15:27:59 | 262.1000 | Paid Trans | 9 | | | | -200.0000 | | |
| 14 | 2010-12-15 09:15:50 | 312.1000 | Normal | | 50.0000 | | | 50.0000 | | |
| 15 | 2010-12-15 09:46:53 | 812.1000 | Normal | | 500.0000 | | | 500.0000 | | |
| 16 | 2010-12-15 11:12:20 | 779.3000 | Paid Trans | 10 | | | | -32.8000 | | |
| 17 | 2010-12-15 11:13:24 | 829.3000 | Normal | | | | | 50.0000 | | |
| 18 | 2010-12-15 11:13:34 | 879.3000 | Normal | | | 50.0000 | | 50.0000 | | |
| 19 | 2010-12-15 14:32:58 | 612.3000 | Redeem Deposit | 13 | | | | -267.0000 | | |
| 20 | 2010-12-15 14:35:47 | 412.3000 | Redeem Deposit | | -200.0000 | | | -200.0000 | | |
| 21 | 2010-12-15 14:49:56 | 512.3000 | Top-up Deposit | | 100.0000 | | | 100.0000 | | |
| 22 | 2010-12-15 14:50:05 | 412.3000 | Redeem Deposit | | -100.0000 | | | -100.0000 | | |
| 23 | 2010-12-15 14:54:21 | 512.3000 | Top-up Normal | | 100.0000 | | | 100.0000 | | |
| 24 | 2010-12-15 14:55:07 | 412.3000 | Return Normal | | -100.0000 | | | -100.0000 | | |
| 25 | 2010-12-15 14:58:55 | 312.3000 | Return Customer Deposit | | -100.0000 | | | -100.0000 | | |
| 26 | 2010-12-15 14:59:05 | 512.3000 | Top-up Customer Deposit | | 200.0000 | | | 200.0000 | | |
| 28 | 2010-12-15 15:59:59 | 562.3000 | top-up Deposit | | 50.0000 | | | 50.0000 | | |
| 29 | 2010-12-15 16:00:43 | 612.3000 | top-up Deposit | | 50.0000 | | | 50.0000 | | |
| 30 | 2010-12-15 16:01:04 | 712.3000 | Top-up Deposit | | 100.0000 | | | 100.0000 | | |
| 31 | 2010-12-15 16:01:13 | 612.3000 | Return Deposit | | -100.0000 | | | -100.0000 | | |
| 3 | 2010-12-14 12:45:20 | 500.0000 | Fee | | 500.0000 | | | | 500.0000 | |
| 5 | 2010-12-14 12:46:00 | 225.0000 | Paid Trans | 3 | | | | | -275.0000 | |
| 8 | 2010-12-14 14:49:42 | 325.0000 | Fee | | | 100.0000 | | | 100.0000 | |
| 13 | 2010-12-14 15:27:59 | 140.0000 | Paid Trans | 9 | | | | | -185.0000 | |
| 27 | 2010-12-15 15:15:15 | 40.0000 | Return School Fee | | -100.0000 | | | | -100.0000 | |
+-----------+---------------------+----------+-------------------------+---------+-----------+----------+---------+-----------+-----------+--------+
31 rows in set, 1 warning (0.09 sec)mysql>
-> if(A.DepositGroupID=2,if(A.TransStatus=1,1,-1)*A.Amount,"") as Fee,
-> if(A.DepositGroupID=3,if(A.TransStatus=1,1,-1)*A.Amount,"") as '',当DepositGroupID=1时,列名对应的是他自己的DEPOSITNAME
SET @a1='SELECT A.DepositID,A.TransDate,SUM(IF(e.TransStatus=1,E.AMOUNT,-1*E.AMOUNT)) AS Balance,A.Description,IF(A.TransID=0,"",A.transid) AS Receipt,
IF(A.DepositType="Cash",IF(A.TransStatus=1,1,-1)*A.Amount,"") AS Cash,
IF(A.DepositType="VISA",IF(A.TransStatus=1,1,-1)*A.Amount,"") AS visa,
IF(A.DepositType="EPS",IF(A.TransStatus=1,1,-1)*A.Amount,"") AS EPS,';
SET @a2=' FROM depositdetail A INNER JOIN DepositGroup B ON A.DepositGroupID=B.DepositGroupID
LEFT JOIN DepositDetail E ON A.Customernumber=E.CustomerNUmber AND A.DepositGroupID=E.DepositGroupID
WHERE A.TransDate>=E.TransDATE AND A.CustomerNumber="HK-00001"
GROUP BY A.TransDate,A.DepositGroupID,A.CustomerNumber';
SET @a3='';
SELECT *,@a3:=CONCAT(@a3,'IF(A.DepositGroupID=',DepositGroupID,',IF(A.TransStatus=1,1,-1)*A.Amount,"") AS ',DepositName,',')
FROM depositgroup;
SELECT @a3:=LEFT(@a3,LENGTH(@a3)-1);
SELECT @a4:=CONCAT(@a1,@a3,@a2);
PREPARE dd FROM @a4;
EXECUTE dd;你的DepositGroup只有2条记录
这个语句是什么意思?
@A4中的内容为SQL语句