当然可以用了,给你个示例: SELECT B.BOND_INNER_ID, B.BOND_ID, B.BOND_CODE, B.BOND_SHORT_NAME, B.SEC_MAR_PAR, A.DMI_BSNS_DATE as trade_date, A.DMI_PRVS_CLSNG_RATE AS NET_PRE_CLOS_PRI, A.DMI_OPNG_RATE AS NET_OPEN_PRI, A.DMI_HIGHST_RATE AS HIGH_NET, A.DMI_LWST_RATE AS LOW_NET, ---DMI_LWST_RATE A.DMI_CLSNG_RATE AS CLOS_NET, NULL AS AVE_NET, A.DMI_NET_RATE_CHGE_RATE AS RISE_DROP_NET, NULL AS VIBR_NET, NULL AS FULL_PRE_CLOS, NULL AS FULL_OPEN_CLOS, NULL AS FULL_HIGH_CLOS, NULL AS FULL_LOW_CLOS, NULL AS FULL_CLOS_CLOS, NULL AS FULL_AVE_CLOS, NULL AS FULL_RISE_DROP, NULL AS FULL_VIBR, A.TURNOVER_RATE AS TURNOVER_RATE, NULL AS DEAL_COUNT, NULL AS DEAL_VOL, A.DMI_TTL_TRADED_AMNT AS DEAL_AMUT, A.DMI_OPNG_CONTRA_RATE AS OPEN_LIMIT_YIELD, A.DMI_HIGHST_CONTRA_RATE AS HIGH_LIMIT_YIELD, A.DMI_LWST_CONTRA_RATE AS LOW_LIMIT_YIELD, A.DMI_CLSNG_CONTRA_RATE AS CLOS_LIMIT_YIELD, NULL AS AVE_LIMIT_YIELD, DENSE_RANK() OVER(PARTITION BY A.DMI_MKT_INFRMN_CRTR_DESC ORDER BY A.DMI_BSNS_DATE DESC) RANKNUM FROM V_T_CBT_MARKET_LAST A LEFT JOIN APPDATA.TI_BND_CODE_CONT B ON A.DMI_MKT_INFRMN_CRTR_DESC = B.BOND_CODE WHERE B.BOND_ID = V_BOND_ID AND A.DMI_BSNS_DATE < V_TRADE_DATE ORDER BY DMI_BSNS_DATE DESC
decalre
v_name varchar2(20);
begin
select name into v_name from tesb_table;
end;
sql的要求:按ACCT_ID,t.SERV_ID,t.ACC_NBR, t.ACCT_ITEM_TYPE_ID,字段分组,按 DEAL_TIME, DURATION, FLOWS 排序,取每个分组的最大一条记录,----就是把上面的sql语句不用函数实现,group by ,order by按条件取最大记录,谢谢!
SELECT B.BOND_INNER_ID,
B.BOND_ID,
B.BOND_CODE,
B.BOND_SHORT_NAME,
B.SEC_MAR_PAR,
A.DMI_BSNS_DATE as trade_date,
A.DMI_PRVS_CLSNG_RATE AS NET_PRE_CLOS_PRI,
A.DMI_OPNG_RATE AS NET_OPEN_PRI,
A.DMI_HIGHST_RATE AS HIGH_NET,
A.DMI_LWST_RATE AS LOW_NET, ---DMI_LWST_RATE
A.DMI_CLSNG_RATE AS CLOS_NET,
NULL AS AVE_NET,
A.DMI_NET_RATE_CHGE_RATE AS RISE_DROP_NET,
NULL AS VIBR_NET,
NULL AS FULL_PRE_CLOS,
NULL AS FULL_OPEN_CLOS,
NULL AS FULL_HIGH_CLOS,
NULL AS FULL_LOW_CLOS,
NULL AS FULL_CLOS_CLOS,
NULL AS FULL_AVE_CLOS,
NULL AS FULL_RISE_DROP,
NULL AS FULL_VIBR,
A.TURNOVER_RATE AS TURNOVER_RATE,
NULL AS DEAL_COUNT,
NULL AS DEAL_VOL,
A.DMI_TTL_TRADED_AMNT AS DEAL_AMUT,
A.DMI_OPNG_CONTRA_RATE AS OPEN_LIMIT_YIELD,
A.DMI_HIGHST_CONTRA_RATE AS HIGH_LIMIT_YIELD,
A.DMI_LWST_CONTRA_RATE AS LOW_LIMIT_YIELD,
A.DMI_CLSNG_CONTRA_RATE AS CLOS_LIMIT_YIELD,
NULL AS AVE_LIMIT_YIELD,
DENSE_RANK() OVER(PARTITION BY A.DMI_MKT_INFRMN_CRTR_DESC ORDER BY A.DMI_BSNS_DATE DESC) RANKNUM
FROM V_T_CBT_MARKET_LAST A
LEFT JOIN APPDATA.TI_BND_CODE_CONT B
ON A.DMI_MKT_INFRMN_CRTR_DESC = B.BOND_CODE
WHERE B.BOND_ID = V_BOND_ID
AND A.DMI_BSNS_DATE < V_TRADE_DATE
ORDER BY DMI_BSNS_DATE DESC