ORACLE 語法我不清楚怎麼寫,但是我可以用SQL SERVER的語法寫給樓主參考一下(這兩種語法,我想在SQL語句上都是一樣的吧):
--先創建測試表和插入測試數據
CREATE TABLE Goods(GoodsType char(2),GoodsId char(4),Qty int,FactQty int);
INSERT INTO Goods VALUES(01,0001,100,50);
INSERT INTO Goods VALUES(01,0002,200,200);
INSERT INTO Goods VALUES(01,0010,150,0);
INSERT INTO Goods VALUES(01,0005,150,200);
INSERT INTO Goods VALUES(03,0003,200,300);
INSERT INTO Goods VALUES(02,0004,150,100);
--求出並返回結果,注意:下面求得的配率是指某一類型(GoodsType)的商品總的配率,我想樓主也是
--指這個意思,否則的話配率是不可能同商品總要貨次數,商品實際總要貨次數等樓主要求輸出的列
--放在一起輸出的
select COUNT(GoodsType) AS 商品總要貨次數,SUM(CASE WHEN FactQty/Qty>=1 THEN 1 ELSE 0 END) as 商品實際總要貨次數,SUM(CASE WHEN FactQty/Qty<1 THEN 1 ELSE 0 END) AS 商品總缺貨次數,SUM(CASE WHEN FactQty/Qty>=1 THEN 1 ELSE 0 END)/COUNT(GoodsType) 商品率,[SUM(FactQty)/SUM(Qty) as 配率
from Goods
group by GoodsType;對於樓主的要求,我是把我的做法用SQL SERVER語句寫成後再稍稍改成了Oracle語法的,但是上面代碼中CASE WHEN ...END函數是SQL SERVER中的語法(函數),我不知道在Oracle中是否有此函數(我剛接觸Oracel,呵呵!),其它的語句都是和Oracle一樣的,因此樓主可把我上面的的所有語句拷到ORACLE查詢分析器中直接運行以測試是否正確(隻要把CASE函數語法用Oracle中相應的函數代替就行了,在Oracle中可能也有這個函數吧,那就不用代替了,那麼以上語句在oracle中直接運行就可以了,我剛接觸Oracle,有很多還不清楚,呵呵!)
--先創建測試表和插入測試數據
CREATE TABLE Goods(GoodsType char(2),GoodsId char(4),Qty int,FactQty int);
INSERT INTO Goods VALUES(01,0001,100,50);
INSERT INTO Goods VALUES(01,0002,200,200);
INSERT INTO Goods VALUES(01,0010,150,0);
INSERT INTO Goods VALUES(01,0005,150,200);
INSERT INTO Goods VALUES(03,0003,200,300);
INSERT INTO Goods VALUES(02,0004,150,100);
--求出並返回結果,注意:下面求得的配率是指某一類型(GoodsType)的商品總的配率,我想樓主也是
--指這個意思,否則的話配率是不可能同商品總要貨次數,商品實際總要貨次數等樓主要求輸出的列
--放在一起輸出的
select COUNT(GoodsType) AS 商品總要貨次數,SUM(CASE WHEN FactQty/Qty>=1 THEN 1 ELSE 0 END) as 商品實際總要貨次數,SUM(CASE WHEN FactQty/Qty<1 THEN 1 ELSE 0 END) AS 商品總缺貨次數,SUM(CASE WHEN FactQty/Qty>=1 THEN 1 ELSE 0 END)/COUNT(GoodsType) 商品率,[SUM(FactQty)/SUM(Qty) as 配率
from Goods
group by GoodsType;對於樓主的要求,我是把我的做法用SQL SERVER語句寫成後再稍稍改成了Oracle語法的,但是上面代碼中CASE WHEN ...END函數是SQL SERVER中的語法(函數),我不知道在Oracle中是否有此函數(我剛接觸Oracel,呵呵!),其它的語句都是和Oracle一樣的,因此樓主可把我上面的的所有語句拷到ORACLE查詢分析器中直接運行以測試是否正確(隻要把CASE函數語法用Oracle中相應的函數代替就行了,在Oracle中可能也有這個函數吧,那就不用代替了,那麼以上語句在oracle中直接運行就可以了,我剛接觸Oracle,有很多還不清楚,呵呵!)
'先創建測試表和插入測試數據
CREATE TABLE Goods(GoodsType char(2),GoodsId char(4),Qty int,FactQty int);
INSERT INTO Goods VALUES(01,0001,100,50);
INSERT INTO Goods VALUES(01,0002,200,200);
INSERT INTO Goods VALUES(01,0010,150,0);
INSERT INTO Goods VALUES(01,0005,150,200);
INSERT INTO Goods VALUES(03,0003,200,300);
INSERT INTO Goods VALUES(02,0004,150,100);
'求出並返回結果
select GoodsType,COUNT(GoodsType) AS 商品總要貨次數,SUM(CASE WHEN FactQty/Qty>=1 THEN 1 ELSE 0 END) as 商品實際總要貨次數,SUM(CASE WHEN FactQty/Qty<1 THEN 1 ELSE 0 END) AS 商品總缺貨次數,SUM(CASE WHEN FactQty/Qty>=1 THEN 1 ELSE 0 END)/COUNT(GoodsType) 商品率,[SUM(FactQty)/SUM(Qty) as 配率
from Goods
group by GoodsType;上面語句我在SQL SERVER中測試是可行的
CREATE TABLE Goods(GoodsType char(2),GoodsId char(4),Qty int,FactQty int);
INSERT INTO Goods VALUES("01","0001",100,50);
INSERT INTO Goods VALUES("01","0002",200,200);
INSERT INTO Goods VALUES("01","0010",150,0);
INSERT INTO Goods VALUES("01","0005",150,200);
INSERT INTO Goods VALUES("03","0003",200,300);
INSERT INTO Goods VALUES("02","0004",150,100);
'求出並返回結果
select GoodsType,COUNT(GoodsType) AS 商品總要貨次數,SUM(CASE WHEN FactQty>=0 THEN 1 ELSE 0 END) as 商品實際總要貨次數,SUM(CASE WHEN FactQty<=0 THEN 1 ELSE 0 END) AS 商品總缺貨次數,SUM(CASE WHEN FactQty/Qty>=1 THEN 1 ELSE 0 END)/COUNT(GoodsType) 商品率,SUM(FactQty)/SUM(Qty) as 配率
from Goods
group by GoodsType;'如果樓主意思沒表達清楚的話,那上面的語句可能也不符合你的要求,誰也沒辦法了!
'先創建測試表和插入測試數據,注意:實際要貨量和總要貨量字段的字段類型要為能帶小數點的
'數值型,如十進制型,如果是整型的話,求出來商品率和配率會被自動去小數點後再取整,因而
'可能出現總是0的情形
CREATE TABLE Goods(GoodsType char(2),GoodsId char(4),Qty decimal(10,3),FactQty decimal(10,3),);
INSERT INTO Goods VALUES("01","0001",100,50);
INSERT INTO Goods VALUES("01","0002",200,200);
INSERT INTO Goods VALUES("01","0010",150,0);
INSERT INTO Goods VALUES("01","0005",150,200);
INSERT INTO Goods VALUES("03","0003",200,300);
INSERT INTO Goods VALUES("02","0004",150,100);
'求出並返回結果
select GoodsType,COUNT(GoodsType) AS 商品總要貨次數,SUM(CASE WHEN FactQty>=0 THEN 1 ELSE 0 END) as 商品實際總要貨次數,SUM(CASE WHEN FactQty<=0 THEN 1.00000 ELSE 0.00000 END) AS 商品總缺貨次數,SUM(CASE WHEN FactQty/Qty>=1 THEN 1 ELSE 0 END)/COUNT(GoodsType) 商品率,SUM(FactQty)/SUM(Qty) as 配率
from Goods
group by GoodsType;'以上語句,我已在SQL SERVER中測試過,現在已經沒有問題了。
CREATE TABLE Goods(GoodsType char(2),GoodsId char(4),Qty decimal(10,3),FactQty decimal(10,3),);
INSERT INTO Goods VALUES("01","0001",100,50);
INSERT INTO Goods VALUES("01","0002",200,200);
INSERT INTO Goods VALUES("01","0010",150,0);
INSERT INTO Goods VALUES("01","0005",150,200);
INSERT INTO Goods VALUES("03","0003",200,300);
INSERT INTO Goods VALUES("02","0004",150,100);
'求出並返回結果
select GoodsType,COUNT(GoodsType) AS 商品總要貨次數,SUM(CASE WHEN FactQty>=0 THEN 1 ELSE 0 END) as 商品實際總要貨次數,SUM(CASE WHEN FactQty<=0 THEN 1.00000 ELSE 0.00000 END) AS 商品總缺貨次數,SUM(CASE WHEN FactQty/Qty>=1 THEN 1 ELSE 0 END)/COUNT(GoodsType) 商品率,SUM(FactQty)/SUM(Qty) as 配率
from Goods
group by GoodsType;
CREATE TABLE Goods(GoodsType char(2),GoodsId char(4),Qty decimal(10,3),FactQty decimal(10,3));
INSERT INTO Goods VALUES("01","0001",100,50);
INSERT INTO Goods VALUES("01","0002",200,200);
INSERT INTO Goods VALUES("01","0010",150,0);
INSERT INTO Goods VALUES("01","0005",150,200);
INSERT INTO Goods VALUES("03","0003",200,300);
INSERT INTO Goods VALUES("02","0004",150,100);
'求出並返回結果
select GoodsType,COUNT(GoodsType) AS 商品總要貨次數,SUM(CASE WHEN FactQty>=0 THEN 1 ELSE 0 END) as 商品實際總要貨次數,SUM(CASE WHEN FactQty<=0 THEN 1.00000 ELSE 0.00000 END) AS 商品總缺貨次數,SUM(CASE WHEN FactQty/Qty>=1 THEN 1 ELSE 0 END)/COUNT(GoodsType) 商品率,SUM(FactQty)/SUM(Qty) as 配率
from Goods
group by GoodsType;
但我是在Orcle里,不能用"CASE WHEN FactQty/Qty<1 THEN 1 ELSE 0 END"这句
配上SIGN函数.如判断>=1就可以用DECODE(SIGN(VALUE-1),1,..,0,..,-1,..)
CREATE TABLE Goods(GoodsType char(2),GoodsId char(4),Qty decimal(10,3),FactQty decimal(10,3));
INSERT INTO Goods VALUES('01','0001',100,50);
INSERT INTO Goods VALUES('01','0002',200,200);
INSERT INTO Goods VALUES('01','0010',150,0);
INSERT INTO Goods VALUES('01','0005',150,200);
INSERT INTO Goods VALUES('03','0003',200,300);
INSERT INTO Goods VALUES('02','0004',150,100);
--求出並返回結果
select GoodsType,COUNT(GoodsType) AS 商品總要貨次數,SUM(CASE WHEN FactQty>=0 THEN 1 ELSE 0 END) as 商品實際總要貨次數,SUM(CASE WHEN FactQty<=0 THEN 1.00000 ELSE 0.00000 END) AS 商品總缺貨次數,SUM(CASE WHEN FactQty/Qty>=1 THEN 1 ELSE 0 END)/COUNT(GoodsType) 商品率,SUM(FactQty)/SUM(Qty) as 配率
from Goods
group by GoodsType;
--下面是結果
SQL> /GO 商品總要貨次數 商品實際總要貨次數 商品總缺貨次數 商品率 配率
-- -------------- ------------------ -------------- ---------- ----------
01 4 4 1 .5 .75
02 1 1 0 0 .666666667
03 1 1 0 1 1.5SQL>
select GoodsType,
COUNT(GoodsType) AS 商品總要貨次數,
SUM(CASE WHEN FactQty>0 THEN 1 ELSE 0 END) as 商品實際總要貨次數,
SUM(CASE WHEN FactQty<=0 THEN 1.00000 ELSE 0.00000 END) AS 商品總缺貨次數,
SUM(CASE WHEN FactQty/Qty>=1 THEN 1 ELSE 0 END)/COUNT(GoodsType) 商品率,
SUM(FactQty)/SUM(Qty) as 配率
from Goods
group by GoodsType;GOODSTYPE 商品总要货次数 商品实际总要货次数 商品总缺货次数 商品率 配率
--------- -------------- ------------------ -------------- ---------- ----------
01 4 3 1 0.5 0.75
02 1 1 0 0 0.66666666
03 1 1 0 1 1.5