我将数据insert 到临时表中,然后用order by 多列排序查询出来,结果没有形成排序。
CREATE PROCEDURE `ARAP_APBalanceMX`(
p_Begindate char(10),
p_Enddate char(10),
WLDW_BH varchar(30)
)
BEGIN DECLARE v_WHERESql VARCHAR(2480);
DECLARE v_sql VARCHAR(2480);
DROP TEMPORARY TABLE IF EXISTS T_Account;
/*建临时表*/
CREATE TEMPORARY TABLE IF NOT EXISTS T_Account(
WLDW_BH varchar(30) /*往来单位编号*/,
Forder INT /*排序*/,
PaymentID int /* 付款单标识符 */,
BillCode varchar(30) /* 付款单编号 */,
Cheque varchar(60) /* 支票号 */,
BillDate date /* 单据日期 */,
PayingType varchar(10) /* 付款类型 预付款 应付款 */,
JSFS varCHAR(20) /* 结算方式 */,
PayValue decimal(18,8)NOT NULL default 0/* 付款金额 */,
DiscountValue decimal(18,8)NOT NULL default 0/* 折扣金额 */,
ActPayValue decimal(18,8)NOT NULL default 0/* 实付金额 */
);
SET v_WHERESql=CONCAT(' BillDate>=cast(''' , p_Begindate , ''' as date) AND BillDate<= CAST(''' , p_Enddate , ''' as date)');
IF (WLDW_BH <>'' AND WLDW_BH IS NOT NULL) THEN
SET v_WHERESql=CONCAT(v_WHERESql , ' AND Payment.VendorID=''', WLDW_BH , '''');
END IF ;
/*一个供应商,一个供应商 */
SET v_sql=CONCAT('INSERT INTO T_Account(WLDW_BH,Forder,PaymentID,BillCode,Cheque,BillDate,PayingType,JSFS)
SELECT DISTINCT VendorID,1,0,CONCAT(''供应商编号:'',VendorID),(SELECT CONCAT(''名称:'', WLDW_MC) FROM Zw_WLDW WHERE WLDW_BH=VendorID),null,'''',''''
FROM Payment WHERE ', v_WHERESql); Set @s=v_Sql;
PREPARE stmt1 FROM @s;
EXECUTE stmt1; /*主要内容*/
SET v_sql=CONCAT('INSERT INTO T_Account(WLDW_BH,Forder,PaymentID,BillCode,Cheque,BillDate,PayingType,JSFS,PayValue,DiscountValue,ActPayValue)
SELECT Payment.VendorID,2,PaymentID,BillCode,Cheque,BillDate,case PaymentType when ''0'' then ''预付款'' else ''应付款'' end ,
(SELECT JSFS_MC FROM Zw_jsfs WHERE JSFS_BH=Payment.JSFS_BH) AS JSFS,PayValue,(PayValue-ActPayValue) AS DiscountValue,ActPayValue
FROM Payment WHERE ', v_WHERESql); Set @s=v_Sql;
PREPARE stmt1 FROM @s;
EXECUTE stmt1; /*客户小计*/
SET v_sql=CONCAT('INSERT INTO T_Account(WLDW_BH,Forder,PaymentID,BillCode,Cheque,BillDate,PayingType,JSFS,PayValue,DiscountValue,ActPayValue)
SELECT Payment.VendorID,3,0,''小计'','''',null ,'''','''',COALESCE(sum(PayValue),0) as PayValue,COALESCE(sum(PayValue-ActPayValue),0) AS DiscountValue,COALESCE(sum(ActPayValue),0)
FROM Payment WHERE ', v_WHERESql, 'GROUP BY Payment.VendorID'); Set @s=v_Sql;
PREPARE stmt1 FROM @s;
EXECUTE stmt1; SET v_sql=CONCAT('INSERT INTO T_Account(WLDW_BH,Forder,PaymentID,BillCode,Cheque,BillDate,PayingType,JSFS,PayValue,DiscountValue,ActPayValue)
SELECT ''zzzzzzzzzz'',4,0,''合计'','''',null ,'''','''',COALESCE(sum(PayValue),0) as PayValue,COALESCE(sum(PayValue-ActPayValue),0) AS DiscountValue,COALESCE(sum(ActPayValue),0)
FROM Payment WHERE ', v_WHERESql ); Set @s=v_Sql;
PREPARE stmt1 FROM @s;
EXECUTE stmt1;
SELECT *
FROM T_Account
ORDER BY WLDW_BH,Forder,PaymentID;
END
;
结果显示总是按Forder,PaymentID这个排序,很是困惑
CREATE PROCEDURE `ARAP_APBalanceMX`(
p_Begindate char(10),
p_Enddate char(10),
WLDW_BH varchar(30)
)
BEGIN DECLARE v_WHERESql VARCHAR(2480);
DECLARE v_sql VARCHAR(2480);
DROP TEMPORARY TABLE IF EXISTS T_Account;
/*建临时表*/
CREATE TEMPORARY TABLE IF NOT EXISTS T_Account(
WLDW_BH varchar(30) /*往来单位编号*/,
Forder INT /*排序*/,
PaymentID int /* 付款单标识符 */,
BillCode varchar(30) /* 付款单编号 */,
Cheque varchar(60) /* 支票号 */,
BillDate date /* 单据日期 */,
PayingType varchar(10) /* 付款类型 预付款 应付款 */,
JSFS varCHAR(20) /* 结算方式 */,
PayValue decimal(18,8)NOT NULL default 0/* 付款金额 */,
DiscountValue decimal(18,8)NOT NULL default 0/* 折扣金额 */,
ActPayValue decimal(18,8)NOT NULL default 0/* 实付金额 */
);
SET v_WHERESql=CONCAT(' BillDate>=cast(''' , p_Begindate , ''' as date) AND BillDate<= CAST(''' , p_Enddate , ''' as date)');
IF (WLDW_BH <>'' AND WLDW_BH IS NOT NULL) THEN
SET v_WHERESql=CONCAT(v_WHERESql , ' AND Payment.VendorID=''', WLDW_BH , '''');
END IF ;
/*一个供应商,一个供应商 */
SET v_sql=CONCAT('INSERT INTO T_Account(WLDW_BH,Forder,PaymentID,BillCode,Cheque,BillDate,PayingType,JSFS)
SELECT DISTINCT VendorID,1,0,CONCAT(''供应商编号:'',VendorID),(SELECT CONCAT(''名称:'', WLDW_MC) FROM Zw_WLDW WHERE WLDW_BH=VendorID),null,'''',''''
FROM Payment WHERE ', v_WHERESql); Set @s=v_Sql;
PREPARE stmt1 FROM @s;
EXECUTE stmt1; /*主要内容*/
SET v_sql=CONCAT('INSERT INTO T_Account(WLDW_BH,Forder,PaymentID,BillCode,Cheque,BillDate,PayingType,JSFS,PayValue,DiscountValue,ActPayValue)
SELECT Payment.VendorID,2,PaymentID,BillCode,Cheque,BillDate,case PaymentType when ''0'' then ''预付款'' else ''应付款'' end ,
(SELECT JSFS_MC FROM Zw_jsfs WHERE JSFS_BH=Payment.JSFS_BH) AS JSFS,PayValue,(PayValue-ActPayValue) AS DiscountValue,ActPayValue
FROM Payment WHERE ', v_WHERESql); Set @s=v_Sql;
PREPARE stmt1 FROM @s;
EXECUTE stmt1; /*客户小计*/
SET v_sql=CONCAT('INSERT INTO T_Account(WLDW_BH,Forder,PaymentID,BillCode,Cheque,BillDate,PayingType,JSFS,PayValue,DiscountValue,ActPayValue)
SELECT Payment.VendorID,3,0,''小计'','''',null ,'''','''',COALESCE(sum(PayValue),0) as PayValue,COALESCE(sum(PayValue-ActPayValue),0) AS DiscountValue,COALESCE(sum(ActPayValue),0)
FROM Payment WHERE ', v_WHERESql, 'GROUP BY Payment.VendorID'); Set @s=v_Sql;
PREPARE stmt1 FROM @s;
EXECUTE stmt1; SET v_sql=CONCAT('INSERT INTO T_Account(WLDW_BH,Forder,PaymentID,BillCode,Cheque,BillDate,PayingType,JSFS,PayValue,DiscountValue,ActPayValue)
SELECT ''zzzzzzzzzz'',4,0,''合计'','''',null ,'''','''',COALESCE(sum(PayValue),0) as PayValue,COALESCE(sum(PayValue-ActPayValue),0) AS DiscountValue,COALESCE(sum(ActPayValue),0)
FROM Payment WHERE ', v_WHERESql ); Set @s=v_Sql;
PREPARE stmt1 FROM @s;
EXECUTE stmt1;
SELECT *
FROM T_Account
ORDER BY WLDW_BH,Forder,PaymentID;
END
;
结果显示总是按Forder,PaymentID这个排序,很是困惑
0001 1 0 往来单位:中华电子公司 0.00000000 0.00000000 0.00000000
0001 2 1 CF20090312002 2009-3-12 160.00000000 100.00000000 60.00000000
0001 2 3 CF20090313007 2009-3-13 21305.70000000 0.00000000 21305.70000000
0001 2 4 CF20090313009 2009-3-13 1053.00000000 1053.00000000 0.00000000
0001 3 0 小计 22518.70000000 1153.00000000 21365.70000000
0004 1 0 往来单位:山东印刷包装有限公司 0.00000000 0.00000000 0.00000000
0004 2 2 CF20090313005 2009-3-13 1300.00000000 1300.00000000 0.00000000
0004 3 0 小计 1300.00000000 1300.00000000 0.00000000
zzzzzzzzzzzzzzzz 4 0 合计 23818.70000000 2453.00000000 21365.70000000
0001 1 0 往来单位:中华电子公司 0.00000000 0.00000000 0.00000000
0001 2 1 CF20090312002 2009-3-12 160.00000000 100.00000000 60.00000000
0001 2 3 CF20090313007 2009-3-13 21305.70000000 0.00000000 21305.70000000
0001 2 4 CF20090313009 2009-3-13 1053.00000000 1053.00000000 0.00000000
0001 3 0 小计 22518.70000000 1153.00000000 21365.70000000
0004 1 0 往来单位:山东印刷包装有限公司 0.00000000 0.00000000 0.00000000
0004 2 2 CF20090313005 2009-3-13 1300.00000000 1300.00000000 0.00000000
0004 3 0 小计 1300.00000000 1300.00000000 0.00000000
zzzzzzzzzz 4 0 合计 23818.70000000 2453.00000000 21365.70000000
;
CREATE VIEW VW_PUInvoicePay
AS
SELECT DISTINCT VendorID AS WLDW_BH,1 AS Forder,0 AS PurInvoiceID,(SELECT CONCAT('往来单位:', WLDW_MC) FROM Zw_WLDW WHERE WLDW_BH=VendorID) AS BillCode,null AS BillDate,0 AS InvoiceTotalValue,0 AS PayValue,0 AS NotPayValue
FROM PurchaseInvoices
UNION
SELECT PurchaseInvoices.VendorID,2,PurInvoiceID,BillCode,BillDate,InvoiceTotalValue,PayValue,(InvoiceTotalValue-PayValue) as NotPayValue
FROM PurchaseInvoices
UNION
SELECT PurchaseInvoices.VendorID,3,0,'小计',null,COALESCE(sum(InvoiceTotalValue),0),COALESCE(sum(PayValue),0) AS PayValue,COALESCE(sum(InvoiceTotalValue-PayValue),0)
FROM PurchaseInvoices GROUP BY PurchaseInvoices.VendorID
UNION
SELECT 'zzzzzzzzzzzzzzzz',4,0,'合计',null,COALESCE(sum(InvoiceTotalValue),0),COALESCE(sum(PayValue),0) AS PayValue,COALESCE(sum(InvoiceTotalValue-PayValue),0)
FROM PurchaseInvoicesSELECT * FROM VW_PUInvoicePay ORDER BY WLDW_BH,Forder,PurInvoiceID
我改成视图了,这样没有问题
后来改成下面的过程了,什么原因我也没有查清楚。
CREATE PROCEDURE `ARAP_APBalanceMX`(
p_Begindate char(10),
p_Enddate char(10),
WLDW_BH varchar(30)
)
BEGIN DECLARE v_WHERESql VARCHAR(2480);
DECLARE v_sql VARCHAR(2480);
SET v_WHERESql=CONCAT(' BillDate>=cast(''' , p_Begindate , ''' as date) AND BillDate<= CAST(''' , p_Enddate , ''' as date)');
IF (WLDW_BH <>'' AND WLDW_BH IS NOT NULL) THEN
SET v_WHERESql=CONCAT(v_WHERESql , ' AND Payment.VendorID=''', WLDW_BH , '''');
END IF ;
/*一个供应商,一个供应商 */
SET v_sql=CONCAT('SELECT * FROM (SELECT DISTINCT VendorID as WLDW_BH,1 as Forder,0 as PaymentID,CONCAT(''供应商编号:'',VendorID) as BillCode,(SELECT CONCAT(''名称:'', WLDW_MC) FROM Zw_WLDW WHERE WLDW_BH=VendorID) as Cheque,null as BillDate,'''' as PayingType,'''' as JSFS,
0 as PayValue,0 as DiscountValue,0 as ActPayValue
FROM Payment WHERE ', v_WHERESql,'
UNION SELECT Payment.VendorID,2,PaymentID,BillCode,Cheque,BillDate,case PaymentType when ''0'' then ''预付款'' else ''应付款'' end ,
(SELECT JSFS_MC FROM Zw_jsfs WHERE JSFS_BH=Payment.JSFS_BH) AS JSFS,PayValue,(PayValue-ActPayValue) AS DiscountValue,ActPayValue
FROM Payment WHERE ', v_WHERESql,'
UNION SELECT Payment.VendorID,3,0,''小计'','''',null ,'''','''',COALESCE(sum(PayValue),0) as PayValue,COALESCE(sum(PayValue-ActPayValue),0) AS DiscountValue,COALESCE(sum(ActPayValue),0)
FROM Payment WHERE ', v_WHERESql, 'GROUP BY Payment.VendorID
UNION SELECT ''zzzzzzzzzz'',4,0,''合计'','''',null ,'''','''',COALESCE(sum(PayValue),0) as PayValue,COALESCE(sum(PayValue-ActPayValue),0) AS DiscountValue,COALESCE(sum(ActPayValue),0)
FROM Payment WHERE ', v_WHERESql,')a
ORDER BY WLDW_BH,Forder,PaymentID'; Set @s=v_Sql;
PREPARE stmt1 FROM @s;
EXECUTE stmt1;END
;