select a.id,(select count(*) from tableb where num=a.id and gub='1') as cnt,(select count(*) from tableb where num=a.id and gub='2') as cnt2 .a.title,a.name from table a where a.tag='keyword'
이런 형태의 sql문일때 저 카운트를 구하기 위해 쓰는 서브쿼리를 어떻게 바꿔야 서브쿼리 없이 사용할수 있을까요?
Comment 3
-
select a.id , max(a.title) as title , max(a.name) as name , count(iif(b.gub = 1, 1) as cnt1 , count(iif(b.gub = 2, 1) as cnt2 from table1 as a inner join table2 as b on b.num = a.id where a.tag = 'keyword' group by a.id
-
select a.id , a.title , a.name , b.cnt1 , b.cnt2 from table1 as a cross apply ( select count(iif(gub = 1, 1) as cnt1 , count(iif(gub = 2, 1) as cnt2 from table2 where num = a.id ) b where tag = 'keyword'
-
amoeg1
2021.02.17 19:15
select a.id,
C.cnt,
C.cnt2,
a.title,
a.name
from table A OUTER APPLY (SELECT COUNT(CASE B.GUB WHEN 1 THEN 1 END) CNT,
COUNT(CASE B.GUB WHEN 2 THEN 1 END) CNT2
FROM table b
WHERE A.ID = B.NUM
AND B.GUB IN ('1','2')) C
where a.tag='keyword'cmd_comment_vote_user Upvote0 Downvote0 Comment Update Delete
