多个SQL的合并成一个单一的SQL
问题描述:
喜有以下查询多个SQL的合并成一个单一的SQL
(SELECT COUNT(DISTINCT KUNDNR) CHECKED_CUSTOMER from CLNT0001.TCM_CHECK_SUMMARY
where '20170322000000000' <= HISTVON and HISTVON < '20170323000000000' and INSTITUTSNR='0001')
和
SELECT clientNumber
,creationDate
,customerNumber
,checkedCustomer
,CLNT0001.TCM_CHECK_SUMMARY.COUNTRY_CODE countryCode
,CLNT0001.TCM_CHECK_SUMMARY.PST_KURZTEXT personStatus
,CLNT0001.TCM_CASE_COUNTRY_GROUP.COUNTRY_CODE homeCountryCode
,CLNT0001.TCM_CASE_COUNTRY_GROUP.PST_LFD_NR personStatusId
,CLNT0001.TCM_CASE_COUNTRY_GROUP.REGULATION regulation
,caseStatus
,COC_SCORE_COUNT cocCaseCount
FROM (
SELECT GEPRUEFT_JN checkedCustomer
,INSTITUTSNR clientNumber
,KUNDNR customerNumber
,CASE_STATUS caseStatus
,MAX(CREATION_DATE) creationDate
FROM CLNT0001.TAXACTCASE
WHERE GEPRUEFT_JN = 'J' AND CREATION_DATE>='20170322000000000' AND
CREATION_DATE<='20170323000000000'
GROUP BY KUNDNR
,INSTITUTSNR
,GEPRUEFT_JN
,CASE_STATUS
) T1
INNER JOIN CLNT0001.TCM_CHECK_SUMMARY ON T1.customerNumber = CLNT0001.TCM_CHECK_SUMMARY.KUNDNR
INNER JOIN CLNT0001.TCM_CASE_COUNTRY_GROUP ON T1.customerNumber = CLNT0001.TCM_CASE_COUNTRY_GROUP.KUNDNR
WHERE T1.creationDate <= CLNT0001.TCM_CHECK_SUMMARY.HISTBIS
AND T1.creationDate >= CLNT0001.TCM_CHECK_SUMMARY.HISTVON
我需要CHECKED_CUSTOMER列第二查询的结果集的一部分,我不能够想办法做到这一点,这可能吗?
答
SELECT clientNumber,creationDate,customerNumber,checkedCustomer
,CLNT0001.TCM_CHECK_SUMMARY.COUNTRY_CODE countryCode
,CLNT0001.TCM_CHECK_SUMMARY.PST_KURZTEXT personStatus
,CLNT0001.TCM_CASE_COUNTRY_GROUP.COUNTRY_CODE homeCountryCode
,CLNT0001.TCM_CASE_COUNTRY_GROUP.PST_LFD_NR personStatusId
,CLNT0001.TCM_CASE_COUNTRY_GROUP.REGULATION regulation
,caseStatus,COC_SCORE_COUNT cocCaseCount ,CHECKED_CUSTOMER
FROM (
SELECT GEPRUEFT_JN checkedCustomer,INSTITUTSNR clientNumber ,KUNDNR customerNumber ,CASE_STATUS caseStatus,MAX(CREATION_DATE) creationDate,COUNT(DISTINCT b.KUNDNR) CHECKED_CUSTOMER
FROM CLNT0001.TAXACTCASE
LEFT JOIN CLNT0001.TCM_CHECK_SUMMARY b ON CLNT0001.TAXACTCASE.KUNDNR=b.KUNDNR
WHERE GEPRUEFT_JN = 'J' AND CREATION_DATE>='20170322000000000' AND
CREATION_DATE<='20170323000000000'
GROUP BY KUNDNR,INSTITUTSNR ,GEPRUEFT_JN,CASE_STATUS
) T1 INNER JOIN CLNT0001.TCM_CHECK_SUMMARY ON T1.customerNumber = CLNT0001.TCM_CHECK_SUMMARY.KUNDNR
INNER JOIN CLNT0001.TCM_CASE_COUNTRY_GROUP ON T1.customerNumber = CLNT0001.TCM_CASE_COUNTRY_GROUP.KUNDNR
WHERE T1.creationDate <= CLNT0001.TCM_CHECK_SUMMARY.HISTBIS
AND T1.creationDate >= CLNT0001.TCM_CHECK_SUMMARY.HISTVON
这是什么? – kumarD
OOps只是混淆表CLNT0001.TCM_CHECK_SUMMARY为b错T2我输入 –