简单来说就是将List传到SQL语句中进行查询操作
其实,这里将List传到SQL语句中进行查询,方法有很多,我这里只是记录我习惯用的一种方法,原因之初是因为我要判断的情况太多了,不方便全在SQL中进行操作。故此,现在前面代码中封装所需不同的List,然后将不同情况下的List分别传到后台SQL进行操作:
1、首先,这里是Controller
层方法:
@ResponseBody
@RequestMapping("getQuotePriceAnalysisGrid")
public Page<QuotePriceAnalysisDto> getQuotePriceAnalysisGrid() {
Map<String, Object> map = this.getPageParam();
//这里是个共通的方法,很多地方都要调用:具体看下面方法代码
this.dealInSelect(map);
map.put("userId", this.getLoginUser().getUserId());
if("Y".equals(this.getLoginUser().getAdminFlag())){
map.put("isAdmin",true);
}else{
map.put("isAdmin",false);
}
return quotePriceAnalysisService.getQuotePriceAnalysisGridTimeSlotByPage(map,false,true);
}
这里是一个共通方法dealInSelect,
也是这个方法将我们所需的分别装进不同的List中,再封装到map
中,传到后台SQL中,
private void dealInSelect(Map<String, Object> params) {
if (params.get("portfolio_code") != null) {
String str = StringUtil.genInStr(params.get("portfolio_code").toString());
params.put("portfolio_code", str);
}
String assetKind = (String) params.get("assetKind");
List<String> assetKindList = StringUtil.parseString2List(StringUtil.STR_COMMA, assetKind);
//四种特殊资产
List<String> resultList = new ArrayList<>();
//除六种特殊之外的所有*******即除了利率型计划、货币型计划、证券类净值型计划、其他类净值型计划,货币基金、非货币基金之外的
List<String> resultList1 = new ArrayList<>();
//除了四种特殊外(包含货币基金和非货币基金)
List<String> resultList2 = new ArrayList<>();
//除了四种特殊外(且不包含非货币基金之外的所有)
List<String> resultList3 = new ArrayList<>();
//除了四种特殊外(且不包含货币基金之外的所有)
List<String> resultList4 = new ArrayList<>();
if (StringUtil.isNotEmpty(params.get("assetKind"))) {
// List<String> assetKindList = new ArrayList<>();
// String[] assetKindArr = params.get("assetKind").toString().split(",");
// for (String item : assetKindArr) {
// assetKindList.add(item);
// }
for (String asset:assetKindList) {
if("LLXJH".equals(asset)){
resultList.add("PK_01");
}else if("HBXJH".equals(asset)){
resultList.add("PK_02");
resultList2.add(asset);
}else if("JZXJH".equals(asset)){
resultList.add("PK_03");
}else if("QTLJZXJH".equals(asset)){
resultList.add("PK_06");
}else{
if(!"HBJJ".equals(asset)){
resultList4.add(asset);
}
if(!"FHBJJ".equals(asset)){
resultList3.add(asset);
}
if(!"HBJJ".equals(asset) && !"FHBJJ".equals(asset)){
resultList1.add(asset);
}
resultList2.add(asset);
}
}
//当货币基金和非货币基金都选中的话,将基金(“JJ”1105塞进去resultList2)
if(resultList2.contains("HBJJ") && resultList2.contains("FHBJJ")){
resultList2.add("JJ");
}
}
if(!"".equals(resultList)){
params.put("resultList", resultList);
}
if(!"".equals(resultList1)){
params.put("resultList1", resultList1);
}
if(!"".equals(resultList2)){
params.put("resultList2", resultList2);
}
if(!"".equals(resultList3)){
params.put("resultList3", resultList3);
}
if(!"".equals(resultList4)){
params.put("resultList4", resultList4);
}
params.put("assetKindList", assetKindList);
}
2、其次,这里是service
方法:
public Page<QuotePriceAnalysisDto> getQuotePriceAnalysisGridTimeSlotByPage(Map<String, Object> map,Boolean isCT,Boolean isPage){
long msOfDay = 24 * 60 * 60 * 1000;
List<QuotePriceAnalysisDto> resultListTemp = new ArrayList<>();
if (map.get("valDateStart") == null || map.get("valDate") == null){
getLogger().error(">>>【错误】 valDateStart 或者 valDate 为空");
return new Page<>();
}
Date valDate = DateUtil.parseStringToDate(map.get("valDateStart").toString());
Date endDate = DateUtil.parseStringToDate(map.get("valDate").toString());
Integer index = 1;
while(valDate.compareTo(endDate) <= 0) {
map.put("valDate",DateUtil.format(valDate,"yyyy-MM-dd"));
List<QuotePriceAnalysisDto> list = new ArrayList<>();
if (StringUtil.isEmpty((String) map.get("securityCode"))) {
//这里底下会有具体方法,只拿出一例,看下面代码
list = this.getQuotePriceAnalysisGridNullCode(map,index,isCT);
} else {
list = this.getQuotePriceAnalysisGrid(map,index,isCT);
}
if (CollectionUtils.isNotEmpty(list)){
//解析日期
this.parseDate2DateString(list,DateUtil.format(valDate));
resultListTemp.addAll(list);
QuotePriceAnalysisDto dto = list.get(list.size() -1);
index = dto.getIndex() == null ? index + 1: dto.getIndex() +1;
}
valDate = new Date(valDate.getTime() + msOfDay);
}
//排序
this.treeGridSort(resultListTemp);
//分页
int curPage = map.get("curPage") == null ? 1 : Integer.parseInt(map.get("curPage").toString());
int pageSize = map.get("pageSize") == null ? 20 : Integer.parseInt(map.get("pageSize").toString());
int startNum = (curPage - 1) * pageSize;
int endNum = startNum + pageSize;
List<QuotePriceAnalysisDto> resultList = null;
if (isPage){
if (endNum > resultListTemp.size()){
resultList = resultListTemp.subList(startNum,resultListTemp.size());;
}else {
resultList = resultListTemp.subList(startNum,endNum);
}
}else {
resultList = resultListTemp;
}
Integer records = resultListTemp.size();
int pageCount = records / pageSize;
if (records % pageSize != 0){
pageCount = pageCount + 1;
}
//格式转换
Page<QuotePriceAnalysisDto> page = new Page<>();
PageList<QuotePriceAnalysisDto> pageList = new PageList<>();
pageList.addAll(resultList);
page.setRoot(pageList);
page.setTotal(pageCount);
page.setRecords(records);
page.setPage(curPage);
return page;
}
//**********这里是上面判断逻辑其中之一的方法:
public List<QuotePriceAnalysisDto> getQuotePriceAnalysisGridNullCode(Map<String, Object> map,int index,Boolean isCT) {
/*int index = 1;*/
List<QuotePriceAnalysisDto> allList = new ArrayList<QuotePriceAnalysisDto>();
if (map.get("securityCode") != null) {
List<String> str = StringUtil.splitToList(StringUtil.STR_COMMA, map.get("securityCode").toString());
map.put("securityCodeList", str);
}
List<QuotePriceAnalysisDto> listAll = null;
if (isCT){
listAll = quotePriceAnalysisMapper.getQuotePriceAnalysisCT(map);
}else {
listAll = quotePriceAnalysisMapper.getQuotePriceAnalysisFCT(map);
}
if (CollectionUtils.isEmpty(listAll)){
listAll = new ArrayList<>();
}
listAll = changePortpilioNatureC(listAll);
List<String> codeList = new ArrayList<>();
for (QuotePriceAnalysisDto q : listAll) {
if (codeList.contains(q.getSecurityCode())) {
continue;
} else {
codeList.add(q.getSecurityCode());
}
}
List<AssetNameDTO> assetNameDTOList = quotePriceAnalysisMapper.getParentName();
if (CollectionUtils.isEmpty(assetNameDTOList)){
assetNameDTOList = new ArrayList<>();
}
for (int i = 0; i < codeList.size(); i++) {
List<QuotePriceAnalysisDto> list = new ArrayList<>();
for (QuotePriceAnalysisDto q : listAll) {
if (q.getSecurityCode() == null){
continue;
}
if (q.getSecurityCode().toString().equals(codeList.get(i))) {
list.add(q);
}
}
if (list.size() > 0) {
/*list = changePortpilioNatureC(list);*/
QuotePriceAnalysisDto quotePriceAnalysisDto = new QuotePriceAnalysisDto();
quotePriceAnalysisDto.setSecurityNum(BigDecimal.ZERO);
quotePriceAnalysisDto.setSecurityCost(0.0);
quotePriceAnalysisDto.setSMV(new BigDecimal(0));
quotePriceAnalysisDto.setSMCpercent(new BigDecimal(0));
quotePriceAnalysisDto.setParent("0");
quotePriceAnalysisDto.setLevel(0);
quotePriceAnalysisDto.setIndex(index++);
quotePriceAnalysisDto.setIsLeaf(false);
quotePriceAnalysisDto.setExpanded(false);
quotePriceAnalysisDto.setLoaded(true);
allList.add(quotePriceAnalysisDto);
//判断相同组合的记录,只累加一次,防止数据翻倍
List<String> portfolioNameList = new ArrayList<>();
for (QuotePriceAnalysisDto q : list) {
if (q.getParentCode() == null || q.getParentCode().toString().equals("-")) {
q.setParentCode("-");
} else {
for (AssetNameDTO a : assetNameDTOList) {
if (q.getParentCode().equals(a.getCode())) {
q.setParentCode(a.getName());
}
}
}
quotePriceAnalysisDto.setSecurityCode(q.getSecurityCode());
quotePriceAnalysisDto.setSecurityName(q.getSecurityName());
q.setSecurityName("");
q.setSecurityCode("");
q.setParent(quotePriceAnalysisDto.getIndex() + "");
q.setLevel(1);
q.setIndex(index++);
q.setIsLeaf(true);
q.setExpanded(false);
q.setLoaded(true);
if(!portfolioNameList.contains(q.getPortpolioName())){
quotePriceAnalysisDto.setSecurityNum(ArithmeticUtil.doAdd(quotePriceAnalysisDto.getSecurityNum(), q.getSecurityNum()));
if (q.getSecurityCost() == null) {
} else {
Double cost1 = quotePriceAnalysisDto.getSecurityCost() == null ? 0 : quotePriceAnalysisDto.getSecurityCost();
Double cost2 = q.getSecurityCost() == null ? 0 : q.getSecurityCost();
quotePriceAnalysisDto.setSecurityCost(cost1 + cost2);
}
if (q.getSMV() == null) {
} else {
BigDecimal smv1 = quotePriceAnalysisDto.getSMV() == null ? new BigDecimal(0) : quotePriceAnalysisDto.getSMV();
BigDecimal smv2 = q.getSMV() == null ? new BigDecimal(0) : q.getSMV();
quotePriceAnalysisDto.setSMV(smv1.add(smv2));
}
if (q.getSMCpercent() == null) {
} else {
BigDecimal sp1 = quotePriceAnalysisDto.getSMCpercent() == null ? new BigDecimal(0) : quotePriceAnalysisDto.getSMCpercent();
BigDecimal sp2 = q.getSMCpercent() == null ? new BigDecimal(0) : q.getSMCpercent();
quotePriceAnalysisDto.setSMCpercent(sp1.add(sp2));
}
portfolioNameList.add(q.getPortpolioName());
}
allList.add(q);
}
}
}
return allList;
}
3、我们再看mapper
文件:
public List<QuotePriceAnalysisDto> getQuotePriceAnalysisCT(Map<String, Object> map);
4、最后,进入到SQL中对不同List进行分析判断:
<select id="getQuotePriceAnalysisCT"
resultType="com.joyintech.tams.wwp.positionanalysis.quotepriceanalysis.dto.QuotePriceAnalysisDto"
parameterType="Map">
SELECT
allList.*,
portpolioCombine.parentCode as parentCode
FROM
(
SELECT
detailList.valDate as valDate,
detailList.portpolioCode as portpolioCode,
detailList.securityName AS securityName,
detailList.securityCode AS securityCode,
detailList.portpolioName AS portpolioName,
detailList.portpolioNature AS portpolioNature,
detailList.securityNum AS securityNum ,
detailList.securityCost AS securityCost,
detailList.SMV AS SMV,
totalList.overallScale overallScale,
(CASE
WHEN detailList.valMethod='MV' THEN ROUND(detailList.SMV/totalList.overallScale,6)*100
ELSE ROUND(detailList.securityCost/totalList.overallScale,6)*100
END) AS SMCpercent
FROM
(
SELECT
ASSET_OBJECT.ID,
ASSET_OBJECT.SHORT_NAME AS securityName,
ASSET_OBJECT.ASSET_CODE as securityCode,
PORTFOLIO_ASSET_RIFT_VALUATION.VAL_DATE AS valDate ,
PORTFOLIO_ASSET_RIFT_VALUATION.PORTFOLIO_CODE as portpolioCode,
PORTFOLIO_ASSET_RIFT_VALUATION.SUBJECT_CODE ,
PORTFOLIO_ASSET_RIFT_VALUATION.SUBJECT_NAME,
PORTFOLIO_VAL_INFO.VAL_METHOD AS valMethod,
PORTFOLIO_INFO.NAME AS portpolioName,
PORTFOLIO_INFO.PORTFOLIO_NATURE AS portpolioNature ,
PORTFOLIO_ASSET_RIFT_VALUATION.SECURITY_QUANTITY AS securityNum ,
PORTFOLIO_ASSET_RIFT_VALUATION.SECURITY_COST AS securityCost ,
PORTFOLIO_ASSET_RIFT_VALUATION.SMV AS SMV
FROM
PORTFOLIO_ASSET_RIFT_VALUATION,
PORTFOLIO_INFO,
PORTFOLIO_VAL_INFO,
(select
max(VAL_DATE) AS valDate,
portfolio_code
from
PORTFOLIO_ASSET_RIFT_VALUATION
where
VAL_DATE <=#{valDate}
group by
portfolio_code
) dd,
ASSET_OBJECT
LEFT JOIN PORTFOLIO_INFO PORTFOLIO_INFO2
ON ASSET_OBJECT.ASSET_CODE = PORTFOLIO_INFO2.CODE
LEFT JOIN
ASSET_FUND_INFO AFO
ON
ASSET_OBJECT.ID=AFO.ASSET_OBJECT_ID
WHERE
PORTFOLIO_ASSET_RIFT_VALUATION.PORTFOLIO_CODE=PORTFOLIO_INFO.CODE
AND PORTFOLIO_ASSET_RIFT_VALUATION.ASSET_OBJECT_ID=ASSET_OBJECT.ID
AND PORTFOLIO_VAL_INFO.CODE=PORTFOLIO_INFO.CODE
<!--选:利率型计划、货币型计划、证券类净值型计划、其他类净值型计划 之外的资产维度 且(资产维度既选货币基金又选非货币基金或者这两个都没选) -->
<if test="resultList2 != null and resultList2.size()>0 and resultList.size()==0 and resultList3.size()==resultList4.size()">
AND (SUBSTR(PORTFOLIO_ASSET_RIFT_VALUATION.SUBJECT_CODE,1,4) IN (select SUBJECT_CODE from
ASSET_CLASS_SUBJECT_CODE where ASSET_CLASS_CODE IN
<foreach item="item" collection="resultList2" index="index" open="(" separator="," close=")">
'${item}'
</foreach>))
</if>
<!--选:利率型计划、货币型计划、证券类净值型计划、其他类净值型计划 之外的资产维度 且(资产维度不包含非货币基金) -->
<if test="resultList3 != null and resultList3.size()>0 and resultList.size()==0 and resultList3.size()>resultList4.size()">
AND (SUBSTR(PORTFOLIO_ASSET_RIFT_VALUATION.SUBJECT_CODE,1,4) IN (select SUBJECT_CODE from
ASSET_CLASS_SUBJECT_CODE where ASSET_CLASS_CODE IN
<foreach item="item" collection="resultList3" index="index" open="(" separator="," close=")">
'${item}'
</foreach>)
OR
SUBSTR(PORTFOLIO_ASSET_RIFT_VALUATION.SUBJECT_CODE,1,4)= '1105'
AND AFO.FUND_TYPE ='01'
)
</if>
<!--选:利率型计划、货币型计划、证券类净值型计划、其他类净值型计划 之外的资产维度 且(资产维度不包含货币基金) -->
<if test="resultList4 != null and resultList4.size()>0 and resultList.size()==0 and resultList4.size()>resultList3.size()">
AND (SUBSTR(PORTFOLIO_ASSET_RIFT_VALUATION.SUBJECT_CODE,1,4) IN (select SUBJECT_CODE from
ASSET_CLASS_SUBJECT_CODE where ASSET_CLASS_CODE IN
<foreach item="item" collection="resultList4" index="index" open="(" separator="," close=")">
'${item}'
</foreach>)
OR
SUBSTR(PORTFOLIO_ASSET_RIFT_VALUATION.SUBJECT_CODE,1,4)= '1105'
AND AFO.FUND_TYPE !='01'
)
</if>
<!--仅选:利率型计划、货币型计划、证券类净值型计划、其他类净值型计划 其中之一的资产维度-->
<if test="resultList != null and resultList1.size()==0 and resultList2.size()==0 and resultList3.size()==0 and resultList4.size()==0 and resultList.size()>0 ">
AND SUBSTR(PORTFOLIO_ASSET_RIFT_VALUATION.SUBJECT_CODE,1,4)='1701'
AND PORTFOLIO_INFO2.PORTFOLIO_KIND IN
<foreach item="item" collection="resultList" index="index" open="(" separator="," close=")">
'${item}'
</foreach>
</if>
<!-- 既选了特殊的四种又选了四种之外的情况,选了非货币基金-->
<if test="resultList1 != null and resultList != null and resultList4!=null and resultList1.size()>0 and resultList.size()>0 and resultList4.size()>resultList3.size()">
AND (SUBSTR(PORTFOLIO_ASSET_RIFT_VALUATION.SUBJECT_CODE,1,4) IN (select SUBJECT_CODE from
ASSET_CLASS_SUBJECT_CODE where ASSET_CLASS_CODE IN
<foreach item="item" collection="resultList4" index="index" open="(" separator="," close=")">
'${item}'
</foreach>)
OR
SUBSTR(PORTFOLIO_ASSET_RIFT_VALUATION.SUBJECT_CODE,1,4)= '1105'
AND AFO.FUND_TYPE !='01'
OR SUBSTR(PORTFOLIO_ASSET_RIFT_VALUATION.SUBJECT_CODE,1,4)='1701'
AND PORTFOLIO_INFO2.PORTFOLIO_KIND IN
<foreach item="item" collection="resultList" index="index" open="(" separator="," close=")">
'${item}'
</foreach>
)
</if>
<!-- 既选了特殊的四种又选了四种之外的情况,选了货币基金-->
<if test="resultList1 != null and resultList != null and resultList3 != null and resultList1.size()>0 and resultList.size()>0 and resultList3.size()>resultList4.size()">
AND (SUBSTR(PORTFOLIO_ASSET_RIFT_VALUATION.SUBJECT_CODE,1,4) IN (select SUBJECT_CODE from
ASSET_CLASS_SUBJECT_CODE where ASSET_CLASS_CODE IN
<foreach item="item" collection="resultList3" index="index" open="(" separator="," close=")">
'${item}'
</foreach>)
OR
SUBSTR(PORTFOLIO_ASSET_RIFT_VALUATION.SUBJECT_CODE,1,4)= '1105'
AND AFO.FUND_TYPE ='01'
OR SUBSTR(PORTFOLIO_ASSET_RIFT_VALUATION.SUBJECT_CODE,1,4)='1701'
AND PORTFOLIO_INFO2.PORTFOLIO_KIND IN
<foreach item="item" collection="resultList" index="index" open="(" separator="," close=")">
'${item}'
</foreach>
)
</if>
<!-- 既选了特殊的四种又选了四种之外的情况(资产维度既选了货币基金又选了非货币基金或者这两个都没选)-->
<if test="resultList4 != null and resultList3 != null and resultList != null and resultList.size()>0 and resultList3.size()>0 and resultList4.size()>0 and resultList3.size()==resultList4.size()">
AND (SUBSTR(PORTFOLIO_ASSET_RIFT_VALUATION.SUBJECT_CODE,1,4) IN (select SUBJECT_CODE from
ASSET_CLASS_SUBJECT_CODE where ASSET_CLASS_CODE IN
<foreach item="item" collection="resultList2" index="index" open="(" separator="," close=")">
'${item}'
</foreach>)
OR SUBSTR(PORTFOLIO_ASSET_RIFT_VALUATION.SUBJECT_CODE,1,4)='1701'
AND PORTFOLIO_INFO2.PORTFOLIO_KIND IN
<foreach item="item" collection="resultList" index="index" open="(" separator="," close=")">
'${item}'
</foreach>
)
</if>
<!-- 资产维度不选的情况-->
<if test="resultList1.size()==0 and resultList.size()==0 and resultList3.size()==0 and resultList4.size()==0">
AND (SUBSTR(PORTFOLIO_ASSET_RIFT_VALUATION.SUBJECT_CODE,1,4) IN ('1102', '1103','1104','1105','3102', '3103','3101','3104','3105','1701','1901'))
</if>
<if test="securityCodeList != null and !securityCodeList.isEmpty()">
AND ASSET_OBJECT.ASSET_CODE in
<foreach collection="securityCodeList" item="item" index="index" open="(" separator="," close=")">
'${item}'
</foreach>
</if>
<if test="securityName != null" >
AND ASSET_OBJECT.SHORT_NAME IN ${securityName}
</if>
<if test="portfolio_code != null and portfolio_code != ''">
AND PORTFOLIO_INFO.CODE IN ${portfolio_code}
</if>
<!--update BY CJ 2019/9/10 start-->
<if test="zhwd == 'PD_02'">
AND PORTFOLIO_INFO.PORTFOLIO_KIND IN('PK_04','PK_05')
</if>
<if test="zhwd == 'PD_02' and isAdmin != true ">
AND PORTFOLIO_INFO.CODE IN(
SELECT
PORTFOLIO_INFO.CODE
FROM
PORTFOLIO_INFO,SYS_PORTFOLIO_INFO
where
SYS_PORTFOLIO_INFO.GROUP_ID = PORTFOLIO_INFO.GROUP_ID
AND SYS_PORTFOLIO_INFO.STATUS = 'E'
<if test="userId != null and userId != ''">
AND SYS_PORTFOLIO_INFO.USER_ID = #{userId,jdbcType = VARCHAR}
</if>
)
</if>
<if test="zhwd != null and zhwd != '' and (zhwd == 'PD_01' or zhwd == 'PD_03') and isAdmin != true">
<![CDATA[
and PORTFOLIO_INFO.code in (SELECT
PORTFOLIO_CODE
FROM
SYS_DEPT_PORTFOLIO
WHERE
DEPT_CODE IN (
SELECT
DEPT_CODE
FROM
SYS_DEPT_USER
WHERE
USER_ID = #{userId}
UNION ALL
SELECT
DEPT_CODE
FROM
SYS_DEPARTMENT
WHERE
DIRECT_DEPT_CODE IN (
SELECT
DEPT_CODE
FROM
SYS_DEPT_USER
WHERE
USER_ID = #{userId}
)
))
]]>
</if>
<if test="zhwd == 'PD_01'">
and PORTFOLIO_INFO.PORTFOLIO_KIND in ('PK_01','PK_02','PK_03','PK_06','PK_09')
</if>
<!--update BY CJ 2019/9/10 end-->
AND PORTFOLIO_ASSET_RIFT_VALUATION.VAL_DATE <=#{valDate}
and PORTFOLIO_ASSET_RIFT_VALUATION.val_date=dd.valDate
and PORTFOLIO_ASSET_RIFT_VALUATION.portfolio_code=dd.portfolio_code
) detailList
LEFT JOIN
(
SELECT
PSV.VAL_DATE as valDate ,
PSV.PORTFOLIO_CODE as portpolioCode ,
SUM(CASE
WHEN PORTFOLIO_VAL_INFO.VAL_METHOD='MV' THEN PSV.SMV
ELSE PSV.SECURITY_COST
END) AS overallScale
FROM
PORTFOLIO_SUM_VALUATION PSV,
PORTFOLIO_VAL_INFO
WHERE
PSV.PORTFOLIO_CODE=PORTFOLIO_VAL_INFO.CODE
AND PSV.SUM_CODE='604'
AND PSV.VAL_DATE <= #{valDate}
AND NOT EXISTS (
SELECT 1 FROM
PORTFOLIO_SUM_VALUATION PAV
WHERE 1=1
AND PAV.PORTFOLIO_CODE = PSV.PORTFOLIO_CODE
AND PAV.VAL_DATE > PSV.VAL_DATE
AND PAV.PORTFOLIO_CODE=PORTFOLIO_VAL_INFO.CODE
AND PAV.SUM_CODE='604'
AND PAV.VAL_DATE <= #{valDate}
)
Group BY PSV.VAL_DATE,PSV.PORTFOLIO_CODE
) totalList
ON
totalList.valDate=detailList.valDate
AND totalList.portpolioCode=detailList.portpolioCode
ORDER BY
detailList.securityCode
) allList
LEFT JOIN
(
SELECT distinct
PRT.PORTFOLIO_CODE as portpolioCode,
PRT.PARENT_PCODE as parentCode,
PRT.TOP_PCODE ,
(CASE
WHEN PRT.PARENT_PCODE='-' THEN NULL
ELSE PORTFOLIO_INFO.NAME
END) AS parentPCode
FROM
PORTFOLIO_RELATE_TREE PRT
LEFT JOIN
PORTFOLIO_INFO
ON
PRT.PARENT_PCODE=PORTFOLIO_INFO.CODE
WHERE
PRT.RELATE_DATE<=#{valDate}
AND NOT EXISTS (
SELECT 1 FROM
PORTFOLIO_RELATE_TREE PAV
WHERE 1=1
AND PAV.PORTFOLIO_CODE = PRT.PORTFOLIO_CODE
AND PAV.RELATE_DATE > PRT.RELATE_DATE
AND PAV.RELATE_DATE<=#{valDate})
)portpolioCombine
ON allList.portpolioCode=portpolioCombine.portpolioCode
</select>
总结
其实这个方法不难,可能也有不妥的地方,请大神多多指教。我传的代码可能也有冗余,但是我想记录的是之前开始在做逻辑思考的时候,想复杂了以至于乱的一塌糊涂。所以,时候隔了很久我想记录下来,提醒我自己,时刻保持清醒,这里也可以供有心人参考一下,不当之处,烦请指教,谢谢。。。。。。