sql 연습

select a.카드번호 , 
	a.거래금액 전일_거래금액 , 
    b.거래금액 주간_거래금액 , 
    c.거래금액 전월_거래금액 , 
    d.거래금액 연중_거래금액 
from 
	( 
	-- 전일거래실적 
		select 카드번호, 거래금액 
		from 일별카드거래내역 
		where 거래일자 = to_char(sysdate-1,'yyyymmdd') 
	) a ,
	( 
	-- 전주거래실적 
		select 카드번호, 
		sum(거래금액) 거래금액 
		from 일별카드거래내역 
	where 거래일자 between to_char(sysdate-7,'yyyymmdd') 
	and to_char(sysdate-1,'yyyymmdd') group by 카드번호 
	) b , 
	( 
	-- 전월거래실적 
	select 카드번호, sum(거래금액) 거래금액 
	from 일별카드거래내역 
	where 거래일자 between to_char(add_months(sysdate,-1),'yyyymm') || '01' and to_char(last_day(add_months(sysdate,-1)),'yyyymmdd') group by 카드번호 ) c , 
	( 
	-- 연중거래실적 
	select 카드번호, 
		sum(거래금액) 거래금액 
	from 일별카드거래내역 
	where 거래일자 between to_char(add_months(sysdate,-12),'yyyymmdd') and to_char(sysdate-1,'yyyymmdd') group by 카드번호 ) d 
where b.카드번호 (+) = a.카드번호 and c.카드번호 (+) = a.카드번호 and d.카드번호 (+) = a.카드번호


select 카드번호 , 
	sum( case when 거래일자 = to_char(sysdate-1,'yyyymmdd') then 거래금액 end ) 전일_거래금액 , 
        sum( case when 거래일자 between to_char(sysdate-7,'yyyymmdd') and to_char(sysdate-1,'yyyymmdd') then 거래금액 end ) 주간_거래금액 , 
        sum( case when 거래일자 between to_char(add_months(sysdate,-1),'yyyymm') || '01' and to_char(last_day(add_months(sysdate,-1)),'yyyymmdd') then 거래금액 end ) 전월_거래금액 , 
        sum( 거래금액 )연중_거래금액 
from 일별카드거래내역 
where 거래일자 between to_char(add_months(sysdate,-12),'yyyymmdd') and to_char(sysdate-1,'yyyymmdd') 
group by 카드번호 
having sum( case when 거래일자 = to_char(sysdate-1,'yyyymmdd') then 거래금액 end ) > 0

 

 

select * from sales_data
select convert(varchar(6), sales_date, 112) Month, sum(sales_count) as cnt
from sales_data
group by convert(varchar(6), sales_date, 112)

 

select convert(varchar(6), dateadd(month, (-1) * (cp.num-1), getdate()), 112) as Month, isnull(x.Cnt, 0) as Cnt
from copy_t cp
left outer join (
	select convert(varchar(6), sales_date, 112) Month, sum(sales_count) as cnt
	from sales_data
	group by convert(varchar(6), sales_date, 112)) x on x.Month = convert(varchar(6), dateadd(month, (-1) * (cp.num-1), getdate()), 112
    )
where cp.num <= 12
order by cp.num desc

 

select Month, min(Sales_date), Sales_Count
from (
	select convert(varchar(6), sales_date, 112) as Month, max(x.Sales_count) MaxCnt
    from sales_data x
    group by convert(varchar(6), sales_date, 112)
    ) y1
inner join sales_data y2 on convert(varchar(6), y2.sales_date, 112) = y1.Month and y2.Sales_Count = y1.MaxCnt
group by Month, Sales_Count




select ?????????????
from (
	select convert(varchar(6), dateadd(month, (-1) * (cp.num-1), getdate()), 112) as Month, isnull(x.Cnt, 0) as Cnt
    from copy_t cp
    left outer join (select convert(varchar(6), sales_date, 112) Month, sum(sales_count) as cnt
    from sales_data
    group by convert(varchar(6), sales_date, 112)) x on x.Month = convert(varchar(6), dateadd(month, (-1) * (cp.num-1), getdate()), 112)
    where cp.num <= 12
    ) x
inner join copy_t y on y.num <= 2

????????????????