select b.user_name as userName,
c.expert_id as expertId,
b.user_photo as userHeadIcon,
amout as codeSum,
rn as rank
from user_info b,(
select expert_id,amout,row_number() over(order by amout desc) as rn
from(
select expert_id, count(expert_id) amout
from code_infos a
group by a.expert_id
)
) c
where b.user_id = c.expert_id and c.rn <= 50;
//
with
sql0 as (SELECT USER_NAME, USER_ID FROM USER_INFO),
sql1 as (SELECT APPROVE_FLOW_ID ,ASSET_TYPE ,ASSET_ID, APPLI_OPERATOR ,CREATED_DT ,APPLI_OPINION FROM ASSET_APPROVE_LOGS WHERE APPROVE_FLOW_ID = :approveFlowId AND PROCESS_ID = :processId),
sql2 as (SELECT PROJECT_ID, PROJECT_LABEL, DOMAIN_CODE ,PROJECT_NAME ,PROJECT_DESC ,RELEASE_TIME ,STATUS FROM MODEL_PROJECT_INFO WHERE APPROVE_FLOW_ID = :approveFlowId),
sql3 as (SELECT DOMAIN_NAME, DOMAIN_CODE FROM ASSET_DOMAIN_INFOS)
select
sql0.USER_NAME AS userName,
sql1.APPROVE_FLOW_ID AS approveFlowId,
DECODE(sql1.ASSET_TYPE, 'A00001', '模型', 'A00002', '专题', 'A00003', '指标', 'A00004', '标签', 'A00005', '代码')
AS assetType,
sql1.ASSET_ID AS assetId,
sql1.CREATED_DT AS appliTime,
sql1.APPLI_OPINION AS appliOpinion,
sql2.PROJECT_NAME AS assetName,
sql2.PROJECT_DESC AS assetDesc,
sql2.RELEASE_TIME AS releaseTime,
DECODE(sql2.STATUS, '0', '草稿', '1', '待审核', '2', '已发布', '3', '已下线', '4', '被驳回', '5', '被退回', '6', '已下发', '7', '已删除')
AS assetStatus,
sql3.DOMAIN_NAME AS domainName,
sql2.PROJECT_LABEL AS assetLabel
from sql0
join sql1 on sql0.USER_ID = sql1.APPLI_OPERATOR
join sql2 on sql1.ASSET_ID = sql2.PROJECT_ID
join sql3 on sql2.DOMAIN_CODE = sql3.DOMAIN_CODE
//marge into的用法
//start with的用法