SQL语句练习题(包含有多表查询)-答案

SQL语句练习题(包含有多表查询)-答案（通用1篇）

篇1：SQL语句练习题(包含有多表查询)-答案

标准|语法|语句

W_KMR01_11 ---- KMM13BR COPYWRITE BY LD /09/07 15:30

SELECT DISTINCT SUBSTRING( KMM102.KMM102_BGT_TYPE,1,4 ) AS BGT_TYPE,

( CASE SUBSTRING( KMM102.KMM102_BGT_TYPE,3,2 ) WHEN “09” THEN “1” WHEN “10” THEN “2”

WHEN “11” THEN “3” ELSE “9” END ) AS BGT_CLASS,

SUBSTRING( KMM102.KMM102_BGT_TYPE,1,2 ) AS BGT_TEAM,

( ISNULL( (SELECT SUBSTRING(KMC101.KMC101_NAME,CHARINDEX(“-”,KMC101.KMC101_NAME)+1,12) FROM KMC101

WHERE ( KMC101.KMC101_TYPE = “BUDGETCODE” )

AND ( KMC101.KMC101_CODE = SUBSTRING( KMM102.KMM102_BGT_TYPE,1,4 ) + “00” ) ),“” ) ) AS BGT_NAME,

SUM( ISNULL( KMM101.KMM101_BGT_JAN,0 ) ) AS BGT_AMT01,

SUM( ISNULL( KMM101.KMM101_BGT_FEB,0 ) ) AS BGT_AMT02,

SUM( ISNULL( KMM101.KMM101_BGT_MAR,0 ) ) AS BGT_AMT03,

SUM( ISNULL( KMM101.KMM101_BGT_APR,0 ) ) AS BGT_AMT04,

SUM( ISNULL( KMM101.KMM101_BGT_MAY,0 ) ) AS BGT_AMT05,

SUM( ISNULL( KMM101.KMM101_BGT_JUN,0 ) ) AS BGT_AMT06,

SUM( ISNULL( KMM101.KMM101_BGT_JUL,0 ) ) AS BGT_AMT07,

SUM( ISNULL( KMM101.KMM101_BGT_AUG,0 ) ) AS BGT_AMT08,

SUM( ISNULL( KMM101.KMM101_BGT_SEP,0 ) ) AS BGT_AMT09,

SUM( ISNULL( KMM101.KMM101_BGT_OCT,0 ) ) AS BGT_AMT10,

SUM( ISNULL( KMM101.KMM101_BGT_NOV,0 ) ) AS BGT_AMT11,

SUM( ISNULL( KMM101.KMM101_BGT_DEC,0 ) ) AS BGT_AMT12,

( SUM( ISNULL( KMM101.KMM101_BGT_JAN,0 ) ) + SUM( ISNULL( KMM101.KMM101_BGT_FEB,0 ) ) +

SUM( ISNULL( KMM101.KMM101_BGT_MAR,0 ) ) + SUM( ISNULL( KMM101.KMM101_BGT_APR,0 ) ) +

SUM( ISNULL( KMM101.KMM101_BGT_MAY,0 ) ) + SUM( ISNULL( KMM101.KMM101_BGT_JUN,0 ) ) +

SUM( ISNULL( KMM101.KMM101_BGT_JUL,0 ) ) + SUM( ISNULL( KMM101.KMM101_BGT_AUG,0 ) ) +

SUM( ISNULL( KMM101.KMM101_BGT_SEP,0 ) ) + SUM( ISNULL( KMM101.KMM101_BGT_OCT,0 ) ) +

SUM( ISNULL( KMM101.KMM101_BGT_NOV,0 ) ) + SUM( ISNULL( KMM101.KMM101_BGT_DEC,0 ) ) ) AS AMT01,

( “XXXXXXXX” ) AS AMT02,

SUM( ISNULL( ( CASE WHEN ( CONVERT(CHAR(8),KMM102.KMM102_CHK_DATE,112) < “YYYYMMDD” )

THEN KMM102.KMM102_CHK_AMT ELSE 0 END ),0 ) ) AS AMT09,

SUM( ISNULL( ( CASE WHEN ( ( CONVERT(CHAR(8),KMM102.KMM102_OVER_DATE,112) < “YYYYMMDD” )

AND KMM102.KMM102_OVER_STATUS “00” )

THEN KMM102.KMM102_OVER_AMT

WHEN ( ( CONVERT(CHAR(8),KMM102.KMM102_OVER_DATE,112) < “YYYYMMDD” )

AND KMM102.KMM102_OVER_STATUS = “00” )

THEN KMM102.KMM102_OVER_AMT ELSE 0 END ),0 ) ) AS AMT07,

SUM( ISNULL( ( SELECT CASE WHEN ( ( CONVERT(CHAR(8),KMM103.KMM103_PAY_DATE,112) < “YYYYMMDD” )

AND KMM103.KMM103_PAY_STATUS “2” )

THEN KMM103.KMM103_PAY_AMT ELSE 0 END

FROM KMM103

WHERE ( KMM103.KMM103_YY = KMM102.KMM102_YY )

AND ( KMM103.KMM103_BGT_KIND = KMM102.KMM102_BGT_KIND )

AND ( KMM103.KMM103_CASE_NO = KMM102.KMM102_CASE_NO ) ),0 ) ) AS AMT03,

SUM( ISNULL( ( SELECT CASE WHEN ( ( CONVERT(CHAR(8),KMM103.KMM103_PAY_DATE,112) < “YYYYMMDD” )

AND KMM103.KMM103_PAY_STATUS “1” )

THEN KMM103.KMM103_PAY_AMT ELSE 0 END

FROM KMM103

WHERE ( KMM103.KMM103_YY = KMM102.KMM102_YY )

AND ( KMM103.KMM103_BGT_KIND = KMM102.KMM102_BGT_KIND )

AND ( KMM103.KMM103_CASE_NO = KMM102.KMM102_CASE_NO ) ),0 ) ) AS AMT04

FROM KMM102,

KMM101

WHERE ( KMM102.KMM102_YY = “YYY” ) AND

( KMM102.KMM102_BGT_KIND = “1” ) AND

( KMM101.KMM101_YY =* KMM102.KMM102_YY ) AND

( KMM101.KMM101_BGT_KIND =* KMM102.KMM102_BGT_KIND ) AND

( KMM101.KMM101_BGT_TYPE =* KMM102.KMM102_BGT_TYPE )

GROUP BY SUBSTRING( KMM102.KMM102_BGT_TYPE,1,4 ),

SUBSTRING( KMM102.KMM102_BGT_TYPE,3,2 ),

SUBSTRING( KMM102.KMM102_BGT_TYPE,1,2 )

ORDER BY BGT_CLASS ASC,

BGT_TYPE DESC

;