[MSSQL] 서브쿼리, 하위쿼리, Inner Query

https://infodbbase.tistory.com/45

[MS-SQL]SubQuery - (mssql 서브쿼리, mssql 하위쿼리)

http://blog.naver.com/PostView.nhn?blogId=chsmanager&logNo=140202593872&parentCategoryNo=&categoryNo=48&viewDate=&isShowPopularPosts=false&from=postView

ms sql subquery, 서브쿼리 종류 및 SQL 문 사용방법

 

declare @scale_line char(2)
declare @la_today char(8)

set @scale_line = 'A'
set @la_today = '20190219'

declare @StartTime char(5)
declare @EndTime char(5)

select * INTO #tempTable from

-----------------------
-- A (07:00 ~ 09:00) --
-----------------------
-- Select - 1
(select sd.scale_line as 'SD1_scale_line', sd.ymd_scale AS 'SD1_ymd_scale', '07:00 ~ 09:00' as 'SD1_time_scale', kd.Kind_Name AS 'SD1_Kind_Name'
, sd.fs_stock as 'SD1_fs_stock', sd.f1_stock as 'SD1_f1_stock', sd.f2_stock as 'SD1_f2_stock', sd.f3_stock as 'SD1_f3_stock', sd.lg_stock as 'SD1_lg_stock'
FROM scale_data as sd
INNER JOIN Kind_Master as kd
on sd.kind_code = kd.kind_code
WHERE sd.scale_line = @scale_line
AND sd.ymd_scale + Substring( sd.time_scale, 1, 5 ) = @la_today +'07:00') as SD1
-- Select - 2
, (select
MAX( sd.b1_all_total ) - MIN( sd.b1_all_total ) as 'SD2_b1_all_total',
MAX( sd.fs_all_total ) - MIN( sd.fs_all_total ) as 'SD2_fs_all_total',
MAX( sd.f1_all_total ) - MIN( sd.f1_all_total ) as 'SD2_f1_all_total',
MAX( sd.f2_all_total ) - MIN( sd.f2_all_total ) as 'SD2_f2_all_total',
MAX( sd.f3_all_total ) - MIN( sd.f3_all_total ) as 'SD2_f3_all_total',
MAX( sd.lg_all_total ) - MIN( sd.lg_all_total ) as 'SD2_lg_all_total',
MAX( sd.br_all_total ) - MIN( sd.br_all_total ) as 'SD2_br_all_total',
MAX( sd.jo_all_total ) - MIN( sd.jo_all_total ) as 'SD2_jo_all_total',
MAX( sd.of_all_total ) - MIN( sd.of_all_total ) as 'SD2_of_all_total'

from scale_data sd
WHERE sd.scale_line = @scale_line
AND sd.ymd_scale + Substring( sd.time_scale, 1, 5 )
BETWEEN @la_today + '07:00' AND @la_today + '09:00') as SD2
-- Select - 3
, (select
SUM( CASE tech_info.tc_grade WHEN 'F0' THEN tech_info.tc_prod_mist ELSE 0 END ) as 'T1_tc_prod_mist_F0',
SUM( CASE tech_info.tc_grade WHEN 'F0' THEN tech_info.tc_prod_ASh ELSE 0 END ) as 'T1_tc_prod_ASh_F0',
SUM( CASE tech_info.tc_grade WHEN 'F0' THEN tech_info.tc_prod_prtn ELSE 0 END ) as 'T1_tc_prod_prtn_F0',
SUM( CASE tech_info.tc_grade WHEN 'F1' THEN tech_info.tc_prod_mist ELSE 0 END ) as 'T1_tc_prod_mist_F1',
SUM( CASE tech_info.tc_grade WHEN 'F1' THEN tech_info.tc_prod_ASh ELSE 0 END ) as 'T1_tc_prod_ASh_F1',
SUM( CASE tech_info.tc_grade WHEN 'F1' THEN tech_info.tc_prod_prtn ELSE 0 END ) as 'T1_tc_prod_prtn_F1',
SUM( CASE tech_info.tc_grade WHEN 'F2' THEN tech_info.tc_prod_mist ELSE 0 END ) as 'T1_tc_prod_mist_F2',
SUM( CASE tech_info.tc_grade WHEN 'F2' THEN tech_info.tc_prod_ASh ELSE 0 END ) as 'T1_tc_prod_ASh_F2',
SUM( CASE tech_info.tc_grade WHEN 'F2' THEN tech_info.tc_prod_prtn ELSE 0 END ) as 'T1_tc_prod_prtn_F2',
SUM( CASE tech_info.tc_grade WHEN 'F3' THEN tech_info.tc_prod_mist ELSE 0 END ) as 'T1_tc_prod_mist_F3',
SUM( CASE tech_info.tc_grade WHEN 'F3' THEN tech_info.tc_prod_ASh ELSE 0 END ) as 'T1_tc_prod_ASh_F3',
SUM( CASE tech_info.tc_grade WHEN 'F3' THEN tech_info.tc_prod_prtn ELSE 0 END ) as 'T1_tc_prod_prtn_F3'
FROM tech_info
WHERE tech_info.tc_line = @scale_line
AND tech_info.tc_date + tech_info.tc_time BETWEEN @la_today + '07:00' AND @la_today + '09:00') as TI