富豪刚赞助了点分,刚才那问题的分数送上。
http://topic.csdn.net/u/20101217/12/6afc4775-485a-456d-a25c-8dbe125f5827.html?seed=2119660219&r=70648340#r_70648340
http://topic.csdn.net/u/20101217/12/6afc4775-485a-456d-a25c-8dbe125f5827.html?seed=2119660219&r=70648340#r_70648340
简单示例:
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条记录
测试OK