OracleÐÐתÁÐʵÀý

ѪҺÃ÷ϸ±íÈçÏ£º
create table "blood"."OC_CarryOrderD"  (
     ID                   nvarchar2(20)                   not null,
    "ProductCode"        nvarchar2(10),
     "BloodGroup"         nvarchar2(10)
);
ÓбàºÅ£¬²úÆ·Àà±ðºÍѪÐÍÈý¸ö×Ö¶Î
Êý¾ÝÈçÏ£º
Òª¼ÆËãijһ²úÆ·µÄ²»Í¬ÑªÐ͵ĴüÊý
¾ÍÒª½«±íÐÐתÁУ¬×ö³ÉÈçÏÂЧ¹û£º
´úÂëÈçÏÂ
select productcode,
MAX(decode(rn, 1, codesum, NULL)) A,
MAX(decode(rn, 2, codesum, NULL)) AB,
MAX(decode(rn, 3, codesum, NULL)) B,
MAX(decode(rn, 4, codesum, NULL)) O
from
(
       select productcode,bloodgroup,codesum,row_number() over(PARTITION BY productcode ORDER BY bloodgroup, codesum) rn from (
       select productcode,bloodgroup, count(productcode) as codesum from oc_carryorderd2 group by productcode,bloodgroup order by bloodgroup)
)
group by productcode;
×¢Ò⣺ MAXºóÃæµÄÁÐÃû(A,AB,B,O)ҪעÒâ°´×Öĸ˳ÐòÅÅÁУ¬ÒòΪºóÃæµÄselectÓï¾äÊÇ°´ÑªÐÍ×Ö¶ÎÉýÐòÅÅÁеÄ
¶ÔÿÖÖ²úÆ·µÄ´üÊý×ö¸ö»ã×Ü£¬ÈçÏÂͼ
¿ÉÒÔÔÚMAXºóÃæ¼ÓÈë²éѯ×ֶΠsum(codesum)