다음과 같아 Query를 작성 했는데 하나로 표시 하고자 합니다
select A. tick_kind, A. tick_id, A. com_id, A. comp_id, sum(A. ti_qty) as cqty, 0 as pqty
from tbl_ticksale A join tbl_tickbase B
on A. tick_kind = B. tick_kind
and A. tick_id = B. tick_id
and A. com_id = B. com_id
where A. tick_kind = '01'
and A. com_id ='H'
and A. comp_id ='h01'
and B. tick_id ='075010022537'
group by A. tick_kind, A. tick_id, A. com_id, A. comp_id
union
select C. tick_kind, C. tick_id, C. com_id, C. comp_id, 0 as cqty, sum(C. ti_qty) as pqty
from tbl_tickre C join tbl_tickbase D
on C. tick_kind = D. tick_kind
and C. tick_id = D. tick_id
and C. com_id = D. com_id
where C. tick_kind ='01'
and C. com_id ='H'
and C. comp_id = 'H01'
and D. tick_id ='075010022537'
group by C. tick_kind, C. tick_id, C. com_id, C. comp_id
결과 값
tick_kind tick_id com_id comp_id cqty pqty
------------ ----------- --------- ----------- ------- -------
01 075010022537 H H01 0 1
01 075010022537 H H01 1 0
원하는 값
tick_kind tick_id com_id comp_id cqty pqty
------------ ----------- --------- ----------- ------- -------
01 075010022537 H H01 1 1
Comment 5
-
호리205
2019.07.30 17:25
-
슈토파이터
2019.07.30 17:48
다 group by 를 사용 한 결과 값인데..무슨 말씀이신지..?
-
작성한 쿼리를 한번 더 감싸서 group by 하라는 말 같네여.
-
이렇게 할 수도 잇겟네요.select a.tick_kind, a.tick_id, a.com_id, a.comp_id, sum(a.ti_qty) as cqty, sum(b.ti_qty) as pqtyfrom dbo.tbl_TickSale as ainner joindbo.tbl_TickRe as bon a.tick_kind = b.tick_kindand a.tick_id = b.tick_idand a.com_id = b.com_idinner joindbo.tbl_TickBase as con c.tick_kind = a.tick_kindand c.tick_id = a.tick_idand c.com_id = a.com_idwhere a.tick_kind = '01'and a.com_id = 'H'and a.comp_id = 'H01'and a.tick_id = '075010022537'group by a.tick_kind, a.tick_id, a.com_id, a.comp_id
-
폭주
2019.07.31 09:05
select T. tick_kind, T. tick_id, T.com_id, T.comp_id, sum(T.cqty) as cqty, sum(pqty) as pqty
from
(
select A. tick_kind, A. tick_id, A. com_id, A. comp_id, ti_qty as cqty, 0 as pqty
from tbl_ticksale A
union allselect C. tick_kind, C. tick_id, C. com_id, C. comp_id, 0 as cqty, C. ti_qty as pqty
from tbl_tickre C) as T
where T. tick_kind ='01'
and T. com_id ='H'
and T. comp_id = 'H01'
and T. tick_id ='075010022537'group by T. tick_kind, T. tick_id, T. com_id, T. comp_id
tick_kind, tick_id, com_id, comp_id 로 groupby해서 max나 sum 하시면 될 듯하네요.
union 안쓰고 풀 수도 있을듯 합니다.