现有三个表,结构如下:
表一Customer:
CustomerNo CustomerName Address
70223 张三 某地1
70257 李四 某地2表二OrderList:
OrderID CustomerNo OrderTime
200407010001 70223 2004-07-01
200407020001 70223 2004-07-02
200407020002 70257 2004-07-02
200407070001 70223 2004-07-07
200407210001 70223 2004-07-21
200407210002 70257 2004-07-21表三OrderContent:
OrderID CustomerNo WareName Quantity WareType
200407010001 70223 某商品1 1 LX
200407010001 70257 某商品2 1 LX
200407020001 70223 某商品1 1 LX
200407020002 70257 某商品1 2 LX
200407020002 70257 某商品2 1 LX
200407020002 70257 某商品3 1 LXX
200407070001 70223 某商品1 1 LX
200407210001 70223 某商品1 1 LX
200407210002 70257 某商品1 3 LX
200407210002 70257 某商品2 2 LX现在要生成如下表:
CustomerNo CustomerName QDate1 Qdate2 ... Qdate31
70223 张三 2 1 0
70257 李四 0 3 0
即要统计每个客户当月每天购买的WareType为LX的商品的数量,QDate1,QData2...Qdate31。
请教如何实现这样的存储过程。
表一Customer:
CustomerNo CustomerName Address
70223 张三 某地1
70257 李四 某地2表二OrderList:
OrderID CustomerNo OrderTime
200407010001 70223 2004-07-01
200407020001 70223 2004-07-02
200407020002 70257 2004-07-02
200407070001 70223 2004-07-07
200407210001 70223 2004-07-21
200407210002 70257 2004-07-21表三OrderContent:
OrderID CustomerNo WareName Quantity WareType
200407010001 70223 某商品1 1 LX
200407010001 70257 某商品2 1 LX
200407020001 70223 某商品1 1 LX
200407020002 70257 某商品1 2 LX
200407020002 70257 某商品2 1 LX
200407020002 70257 某商品3 1 LXX
200407070001 70223 某商品1 1 LX
200407210001 70223 某商品1 1 LX
200407210002 70257 某商品1 3 LX
200407210002 70257 某商品2 2 LX现在要生成如下表:
CustomerNo CustomerName QDate1 Qdate2 ... Qdate31
70223 张三 2 1 0
70257 李四 0 3 0
即要统计每个客户当月每天购买的WareType为LX的商品的数量,QDate1,QData2...Qdate31。
请教如何实现这样的存储过程。
选统计出来,然后行转列
表一Customer:与表三OrderContent:左连接
分组统计OK
Select OrderList.OrderID, OrderList.CustomerNo, Customer.CustomerName,
IsNull((Select Sum(Quantity) From OrderContent Where
OrderContent.OrderID=OrderList.OrderID and Day(OrderList.OrderTime) = 1 and
OrderContent.WareType = 'LX' Group by OrderList.CustomerNo),0) as QDate1,
IsNull((Select Sum(Quantity) From OrderContent Where
OrderContent.OrderID=OrderList.OrderID and Day(OrderList.OrderTime) = 2 and
OrderContent.WareType = 'LX' Group by OrderList.CustomerNo),0) as QDate2,
....
IsNull((Select Sum(Quantity) From OrderContent Where
OrderContent.OrderID = OrderList.OrderID and Day(OrderList.OrderTime) = 31
and OrderContent.WareType = 'LX' Group by OrderList.CustomerNo),0) as
QDate31
From OrderList
Left Join Customer On (OrderList.CustomerNo = Customer.CustomerNo)
Where Month(OrderList.OrderTime) = Month(GetDate())
(
IS_CUSTOMERNO IN VARCHAR2,
IS_YEAR IN VARCHAR2,
IS_MONTH IN VARCHAR2,
IS_WARETYPE IN VARCHAR2,
OI_CODE OUT NUMBER,
OS_MESS OUT VARCHAR2
)
IS
CURSOR CUR_ORDERLISTS
IS
select A.CustomerNo,A.CustomerName,
B.OrderID,B.OrderTime,
C.WareName,C.Quantity,C.WareType
from Customer A,OrderList B,OrderContent C
where A.CustomerNo = B.CustomerNo AND
A.CustomerNo = C.CustomerNo AND
B.OrderID = C.OrderID AND
A.CUSTOMERNO = IS_CUSTOMERNO AND
C.WARETYPE = IS_WARETYPE AND
SUBSTR(B.ORDERTIME,1,4) = IS_YEAR AND
SUBSTR(B.ORDERTIME,6,2) = IS_MONTH
ORDER BY A.CustomerNo,B.OrderTime,B.OrderID;
ORDERLISTS CUR_ORDERLISTS%ROWTYPE;
CUSTOMERNAME VARCHAR2(50);
LI_CID NUMBER;
LS_SQL VARCHAR2(255);
LI_COUNT NUMBER := 0;
LI_TEMP NUMBER;
LI_FLAG NUMBER; UPDATE_ERROR EXCEPTION;
BEGIN
OI_CODE := 0;
OS_MESS := '执行失败';
SET TRANSACTION READ WRITE; DELETE FROM CUSTOMERORDER
WHERE CUSTOMERNO = IS_CUSTOMERNO; select CUSTOMERNAME
INTO CUSTOMERNAME
from CUSTOMER
WHERE CUSTOMERNO = IS_CUSTOMERNO;
INSERT INTO CUSTOMERORDER
(CUSTOMERNO, CUSTOMERNAME, QDATE1, QDATE2, QDATE3, QDATE4, QDATE5,
QDATE6, QDATE7, QDATE8, QDATE9, QDATE10, QDATE11, QDATE12, QDATE13,
QDATE14, QDATE15, QDATE16, QDATE17, QDATE18, QDATE19, QDATE20, QDATE21,
QDATE22, QDATE23, QDATE24, QDATE25, QDATE26, QDATE27, QDATE28, QDATE29,
QDATE30, QDATE31)
VALUES
(IS_CUSTOMERNO,CUSTOMERNAME,0,0,0,0,0,
0,0,0,0,0,0,0,0,
0,0,0,0,0,0,0,0,
0,0,0,0,0,0,0,0,
0,0);
LI_CID := DBMS_SQL.OPEN_CURSOR;
FOR ORDERLISTS IN CUR_ORDERLISTS LOOP
LI_COUNT := NVL(ORDERLISTS.Quantity,0);
LS_SQL := ' UPDATE CUSTOMERORDER SET QDATE'||
TO_CHAR(TO_NUMBER(SUBSTR(ORDERLISTS.ORDERTIME,9,2)))||
' = QDATE'||
TO_CHAR(TO_NUMBER(SUBSTR(ORDERLISTS.ORDERTIME,9,2)))||
' + '||TO_CHAR(LI_COUNT)||
' WHERE CUSTOMERNO='||IS_CUSTOMERNO;
DBMS_SQL.PARSE(LI_CID,LS_SQL,DBMS_SQL.NATIVE);
DBMS_OUTPUT.PUT_LINE(LS_SQL);
LI_FLAG := DBMS_SQL.EXECUTE(LI_CID);
END LOOP;
DBMS_SQL.CLOSE_CURSOR(LI_CID);
COMMIT;
OI_CODE := 1;
OS_MESS := '执行成功';
EXCEPTION
WHEN UPDATE_ERROR THEN
OI_CODE := 2;
OS_MESS := '更新用户数据失败';
ROLLBACK;
WHEN OTHERS THEN
OI_CODE := -1;
OS_MESS := '未知错误';
ROLLBACK;
END;
建表SQL代码如下:全部在ORACLE805环境下测试通过create table CustomerOrder
(
CustomerNo Varchar2(50) NOT NULL,
CustomerName Varchar2(50) NOT NULL,
QDate1 Varchar2(50) NOT NULL,
QDate2 Varchar2(50) NOT NULL,
QDate3 Varchar2(50) NOT NULL,
QDate4 Varchar2(50) NOT NULL,
QDate5 Varchar2(50) NOT NULL,
QDate6 Varchar2(50) NOT NULL,
QDate7 Varchar2(50) NOT NULL,
QDate8 Varchar2(50) NOT NULL,
QDate9 Varchar2(50) NOT NULL,
QDate10 Varchar2(50) NOT NULL,
QDate11 Varchar2(50) NOT NULL,
QDate12 Varchar2(50) NOT NULL,
QDate13 Varchar2(50) NOT NULL,
QDate14 Varchar2(50) NOT NULL,
QDate15 Varchar2(50) NOT NULL,
QDate16 Varchar2(50) NOT NULL,
QDate17 Varchar2(50) NOT NULL,
QDate18 Varchar2(50) NOT NULL,
QDate19 Varchar2(50) NOT NULL,
QDate20 Varchar2(50) NOT NULL,
QDate21 Varchar2(50) NOT NULL,
QDate22 Varchar2(50) NOT NULL,
QDate23 Varchar2(50) NOT NULL,
QDate24 Varchar2(50) NOT NULL,
QDate25 Varchar2(50) NOT NULL,
QDate26 Varchar2(50) NOT NULL,
QDate27 Varchar2(50) NOT NULL,
QDate28 Varchar2(50) NOT NULL,
QDate29 Varchar2(50),
QDate30 Varchar2(50),
QDate31 Varchar2(50),
constraint CustomerOrder_pk primary key (CustomerNo)
);